Meaning of sp_who Status in SQL Server

Understanding sp_who Status in SQL Server

Demystify the various status messages returned by sp_who and sp_who2 in SQL Server to effectively monitor and troubleshoot database activity.

The sp_who and sp_who2 stored procedures are invaluable tools for SQL Server administrators and developers. They provide a snapshot of current user activity, processes, and their states within the SQL Server instance. Understanding the 'Status' column is crucial for identifying bottlenecks, long-running queries, blocking issues, and other performance problems. This article will break down the common status messages you'll encounter and explain what each one signifies.

Common sp_who Status Messages

The 'Status' column in the output of sp_who or sp_who2 indicates the current state of a session or process. These states can range from idle connections to actively executing queries or processes waiting for resources. Interpreting these statuses correctly is key to diagnosing performance issues.

EXEC sp_who;
EXEC sp_who2;

Executing sp_who and sp_who2 to view current processes.

flowchart TD
    A[SQL Server Process] --> B{Status?}
    B -->|Sleeping| C[Idle/Waiting for Command]
    B -->|Runnable| D[Ready to Execute]
    B -->|Running| E[Actively Executing]
    B -->|Suspended| F[Waiting for Resource]
    B -->|Background| G[Internal Task]
    B -->|Rollback| H[Rolling Back Transaction]
    B -->|Pending| I[Waiting for Worker Thread]

Simplified flow of SQL Server process statuses.

Detailed Explanation of Key Statuses

Let's delve into the most frequently observed status messages and their implications:

Sleeping

This is a very common status and usually indicates that the session is currently idle, waiting for the client application to send the next command. It's not necessarily a problem unless there are too many sleeping connections holding onto resources unnecessarily.

Runnable

This status means the process is ready to execute but is waiting for an available CPU scheduler. It's in the queue to get CPU time. A high number of 'Runnable' processes can indicate CPU pressure.

Running

This signifies that the process is actively executing on a CPU. This is a normal and expected status for active queries. If a query stays in 'Running' for an unusually long time, it might be a long-running query or an inefficient one.

Suspended

This is one of the most critical statuses for troubleshooting. A 'Suspended' process is waiting for a resource to become available. This resource could be a lock, I/O completion, memory, network buffer, or a latch. Identifying what resource it's waiting for (using sys.dm_exec_requests and wait_type) is crucial for resolving performance issues.

Background

This status is typically associated with internal SQL Server tasks, such as checkpoint processes, lazy writer, or log writer. These are system processes and usually don't indicate a user-initiated problem.

Rollback

When a transaction encounters an error or is explicitly rolled back, the process enters this state. It's actively undoing changes made by the transaction. Long-running rollbacks can consume significant resources and indicate issues with large transactions or application errors.

Pending

This status indicates that a process is waiting for a worker thread to become available. This can happen under extreme load when all available worker threads are busy. It's a sign of severe resource contention, often CPU or I/O related, leading to a backlog of requests.

Advanced Monitoring with sys.dm_exec_requests

While sp_who and sp_who2 provide a quick overview, for more detailed insights into what a 'Suspended' process is waiting for, or to get the actual query text, you should use Dynamic Management Views (DMVs) like sys.dm_exec_requests and sys.dm_exec_sessions.

SELECT
    s.session_id,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.cpu_time,
    r.total_elapsed_time,
    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,
    qp.query_plan
FROM
    sys.dm_exec_requests r
JOIN
    sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY
    sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY
    sys.dm_exec_query_plan(r.plan_handle) qp
WHERE
    s.is_user_process = 1;

Querying sys.dm_exec_requests for detailed process information, including wait types and query text.