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
2) Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources
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