Execution Model in SQL Server 2005-2008:
The execution of queries in SQL Server are done One at a time per Scheduler. Which means a scheduler can only have one session running at a time.The SQL Server SQLOS uses schedulers which map to CPUs on the SQL Server(4 CPU = 4Schedulers by default), to manage the execution of user requests. SQLOS Schedulers map to CPUs.
Execution Model showing Running, runnable and suspended tasks:
There will be only one session Running or executing per scheduler. SPIDs with suspended statuses are placed in Waiter List until the requested resources are available. If a running session needs a data page that is not in cache, or needs a page that is blocked by another user’s lock, the session is moved to the wait list. The next SPID or session_id in the runnable queue is scheduled to start running.
The status change sequence of events is as follows:
1. SPID75 needs a page not in cache. Thus its status changes from Running to Suspended with wait type IO_Completion
2. The next SPID from Runnable Queue is moved to Running Queue.
3. The SPID whose waiting is done or the resources for which it was waiting is now available then its moved to Runnable Queue.
Execution Model showing Running, runnable and suspended tasks after SPID 70 moved to Waiter List:
The wait list means a thread has to wait for a resource to be available or for a action to be completed. The different types of resource waits includes IOs to complete, a lock to be released, a memory grant, and so on. When the session is moved to the wait list, a wait type is assigned and time is accumulated. When the resource becomes available, the thread is moved to the runnable queue and it executes as soon as the CPU is available. The clockwise rotation between running, runnable and suspended states continues until the user request is completed.
Waiter List and Wait Types
The different types of Wait Types can be read here.
The current wait list can be seen in sys.dm_os_waiting_tasks. The current runnable queue is found in sys.dm_exec_requests where the status is “runnable”. The total time that is spent waiting in sys.dm_os_waiting_tasks is found in the column wait_time_ms and the time that is spent waiting for CPU in the runnable queue is called signal_wait _time_ms. Resource waits can be computed by subtracting signal_wait_time_ms from wait_time_ms.
Dynamic Management Views (DMVs) and Functions (DMFs)
Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are useful in troubleshooting the issues with SQL Server. Please read this article for more information.
To Retrieve Waiter List Information use the below Query:
select session_id
, exec_context_id
, wait_type
, wait_duration_ms
, blocking_session_id
from sys.dm_os_waiting_tasks
where session_id > 50
order by session_id, exec_context_id
For Top Waits Rollup Query giving Running percentage for Waits use the Query here.