Calculate sql row size

Question: How can I calculate the sql row size? Answer: Calculating the row-level row size is complicated and challenging. One of the key problems is SQL Server defines space at the extent level . An extent is made up of 8 x 8 KB physically contiguous pages . The complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated. Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers There... Read more →

Amount of sql server processors with sys.dm_os_schedulers

I needed to establish the amount of processors available to a SQL Server. A SQL Server scheduler is mapped to a single logical processor, so using this query you can establish the amount of processors. Any scheduler_id < 255 is a scheduler. It gets lightly more complicated if hyperthreading is enabled. . Find out from the server administrator , use a product such as the coreinfo or this Powershell script - Number of physical processors with Powershell – number of cores to establish if hyperthreading is turned on. If hyperthreading is enabled – this equates to double the schedulers. SELECT... Read more →

Monitor SQL Server TempDB usage

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage How to Troubleshoot TempDB First Step - Comprehensive Tempdb Sizing exercise. There are many different methods , such as : a) create a workload b) running large maintenance jobs e.g index rebuild Second Step - monitoring Tempdb usage. As well as a SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs to drill into... Read more →

SQL Server - Find last sql statement based on SPID

Find the last query based on a SPID can be useful . For example, a client may be experiencing SQL performance issues and the DBA needs to isolate the last run query. There are three methods below to return the query. Before we explore the methods – you’ll need to obtain the SPID. The SPID is the session User ID. Try: --this returns your current session ID SELECT @@SPID --this returns list of all sessions-check the spid column sp_who Method 1 - DBCC INPUTBUFFER ----------------------------- DBCC INPUTBUFFER returns the last sql statement issued by a client. The command requires the... Read more →

SQL Server – LATCH_EX AND how to reduce it

SQL Server Books online defines the SQL wait type LATCH_EX as “Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.” Note: 1)The definition mentions “does not include buffer latches or transaction mark latches”. This suggests this wait type is not related to IO or data. How to reduce LATCH_EX wait type 1) Investigate the DMV sys.dm_os_latch_stats for latch contention source. Most sources are esoteric and there is very little in-depth... Read more →