DaveWentzel.com            All Things Data

SQL Server Execution Model


This is a simplified overview of the SQL Server execution model.  However, understanding how this works will help you understand waits and q's much better. 
If session_id 100 is running and must lock a resource X that is unavailable at the time, 100 moves to the resource wait list (time T0)
A signal later indicates the X is available, session_id 100 moves to the runnable q at time T1. 
Session_id 100 waits until time T2 as the CPUs go through the runnable q in order of arrival.  When session_id 100 finally acquires time on a SQLOS scheduler then it will show as Running.  Only one session can be Running on a scheduler at a time.  The Suspended status shows when the spid is placed on the waiter list until the requested resource is freed.  A wait state is assigned indicating the reason why it is suspended.  The waiter list is shown in sys.dm_os_waiting_tasks.  The current runnable q can be found in sys.dm_exec_requests where the status=runnable.  Time spent in the runnable q can be found in dm_os_waiting_tasks by looking at signal_wait_time_ms.  Resource waits are calculated by wait_time_ms-signal_wait_time_ms.  Sleeping indicates the server is waiting on the client to issue another command. 
The resource wait time is the actual time waiting for the resource to be available, or T0 to T1. 
Signal wait time is time spent in the runnable q.  T2-T1. 
If T1-T0 is larger then the bottleneck is resource X. 
Signal wait time indicates CPU pressure.  Session_ids in the runnable q are waiting only for CPU, if this value is large the bottleneck is CPU. 
The highest waits indicate the bottleneck you need to solve for scalability. 

Add new comment