Different Status if SPID

Different Status of SPID

We all know that SPID is a process identifier assigned by the SQLOS for each process running under its context. When we query sysprocess table or sp_who2 we will be noticing all processes listed with different statuses like BACKGROUND, RUNNING, RUNNABLE, PENDING etc...

Let’s look in more details of these and what they exactly mean....

RUNNING:

This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.

What this actually means is, the client connected to SQL Server using this session has already submitted a query for SQL Server to process and SQL Server is currently processing the query. The query could be anywhere between generating a parser tree to performing a join to sorting the data... and it is consuming the CPU (Processor) cycles currently.

SUSPENDED:
It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed.

For example, if the query the has posted a I/O request to read data of a complete table Person. Details then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table Person. Details is available in the memory), query will move into RUNNABLE queue.

RUNNABLE:
The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

The RUNNABLE queue can be likened to a grocery analogy where there are multiple check out lines.  The register clerk is the CPU.  There is just one customer checking out e.g. “RUNNING” at any given register.  The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running to get out so that it can start RUNNING.

PENDING:
The request is waiting for a worker to pick it up.

This means the request is ready to run but there are no worker threads available to execute the requests in CPU.  This doesn't mean that you have to increase 'Max. Worker threads", you have to check what the currently executing threads are doing and why they are not yielding back. I personally have seen more SPID's with status PENDING on issues which ended up in "Non-yielding Scheduler" and "Scheduler deadlock". 

BACKGROUND:
The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING:

There is no work to be done.