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.
First, let us see the definition:
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)