Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Find the longest-running active user requests
  5. 2. Show the currently executing statement inside the batch
  6. 3. Separate CPU-heavy from wait-heavy requests
  7. 4. Find long-running blocked requests
  8. 5. Find long-running requests with high logical reads
  9. 6. Find historically slow cached queries
  10. 7. Find queries with the highest maximum elapsed time
  11. 8. Group slow queries by query hash
  12. 9. Use Query Store for durable historical slow-query analysis
  13. 10. Compare plans for one slow query
  14. What the result means
  15. What to look for
  16. High elapsed time + low CPU
  17. High elapsed time + high CPU
  18. High elapsed time + high logical reads
  19. blocking_session_id > 0
  20. Large min/max duration spread
  21. Query Store shows same query with multiple plans
  22. Production notes
  23. Long-running does not mean bad
  24. Active and historical data come from different sources
  25. Plan-cache history is transient
  26. Averages can hide outliers
  27. Elapsed time includes waiting
  28. Permissions vary by version
  29. Common mistakes
  30. 1. Calling the longest query “the problem”
  31. 2. Treating elapsed time as CPU time
  32. 3. Looking only at averages
  33. 4. Ignoring execution count
  34. 5. Killing a long-running session without context
  35. 6. Assuming a slow query always needs an index
  36. 7. Using plan-cache stats as durable history
  37. Next step
  38. 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 requests
  • sys.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.

Next step

Use the runtime pattern to choose the next investigation.

  • Blocked → find the head blocker and inspect open transactions.
  • High CPU → inspect the current/previous execution plan and CPU-heavy workload.
  • High reads → inspect scans, predicates, joins, and indexing.
  • Meaningful wait type → follow the wait category.
  • Large min/max duration spread → investigate parameter sensitivity and plan stability.
  • Plan changed when performance degraded → compare Query Store plans.
  • Problem already disappeared → use Query Store and persisted monitoring.
  • Long duration is expected → validate SLA and resource impact before tuning.

← All cheat sheets