Running, Runnable, Suspended: The Life of a Query in sys.dm_exec_requests

A query can remain active without using the processor at that instant. Its runnable or suspended state tells you whether it is waiting for CPU time or for another resource to become ready.

Follow Work From Running to Runnable to Suspended

A running task currently executes on a scheduler. A task waiting for a resource becomes suspended. When the resource is ready, the task joins the ready queue before getting another turn on the processor. That queueing state is different from waiting for a lock or disk read to finish.

I separate request activity from processor activity when reading a live server. An active request can spend much of its lifetime waiting. A sleeping session has no currently executing request, yet can retain an open transaction. Session, request, and task are different levels of the engine's work.

The state changes quickly. One sample is a moment in an execution lifecycle, rather than the query's permanent identity. Take several short observations during the affected period. Preserve their capture times and compare them with application response. A query does not sign a lease on its current status.

Read the Current Requests

sys.dm_exec_requests lists active requests. Include elapsed time, CPU time, waits, and the blocking session so the status has context. Exclude your monitoring session when you want to focus on other work. Join session metadata only for labeling, not as proof of which person initiated the business operation.

SELECT r.session_id, r.request_id, r.status, r.command,
       DB_NAME(r.database_id) AS DatabaseName,
       r.cpu_time, r.total_elapsed_time,
       r.wait_type, r.wait_time, r.blocking_session_id,
       s.program_name, s.host_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;

On SQL Server 2022 and later, the relevant performance DMV access requires VIEW SERVER PERFORMANCE STATE. Earlier versions use their documented state permission. Test the monitoring identity's actual visibility. A result containing only your own work can reflect permissions rather than an idle server.

Understand a Suspended Request's Resource

A suspended task waits until another condition lets it proceed. Lock waits, buffer reads, memory grants, and synchronization create different explanations. Read the wait type and resource before acting. The status alone does not tell you which resource is slow or which change would help.

A lock wait needs a blocking investigation and transaction ownership. An I/O wait needs an access-path and storage review. A memory-grant wait needs concurrent grants and plan estimates. Avoid using the same response for every suspended request. Buying more CPU does little for a transaction waiting behind an unfinished lock owner.

The request-level wait reflects the coordinating request's view. Parallel execution introduces several tasks with their own activity. Some workers can run while others wait. Inspect the task and waiting-task views when a single request row seems inconsistent with the plan's actual work.

Inspect Running, Runnable, and Suspended Tasks

sys.dm_os_tasks exposes execution contexts belonging to requests. A parallel request can have several rows. Join on both session_id and request_id so separate requests under one session do not get mixed. Keep task address and context identifiers when following a particular worker's evidence.

SELECT t.session_id, t.request_id, t.exec_context_id,
       t.scheduler_id, t.task_state, t.task_address,
       r.status AS RequestStatus, r.command
FROM sys.dm_os_tasks AS t
LEFT JOIN sys.dm_exec_requests AS r
  ON r.session_id = t.session_id AND r.request_id = t.request_id
WHERE t.session_id IS NOT NULL AND t.session_id <> @@SPID
ORDER BY t.session_id, t.request_id, t.exec_context_id;

RUNNING, RUNNABLE, and SUSPENDED appear as task states, alongside other internal lifecycle states. Do not count every task as one independent user query. The execution-context identifier helps reveal parallel workers. A high task population needs interpretation against request count and execution width.

Use a selected session filter for a focused incident. Sampling all tasks repeatedly can produce distracting volume on a busy instance. Preserve the request identifiers and capture time before narrowing the view. A rapidly finishing query can disappear between related queries, so live joins cannot promise a frozen picture.

Check the Runnable Queue on Each Scheduler

Schedulers manage ready work. runnable_tasks_count measures tasks waiting for their turn. Sustained queue growth across visible online schedulers supports CPU-pressure investigation. One brief nonzero value is part of ordinary scheduling. Look for repeated pressure during the period when application latency increased.

SELECT scheduler_id, cpu_id, runnable_tasks_count,
       current_tasks_count, active_workers_count,
       work_queue_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC, scheduler_id;

Compare schedulers rather than relying only on one sum. Uneven load can concentrate pressure on particular schedulers. Processor affinity, parallelism, and workload shape affect that distribution. Windows CPU counters help establish whether the host is busy or whether virtual-machine scheduling introduces another constraint.

I keep queue samples beside processor utilization when interpreting the pattern. A server can have low average CPU while one important serial query saturates a single execution path. An instance-wide average hides that local limitation. Check the affected query's plan before concluding that more parallel execution is the right response.

Join Live Wait Evidence Carefully

The waiting-task view describes tasks currently waiting. It helps connect a request to resource descriptions and blocking information. Join its task address to the task inventory for request context. Keep the output as one sample of current activity rather than a total accumulated since startup.

SELECT t.session_id, t.request_id, t.exec_context_id,
       w.wait_type, w.wait_duration_ms,
       w.blocking_session_id, w.resource_description
FROM sys.dm_os_waiting_tasks AS w
LEFT JOIN sys.dm_os_tasks AS t
  ON t.task_address = w.waiting_task_address
WHERE t.session_id IS NOT NULL AND t.session_id <> @@SPID
ORDER BY w.wait_duration_ms DESC;

A waiting task's duration answers a different question from a request's total elapsed time. A long request can contain many short waits. Some blockers are special internal owners rather than ordinary positive session identifiers. Read the documented meaning before issuing a command against a guessed session.

Match State Evidence to a Resource Decision

Which part of the lifecycle dominates the slow operation? Repeated resource waits point toward the specific dependency. Repeated ready-queue pressure points toward access to CPU. Growing elapsed time with modest CPU consumption needs a waiting explanation. Keep these distinctions visible when presenting the incident.

Also inspect query work. Excessive reads or unnecessary rows can create both CPU and I/O pressure. A state sample describes where work is delayed, but it does not prove that all the work was necessary. Compare estimates, actual rows, and access paths before proposing a capacity increase.

Use the runnable queue as a signal within that investigation. The runnable count becomes useful when repeated samples, host counters, and affected requests agree. Capture the evidence before any reset or tuning change, then repeat the same observations after a justified intervention.

Related reading on this blog: Lots of Runnable SPID: What Next? and SQLOS Scheduler and the Process States.

An active request is not continuous CPU use, it is work moving through execution and waiting states.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.