SQL Server RUNNABLE – SUSPENDED – RUNNING status model

21 May,2014 by Jack Vamvas

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 model definitions

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 on using DMV for troubleshooting sql queries

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server RUNNABLE – SUSPENDED – RUNNING status model


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer