Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: TroubleshootingDifficulty: IntermediateUpdated

Find What Is Running in SQL Server

Quickly identify active SQL Server requests, what they are executing, how long they have been running, what resources they are consuming, and whether they are waiting or blocked.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Show the longest-running active user requests
  5. 2. Show the currently executing statement, not just the whole batch
  6. 3. Show active requests consuming the most CPU so far
  7. 4. Show active requests doing the most logical reads
  8. 5. Find active blocked requests
  9. 6. Inspect one session in detail
  10. What the result means
  11. What to look for
  12. Long elapsed time with low CPU
  13. High CPU relative to other active requests
  14. High logical reads
  15. blocking_session_id > 0
  16. wait_type populated and status is suspended
  17. Unexpected host_name or program_name
  18. Production notes
  19. DMV data is transient
  20. Permissions differ by SQL Server version
  21. Query text can contain sensitive information
  22. This query is a snapshot, not a workload baseline
  23. Avoid SELECT * in reusable DMV tooling
  24. Common mistakes
  25. 1. Looking only at status = 'running'
  26. 2. Treating elapsed time as CPU time
  27. 3. Killing a session before understanding it
  28. 4. Assuming the blocked query is the problem
  29. 5. Adding an index because a request has many reads
  30. 6. Using this DMV as historical evidence
  31. Next step
  32. Related deep dives

Problem

SQL Server is slow, CPU is elevated, users are reporting delays, or you simply need to know what is executing right now.

The goal is not just to list sessions. You want enough context to answer:

  • What statement is executing?
  • How long has it been running?
  • How much CPU and I/O has it used?
  • Is it waiting?
  • Is another session blocking it?
  • Which login, host, and application started it?

Quick answer

Use sys.dm_exec_requests for currently executing requests, join to sys.dm_exec_sessions for session context, and use sys.dm_exec_sql_text to retrieve the SQL text.

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

Start here when the problem is happening. This is a live snapshot, so run it while the slowdown or incident is active.

Code / commands

1. Show the longest-running active user requests

Use this when you need a fast first look and do not need all session metadata.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id
FROM sys.dm_exec_requests AS r
INNER 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;

Use this as a lightweight triage query when you already know how to inspect a specific session afterward.


2. Show the currently executing statement, not just the whole batch

A batch can contain many statements. During troubleshooting, the useful question is often:

Which statement inside this batch is executing right now?

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.total_elapsed_time DESC;

Use statement_text for the statement SQL Server is currently executing and batch_text when you need the surrounding batch or stored procedure call.


3. Show active requests consuming the most CPU so far

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.cpu_time DESC;

Use this when CPU is currently elevated and you want to identify which active requests have accumulated the most CPU time.

Do not confuse this with a historical “top CPU queries” report. sys.dm_exec_requests shows active requests, not completed workload history.


4. Show active requests doing the most logical reads

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.logical_reads,
    r.reads,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.wait_type,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.logical_reads DESC;

High logical reads can point you toward expensive scans, poor selectivity, inefficient access paths, or simply a legitimately large workload.

Do not assume “high reads = missing index.” Inspect the execution plan and workload context before deciding on a fix.


5. Find active blocked requests

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    txt.text AS blocked_batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.blocking_session_id > 0
ORDER BY
    r.wait_time DESC;

Use this to quickly confirm normal session-to-session blocking.

A positive blocking_session_id identifies another session as the blocker. SQL Server can also return special negative values in some internal or distributed transaction scenarios, so do not treat every nonzero value as a normal session ID.


6. Inspect one session in detail

Once you have a suspicious session_id, narrow the scope.

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    r.total_elapsed_time,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.open_transaction_count,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id = @session_id;

This is often the better second step than repeatedly scanning every active request.

What the result means

Column Why it matters
session_id Identifier used to correlate the request with sessions, blocking, transactions, and other DMVs.
status Current request state such as running, runnable, or suspended.
command High-level operation SQL Server is performing, such as SELECT, UPDATE, or BACKUP DATABASE.
database_name Database context for the request.
start_time When the current request started.
elapsed_seconds Wall-clock time the current request has been active.
cpu_ms CPU time accumulated by the current request.
logical_reads Pages read from SQL Server’s buffer pool by the request. Useful for spotting read-heavy work.
reads Physical reads performed by the request.
writes Writes performed by the request.
wait_type What the request is currently waiting on when it is suspended.
wait_ms Current wait duration for the request.
wait_resource Resource associated with the current wait when available.
blocking_session_id Session currently blocking this request. 0 normally means no blocking session is identified.
login_name Login that owns the session.
host_name Client machine name reported by the connection.
program_name Application name reported by the client connection.
statement_text Statement currently executing inside the batch.
batch_text Full SQL batch associated with the request.

What to look for

Long elapsed time with low CPU

Possible interpretation:

  • the query may be waiting rather than actively consuming CPU
  • inspect status, wait_type, wait_time, and blocking_session_id

If status = suspended, the wait information is usually more useful than CPU alone.

High CPU relative to other active requests

Possible interpretation:

  • CPU-intensive expression or operator
  • large joins or aggregations
  • inefficient execution plan
  • compilation/recompilation pressure
  • simply a legitimately expensive workload

Next, inspect the execution plan and compare the request with server-wide CPU conditions.

High logical reads

Possible interpretation:

  • large scan
  • poor predicate selectivity
  • inefficient join strategy
  • weak indexing for the access pattern
  • large expected workload

Do not create an index based on logical reads alone. Confirm the access pattern and execution plan.

blocking_session_id > 0

The request is waiting on another session.

Move immediately to blocking-chain analysis instead of tuning the blocked query in isolation.

wait_type populated and status is suspended

The request is waiting for some resource or event.

The wait type tells you where to investigate next. It does not automatically identify the root cause.

Unexpected host_name or program_name

This can reveal:

  • application traffic hitting the wrong database
  • unexpected scheduled jobs
  • reporting traffic during peak workload
  • API clients producing more activity than expected
  • administrative tools left running

Use connection metadata as supporting evidence, not as proof of causation.

Production notes

DMV data is transient

sys.dm_exec_requests describes requests that are executing now.

Once a request finishes, it disappears from this DMV. If an incident is intermittent, capture the output while the problem is happening or use Query Store/monitoring for historical analysis.

Permissions differ by SQL Server version

For SQL Server 2019 and earlier, seeing all executing sessions normally requires VIEW SERVER STATE.

For SQL Server 2022 and later, the relevant permission is VIEW SERVER PERFORMANCE STATE.

Azure SQL Database has different visibility and permission rules, so do not assume an on-premises/server-level query will expose every session there.

Query text can contain sensitive information

sys.dm_exec_sql_text can expose SQL batches containing literals or other application data.

Be careful when:

  • copying query text into tickets
  • storing DMV snapshots
  • sending troubleshooting output through chat or email
  • publishing examples

Sanitize sensitive values before sharing.

This query is a snapshot, not a workload baseline

A request at the top of the list is not automatically the root cause.

Compare:

  • CPU
  • reads
  • elapsed time
  • wait state
  • blocking
  • concurrency
  • server-level symptoms

before making a tuning decision.

Avoid SELECT * in reusable DMV tooling

Explicitly select the columns you need. DMV schemas can evolve between SQL Server versions, and explicit columns also make diagnostic output easier to interpret.

Common mistakes

1. Looking only at status = 'running'

A request in suspended state can be the most important request on the server because it may be waiting on blocking, I/O, memory, or another resource.

Inspect active requests regardless of whether they are currently on CPU.

2. Treating elapsed time as CPU time

A query that has run for 60 seconds has not necessarily consumed 60 seconds of CPU.

Compare:

  • total_elapsed_time
  • cpu_time
  • waits

before deciding whether the problem is CPU pressure.

3. Killing a session before understanding it

Do not make KILL <session_id> the default troubleshooting action.

Before terminating a session, understand:

  • what it is doing
  • whether it owns an open transaction
  • what application owns it
  • the business impact
  • the rollback cost

4. Assuming the blocked query is the problem

A blocked request is often the victim.

Find the head blocker and determine why it is holding locks before tuning the blocked statement.

5. Adding an index because a request has many reads

High reads are a symptom, not an index recommendation.

Validate the execution plan, predicates, data distribution, existing indexes, and write cost before changing indexing.

6. Using this DMV as historical evidence

Completed requests disappear from sys.dm_exec_requests.

For historical investigation, move to Query Store, monitoring data, Extended Events, or another persisted telemetry source.

Next step

Use the result to choose the next investigation.

  • blocking_session_id > 0 → inspect the blocking chain and identify the head blocker.
  • High cpu_time → inspect high-CPU queries and the current execution plan.
  • High logical_reads → inspect read-heavy queries, access paths, predicates, and indexing.
  • Long elapsed time + meaningful wait_type → investigate the wait category.
  • Unexpected host/application → group workload by login, host, and program name.
  • The request already finished → use Query Store or persisted monitoring instead of sys.dm_exec_requests.
  • Nothing obvious appears → continue with the broader SQL Server performance troubleshooting workflow.

← All cheat sheets