SQL SERVER – Difference Between Status in SP_Who2 – Running, Pending, Runnable, Suspended, Sleeping

One of the most popular questions I receive during my Comprehensive Database Performance Health Check is a difference between status in sp_who2 which discusses status as Running, Pending, Runnable, Suspended. Let us today understand them with regards to CPU and IO.

SQL SERVER - Difference Between Status in SP_Who2 - Running, Pending, Runnable, Suspended, Sleeping statusofsp_who2

First, let us see the definition:

Solarwinds

Running – The session with this status is actually running the batches and consuming CPU cycles.

Runnable – The session with this status is actually have been assigned a thread but waiting for the CPU cycle to be available.

Pending – The session with this status has yet not been assigned a thread and is waiting for the threads to be available.

Suspended – The session with this status is often waiting for the resources to be available as well. I have often seen this with more with I/O operation completion over CPU issues.

Sleeping – The session with this status is actually not doing anything. I often see this status when all the tasks related to the threads are completed but the connection is still open. (You can open a new connection in SQL Server Management Studio and do not execute anything there. Next, check the status of the SPID and you will notice that the status is Sleeping).

So this time when you execute sp_who2, you will quickly know what each thread mean.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
Changing Trends & Technology – Challenging DBAs
Next Post
SQL SERVER – List Query Plan, Cache Size, Text and Execution Count

Related Posts

1 Comment. Leave new

Leave a Reply

Menu