In SQL Server, when you query the session status, you can encounter various states that provide information about what a session is currently doing. Here are some common session statuses and their meanings:
| Status | Meaning |
|---|---|
| Background | The SPID is running a background task, such as deadlock detection, log writer, or checkpoint. |
| Sleeping | The SPID is not currently executing. This usually indicates that the SPID is awaiting a command from the application. |
| Running | The SPID is currently running on a scheduler. |
| Runnable | The SPID is in the runnable queue of a scheduler and waiting to get scheduler time. |
| Suspended | The SPID is waiting for a resource, such as a lock or a latch. |
Runnable: A session in the “Runnable” state is ready to execute, but it’s waiting for the CPU to become available. This typically occurs when multiple queries are competing for CPU resources.
Sleeping: A session in the “Sleeping” state is inactive and not using CPU resources. It’s usually associated with sessions that are waiting for something to happen, such as a user or application not actively running queries.
Suspended: A session in the “Suspended” state is waiting for a specific event to complete, such as I/O operations, locks, or other resource-intensive tasks. It’s essentially paused until the required event finishes.
Running: A session in the “Running” state is actively executing a query or operation. It’s using CPU resources to perform tasks, and its query or operation is currently in progress.
Background: A session in the “Background” state represents system processes and tasks that run in the background to manage various aspects of the SQL Server instance. These processes can include tasks like memory management, log file maintenance, and other system-related functions.
In More Details
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.
Query to Find the status of your session
SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESCHow to identify whether these session are blocking in SQL Server
There are number of ways to find out the details of the system processes IDs (spids) involved in blocking.
- sp_who2 system stored procedure
- sys.dm_exec_requests DMV
- sys.dm_os_waiting_tasks
- SQL Server Management Studio Activity Monitor
- SQL Server Management Studio Reports
sp_who2 system stored procedure
The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The stored procedure can be filtered to return only the active processes by using the ‘active’ parameter.
EXEC sp_who2Below is sample code and a screen shot showing process 55 is being blocked by process 54.
sys.dm_exec_requests DMV‘
The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;sys.dm_os_waiting_tasks DMV
The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0SQL Server Management Studio Activity Monitor
To view the Activity Monitor in SQL Server, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
SQL Server Management Studio Reports
The second option in SQL Server Management Studio to monitor blocking is with the standard reports, which can be accessed by navigating to the instance name, right clicking on the instance name and selecting Reports > Standard Reports > Activity – All Blocking Transactions.
