Browse cheat sheets · SQL Server
Find Long-Running Queries
Identify long-running SQL Server requests and historical slow queries, separate elapsed time from CPU time, and determine whether the delay comes from waits, blocking, reads, or plan regression.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Find the longest-running active user requests
- 2. Show the currently executing statement inside the batch
- 3. Separate CPU-heavy from wait-heavy requests
- 4. Find long-running blocked requests
- 5. Find long-running requests with high logical reads
- 6. Find historically slow cached queries
- 7. Find queries with the highest maximum elapsed time
- 8. Group slow queries by query hash
- 9. Use Query Store for durable historical slow-query analysis
- 10. Compare plans for one slow query
- What the result means
- What to look for
- High elapsed time + low CPU
- High elapsed time + high CPU
- High elapsed time + high logical reads
- blocking_session_id > 0
- Large min/max duration spread
- Query Store shows same query with multiple plans
- Production notes
- Long-running does not mean bad
- Active and historical data come from different sources
- Plan-cache history is transient
- Averages can hide outliers
- Elapsed time includes waiting
- Permissions vary by version
- Common mistakes
- 1. Calling the longest query “the problem”
- 2. Treating elapsed time as CPU time
- 3. Looking only at averages
- 4. Ignoring execution count
- 5. Killing a long-running session without context
- 6. Assuming a slow query always needs an index
- 7. Using plan-cache stats as durable history
- Next step
- Related deep dives
Problem
Users report that a query, report, API call, or batch is taking much longer than expected.
The first question is not simply:
Which query has been running the longest?
The useful questions are:
- Is the query still running?
- Is it actively consuming CPU or mostly waiting?
- Is it blocked?
- Is it doing excessive reads?
- Is the execution time abnormal for this query?
- Did the plan change?
- Is this one slow execution or a consistent pattern?
Quick answer
For queries running right now, use sys.dm_exec_requests.
SELECT TOP (50)
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,
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,
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;
Then ask why the request is slow:
- high CPU → CPU/query-plan path
- high reads → access-path path
blocking_session_id > 0→ blocking path- meaningful
wait_type→ wait-analysis path - nothing live anymore → Query Store/history path
Code / commands
1. Find the longest-running active user requests
SELECT TOP (50)
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,
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,
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;
This is the default live-triage query.
Do not treat the top row as the root cause automatically.
A request can be long-running because it is:
- blocked
- waiting on I/O
- waiting for CPU
- intentionally processing a large workload
- stuck behind another dependency
2. Show the currently executing statement inside the batch
A long-running stored procedure can contain many statements.
Use the request offsets to isolate the current statement.
SELECT
r.session_id,
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.wait_type,
r.blocking_session_id,
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;
This is usually more actionable than looking only at the full procedure text.
3. Separate CPU-heavy from wait-heavy requests
SELECT
r.session_id,
r.total_elapsed_time AS elapsed_ms,
r.cpu_time AS cpu_ms,
r.wait_time AS wait_ms,
r.wait_type,
r.blocking_session_id,
CAST(
100.0 * r.cpu_time
/ NULLIF(r.total_elapsed_time, 0)
AS decimal(6,2)
) AS cpu_to_elapsed_pct,
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 the ratio only as a triage aid.
Do not define a hard threshold.
Parallel queries can consume CPU across multiple workers, and elapsed time includes waiting.
4. Find long-running 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.total_elapsed_time DESC;
If a long-running request is blocked, tune the blocker path first.
The slow query may only be the victim.
5. Find long-running requests with high logical reads
SELECT TOP (50)
r.session_id,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.logical_reads,
r.reads AS physical_reads,
r.cpu_time AS cpu_ms,
r.wait_type,
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;
This helps identify queries that are slow because they are touching a lot of pages.
Next, inspect the execution plan.
6. Find historically slow cached queries
sys.dm_exec_query_stats is useful for recently completed queries whose plans are still in cache.
SELECT TOP (50)
qs.execution_count,
qs.creation_time,
qs.last_execution_time,
CAST(
qs.total_elapsed_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms,
CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
qs.total_logical_reads,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
qs.execution_count > 0
ORDER BY
avg_elapsed_ms DESC;
Use this when the live request is gone but plan-cache history is still available.
7. Find queries with the highest maximum elapsed time
Averages can hide intermittent slow executions.
SELECT TOP (50)
qs.execution_count,
CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,
CAST(
qs.total_elapsed_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.max_elapsed_time DESC;
A large gap between minimum and maximum duration is a signal worth investigating.
Possible causes include:
- parameter sensitivity
- blocking during some executions
- plan changes
- changing data volume
- resource contention
8. Group slow queries by query hash
Ad hoc literal differences can fragment plan-cache statistics.
SELECT TOP (50)
qs.query_hash,
SUM(qs.execution_count) AS executions,
CAST(
SUM(qs.total_elapsed_time) * 1.0
/ NULLIF(SUM(qs.execution_count), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_elapsed_ms,
CAST(
MAX(qs.max_elapsed_time) / 1000.0
AS decimal(18,2)
) AS max_elapsed_ms,
MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
qs.query_hash
ORDER BY
weighted_avg_elapsed_ms DESC;
Use this to aggregate logically equivalent query shapes.
9. Use Query Store for durable historical slow-query analysis
If Query Store is enabled:
SELECT TOP (50)
q.query_id,
p.plan_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms,
CAST(
MAX(rs.max_duration) / 1000.0
AS decimal(18,2)
) AS max_duration_ms,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY
q.query_id,
p.plan_id,
qt.query_sql_text
ORDER BY
weighted_avg_duration_ms DESC;
Use Query Store for:
- intermittent slow queries
- plan regressions
- before/after analysis
- history after plan-cache turnover
10. Compare plans for one slow query
DECLARE @query_id bigint = 123;
SELECT
p.plan_id,
p.is_forced_plan,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_cpu_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE
p.query_id = @query_id
GROUP BY
p.plan_id,
p.is_forced_plan
ORDER BY
weighted_avg_duration_ms DESC;
If one plan is consistently slower than another, investigate plan regression rather than treating the query text alone as the problem.
What the result means
| Field | Why it matters |
|---|---|
total_elapsed_time |
Wall-clock time for the active request. Includes waiting. |
cpu_time |
CPU consumed by the active request. |
wait_time |
Current wait duration for the request. |
wait_type |
Current wait category. Often the fastest clue to why elapsed time is high. |
blocking_session_id |
Session currently blocking the request, if any. |
logical_reads |
Pages touched through the buffer pool. High values can indicate broad access. |
avg_elapsed_ms |
Average elapsed time across completed cached executions. |
max_elapsed_ms |
Slowest observed completed execution in the current stats lifecycle. |
min_elapsed_ms |
Fastest observed completed execution in the current stats lifecycle. |
query_hash |
Useful for grouping logically equivalent queries. |
plan_id |
Query Store plan identifier for regression comparison. |
What to look for
High elapsed time + low CPU
This is often a wait problem, not a CPU problem.
Check:
- blocking
- I/O waits
- memory grants
- network/client consumption
- worker availability
High elapsed time + high CPU
The query may be actively expensive.
Inspect:
- execution plan
- scans
- joins
- sorts
- aggregations
- cardinality estimates
- parameter behavior
High elapsed time + high logical reads
The query may be processing more pages than necessary.
Look for:
- wide scans
- poor predicates
- nonselective joins
- missing/ineffective indexes
- large expected result sets
blocking_session_id > 0
Follow the blocker.
Do not tune the blocked query first unless the query itself is also problematic.
Large min/max duration spread
This can indicate unstable behavior.
Possible causes:
- parameter sensitivity
- plan changes
- inconsistent blocking
- changing workload
- data skew
Query Store shows same query with multiple plans
Compare plans by execution count, average duration, CPU, and reads.
A slower plan that appears after deployment or statistics/index changes is strong regression evidence.
Production notes
Long-running does not mean bad
Some operations are intentionally long:
- ETL
- index maintenance
- backups
- data loads
- large reports
- integrity checks
The question is whether the duration is expected for the workload and SLA.
Active and historical data come from different sources
Use:
sys.dm_exec_requests→ active requestssys.dm_exec_query_stats→ completed cached query stats- Query Store → persisted historical runtime and plans
Do not mix them as if they represent the same observation window.
Plan-cache history is transient
sys.dm_exec_query_stats rows disappear when the plan leaves cache.
Use Query Store for durable historical analysis.
Averages can hide outliers
A query averaging 100 ms can still occasionally take 30 seconds.
Review:
- max duration
- min duration
- plan history
- time-window data
when intermittent latency matters.
Elapsed time includes waiting
Do not compare elapsed time and CPU as if they are the same metric.
A query can spend most of its life waiting.
Permissions vary by version
For broad access to performance DMVs:
- SQL Server 2019 and earlier commonly require
VIEW SERVER STATE - SQL Server 2022 and later commonly require
VIEW SERVER PERFORMANCE STATE
Azure SQL Database has different scope and permission rules.
Common mistakes
1. Calling the longest query “the problem”
The longest-running request may simply be waiting behind another session.
Follow waits and blocking first.
2. Treating elapsed time as CPU time
Elapsed time includes waits.
A 60-second query may have used only a small amount of CPU.
3. Looking only at averages
Averages can hide one-off or parameter-specific regressions.
Review min/max and Query Store history.
4. Ignoring execution count
A query that runs once a day can be slow but low-impact.
A query that runs thousands of times can create more aggregate workload.
5. Killing a long-running session without context
Before terminating it, understand:
- business operation
- transaction state
- rollback cost
- blocking impact
6. Assuming a slow query always needs an index
The root cause may be:
- blocking
- bad plan
- stale statistics
- CPU pressure
- memory pressure
- client slowness
- storage latency
7. Using plan-cache stats as durable history
They are not permanent.
Use Query Store or external monitoring for historical evidence.
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.