SQL server DB status runnable, sleeping, suspended, running and background meanings

Question

I am not able to get the meanings of DB Session status. I searched it for some references, but not getting any help. Can anyone explain these status (runnable, sleeping, suspended, running and background) what actually means, that would be help me a lot. Thanks in advance.

Answer 1

Here is list of this in your question answered easy way.. Answer

Some of the most seen status of SPID's in SQL Server and what do they 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 it 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 tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents 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 (with register clerk) to get out so that it can start RUNNING. You can use the query SELECT wait_type,waiting_tasks_count,signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY signal_wait_time_ms DESC to find out the difference between the time the waiting thread was signaled and when it started running. This difference is the time spent in RUNNABLE queue. Some of the waits on the top of the list can be safely ignored.

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.

Answer 2

The status values are documented with sp_who:

The possible values are:

Dormant - SQL Server is resetting the session.

Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).

Background - The session is running a background task, such as deadlock detection.

Rollback - The session has a transaction rollback in the process.

Pending - The session is waiting for a worker thread to become available.

Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

Spinloop - The session's task is waiting for a spinlock to become free.

Suspended - The session is waiting for an event, such as I/O, to complete.