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 with the  sys.dm_os_schedulers and sys.dm_exec_requests  DMVs

This leads us into the RUNNABLE – SUSPENDED – RUNNING 

1)      RUNNING – thread is executing on the server

2)      SUSPENDED – thread is waiting for resources to become available.

3)      RUNNABLE – the thread is waiting to execute on the processor

 

Why does RUNNING transition to SUSPENDED ? Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list 

Why does SUSPENDED  transition into RUNNABLE? The resource is now available and moves to the bottom of the RUNNABLE queue. 

Why does RUNNABLE transition into RUNNING?  Top spid at head of RUNNABLE queue moves to processor

Read More

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement

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 is complicated formula on the MSDN Online - http://msdn.microsoft.com/en-us/library/ms189124.aspx to predict the space required to store data in a heap

There are some reliable methods of estimating  row sizes . Using DBCC SHOWCONTIG  , sys.dm_db_index_physical_stats  and SysColumns.

Each method offers a slightly different perspective  on row size.

 

 

---METHOD 1  : DBCC SHOWCONTIG

dbcc showcontig ('TableName') with tableresults

--check max min and average record size



--METHOD 2 : sys.dm_db_index_physical_stats
--dbcc showcontig is to be deprecated 
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')


--METHOD 3 : SysColumns

use db_name
go
create table ##tmpRowSize (TableName varchar(100),RowSizeDefinition int)
exec sp_msforeachtable 'INSERT INTO ##tmpRowSize Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '
select * from ##tmpRowSize order by RowSizeDefinition  desc
drop table ##tmpRowSize

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Calculate table size with existing data

Storage Checklist FAQ

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 * 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255;

 Read More

SOS_SCHEDULER_YIELD and how to reduce it - SQL Server DBA

Affinity Mask on SQL Server 2008 R2 - SQL Server DBA

How to report a SQL Server performance problem - SQL Server DBA

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

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 TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

 

go
select * from  sys.dm_db_file_space_usage 
go 
select * from  sys.dm_db_session_space_usage
go
select * from sys.dm_db_task_space_usage

Read More

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart

 

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 SPID

 

DBCC INPUTBUFFER (SPID)

 

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

-----------------------------------

Obtain the sql_handle from sys.sysprocesses and use the sql_handle to obtain the sql text from the DMV sys.dm_exec_sql_text.

 

DECLARE @sqlhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle from sys.sysprocesses where spid = 987
SELECT text FROM sys.dm_exec_sql_text(@sqlhandle)

 

Method 3 – SYS.FN_GET_SQL

---------------------------------

Use sys.fn_get_sql to return SQL statement text based on a SQL handle. Microsoft intend deleting this function in an unspecified future version of SQL Server          

 

DECLARE @sqlhandle varbinary(64);
SELECT @sqlhandle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 987 and request_id = 0;
SELECT * FROM sys.fn_get_sql(@sqlhandle);

 Read More

dm_exec_sql_text and Display text of SQL - SQL Server DBA

SQL Server - SQL open transactions and how to find

SQL Server – Current queries executing on SQL Server