Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. Show the longest-running active user requests
- 2. Show the currently executing statement, not just the whole batch
- 3. Show active requests consuming the most CPU so far
- 4. Show active requests doing the most logical reads
- 5. Find active blocked requests
- 6. Inspect one session in detail
- What the result means
- What to look for
- Long elapsed time with low CPU
- High CPU relative to other active requests
- High logical reads
- blocking_session_id > 0
- wait_type populated and status is suspended
- Unexpected host_name or program_name
- Production notes
- DMV data is transient
- Permissions differ by SQL Server version
- Query text can contain sensitive information
- This query is a snapshot, not a workload baseline
- Avoid SELECT * in reusable DMV tooling
- Common mistakes
- 1. Looking only at status = 'running'
- 2. Treating elapsed time as CPU time
- 3. Killing a session before understanding it
- 4. Assuming the blocked query is the problem
- 5. Adding an index because a request has many reads
- 6. Using this DMV as historical evidence
- Next step
- 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, andblocking_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_timecpu_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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.