SQL Server RUNNABLE – SUSPENDED – RUNNING status model

Question: When I’m viewing activity on SQL Server , for example, sp_who2 – the status column displays different states – RUNNABLE – SUSPENDED – RUNNING. Could you explain the difference? Answer: Some background information on the SQL Schedulers , will make understanding the RUNNABLE – SUSPENDED – RUNNING model clearer. Schedulers are made up of three parts . A thread cycles though these three parts 1) Processor 2) Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources 3) Runnable – thread has all the resources and waiting for the processor. Explore runnable status... Read more →

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 →