Seeing What Is Running Right Now

Users say the server is slow, and you need a live answer. Seeing what is running right now starts with active requests, their waits, and the statement each session is executing.

Use a Live Snapshot of What Is Running Right Now

sys.dm_exec_requests contains requests active at the moment you query it. A request that finished a second earlier is gone. That makes the DMV excellent for what is running right now and weak for reconstructing yesterday. Capture several snapshots during a complaint if the problem comes and goes. Include the sample time in saved output.

I keep a small query ready rather than building one while users wait. It should show session, database, command, status, elapsed time, CPU, reads, wait, and blocker. Those columns tell you where to investigate next. They do not prove which query caused the overall slowdown without workload context.

See Active Requests for What Is Running Right Now

The first query is a compact view of user requests. It excludes the session running the diagnostic query. blocking_session_id shows the immediate blocker when one exists. wait_type and wait_resource describe the current wait, which can change between samples. A high elapsed time alone does not mean the query is consuming CPU; it can spend much of that time waiting.

I sort by elapsed time to find long-running requests, then read wait and resource columns before making a claim. A long backup or index operation can be expected. Ask whether the work fits the schedule.

SELECT SYSDATETIME() AS sample_time,
       r.session_id,
       DB_NAME(r.database_id) AS database_name,
       r.command,
       r.status,
       r.total_elapsed_time,
       r.cpu_time,
       r.logical_reads,
       r.wait_type,
       r.wait_resource,
       r.blocking_session_id
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Read the Current Statement

CROSS APPLY sys.dm_exec_sql_text retrieves the batch text associated with a SQL handle. The request stores statement offsets that identify the active statement within a longer batch. Without those offsets, a batch containing several statements can mislead you. The example returns the full batch and the extracted current statement. Offsets are byte positions, so the division by two matters for Unicode text.

I read the statement together with waits and the database context. A query that looks harmless in isolation can touch a large table through a view. If the text is encrypted or unavailable, the function can return null. State that limit rather than filling in an assumed query.

SELECT r.session_id,
       r.wait_type,
       r.blocking_session_id,
       t.text AS batch_text,
       SUBSTRING(t.text,
                 (r.statement_start_offset / 2) + 1,
                 ((CASE r.statement_end_offset
                     WHEN -1 THEN DATALENGTH(t.text)
                     ELSE r.statement_end_offset
                   END - r.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID;

Add Plan Context Carefully

sys.dm_exec_query_plan can expose the cached plan for an active request. A plan helps you inspect joins, scans, and spills, but retrieving many large XML plans adds load to the diagnostic query and the SSMS client. Fetch plans for a few candidate sessions after the first compact snapshot. Plans can change and a cached plan does not show every runtime detail.

I avoid opening dozens of plans during an incident. First identify the request that is consuming resources or blocking others. Then inspect its plan and relevant actual runtime data through supported monitoring if available. A plan is a map of execution choices, not a record of every row the query processed.

Follow Blocking Separately

A positive blocking_session_id identifies the immediate blocker. Follow that chain until you reach a session that is not itself blocked. The head can be sleeping with an open transaction and therefore absent from sys.dm_exec_requests. Join to sys.dm_exec_sessions and transaction information when needed. Do not kill a session based on one row without understanding the transaction and rollback cost.

I have seen the loudest waiting query blamed for a slowdown when it was only the first victim in line. The head blocker deserves attention, but the business operation behind it matters too. Ask what statement opened the transaction and whether it is still doing useful work. A safe ending can be an application fix rather than an emergency KILL.

Read Waits With Their Units

Request wait_time is a current wait measure, while total_elapsed_time covers the request duration. sys.dm_os_waiting_tasks can show task-level waits, which helps when a parallel request has several workers. A single request row is a useful summary, not a full worker inventory. Always check whether the wait is current or cumulative before comparing values.

I note the sample time and take another snapshot. If the same request remains stuck on the same resource, the case is stronger. If waits move quickly, look at broader workload patterns. What changed when users first reported the issue? That clue can narrow the search faster than sorting a DMV by one number.

Respect Permissions and Privacy

Modern SQL Server versions use specific performance-state permissions for several DMVs. If a query returns a permission error or limited rows, ask for the appropriate approved access. Do not grant broad server control just to run a diagnostic query. SQL text can contain sensitive literals, so handle saved output according to data policy.

I keep a versioned copy of the query in the operations runbook with required permissions. That saves time during an incident and prevents improvisation with elevated accounts. When sharing a result, include only the fields needed to explain the problem. The best troubleshooting screenshot is rarely the one with the most customer data in it.

Know What a Snapshot Cannot Say

A live request query cannot tell you the history of a query that already finished. Use Query Store, Extended Events, or application telemetry for past activity. It also cannot prove that one busy request caused all reported latency. Compare with CPU, memory, I/O, waits, and the affected workload before drawing that conclusion.

I close an incident note with observed facts and open questions. For example, record the active statement, wait, blocker, and sample time. Then document whether the condition repeated. That is more useful than saying the server was busy. A reusable query for what is running right now buys you speed; careful interpretation buys you accuracy.

Capture More Than One Sample of What Is Running Right Now

A request snapshot becomes more useful when repeated at a measured interval during a live complaint. Save sample time, session ID, statement, wait, and blocker so you can see whether work persists or changes. I compare several snapshots before naming a culprit. A query that finished between samples belongs in Query Store or another history source, not in a story invented from an empty DMV. Keep collection small and stop after the incident. A concise sequence helps the next DBA distinguish a persistent block from a brief surge of ordinary work. Document the interval so comparisons make sense.

Related reading on this blog: Representing sp_who2 with DMVs and Long Running Queries with Execution Plan.

A live request list is not a history report, it is a snapshot that tells you where to look next.

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