SQL Server RUNNABLE – SUSPENDED – RUNNING status model

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 andsys.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

Reference:http://www.sqlserver-dba.com/dmv/