Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: TroubleshootingDifficulty: IntermediateUpdated

SQL Server Performance Troubleshooting Flow

Use a repeatable SQL Server troubleshooting flow to separate blocking, CPU, I/O, waits, query regressions, and workload pressure before changing anything.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Confirm what is running right now
  5. 2. Check blocking immediately
  6. 3. Check current waits
  7. 4. Check top cumulative waits
  8. 5. Check active CPU-heavy requests
  9. 6. Check active read-heavy requests
  10. 7. Check Query Store for historical regression
  11. 8. Check open transactions when blocking or log growth exists
  12. 9. Check SQL Server start time before interpreting cumulative DMVs
  13. 10. Check index usage only after identifying the workload
  14. 11. Check missing-index recommendations carefully
  15. What the result means
  16. What to look for
  17. Blocking explains the slowdown
  18. CPU is high and active requests consume CPU
  19. Elapsed time is high but CPU is low
  20. Query Store shows a plan change at the same time performance degraded
  21. Server appears healthy but one application is slow
  22. Nothing looks wrong now
  23. Production notes
  24. Troubleshoot before changing configuration
  25. Live DMVs are transient
  26. Query Store is often the bridge between live and historical troubleshooting
  27. Infrastructure still matters
  28. Compare before and after one change
  29. Permissions vary by SQL Server version
  30. Common mistakes
  31. 1. Starting with indexes
  32. 2. Killing sessions too early
  33. 3. Looking only at one metric
  34. 4. Using cumulative waits as current evidence
  35. 5. Treating a missing index recommendation as the answer
  36. 6. Ignoring the application and infrastructure layers
  37. 7. Making multiple changes at once
  38. Next step
  39. Related deep dives

Problem

SQL Server is slow, users are complaining, dashboards are timing out, or an application suddenly takes longer than normal.

The hardest part is not running a DMV query.

The hardest part is deciding what to check first and avoiding random tuning.

A useful troubleshooting flow should quickly answer:

  • Is the problem happening right now?
  • Is this blocking?
  • Is this CPU pressure?
  • Is the workload waiting on storage, locks, memory, workers, or something else?
  • Is one query responsible, or is the whole workload under pressure?
  • Did a plan change?
  • Did traffic or concurrency increase?
  • Is the issue transient or historical?

Quick answer

Use this sequence:

1. Confirm the symptom and time window
2. Check active requests
3. Check blocking
4. Check current waits
5. Check CPU-heavy / read-heavy work
6. Check Query Store for regressions
7. Check open transactions if blocking/log growth exists
8. Check indexes only after workload evidence
9. Check infrastructure if SQL evidence points outside the engine
10. Re-test after one controlled change

The first SQL query should usually be a live request snapshot:

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    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.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;

From there, follow the symptom instead of jumping directly to indexes or configuration changes.

Code / commands

1. Confirm what is running right now

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,
    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;

Use this when the issue is active.

If nothing abnormal appears, the incident may already have passed. Move to Query Store or monitoring.


2. Check blocking immediately

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,
    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;

If this returns many rows, stop broad tuning and find the head blocker first.

Blocking can make a healthy query look slow.


3. Check current waits

SELECT
    wt.session_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
    wt.wait_duration_ms DESC;

This helps answer what sessions are waiting on right now.

Use the wait category to decide the next branch.


4. Check top cumulative waits

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

Use cumulative waits for pattern/context, not as proof of the current incident.

Always understand the observation window.


5. Check active CPU-heavy requests

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    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,
    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.cpu_time DESC;

High CPU + low waiting often points toward expensive active work.

High elapsed time + low CPU often points toward waiting instead.


6. Check active read-heavy requests

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    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,
    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 reads can point toward:

  • scans
  • poor selectivity
  • weak access paths
  • large analytical queries
  • parameter-sensitive behavior

Do not jump straight to “add an index.”


7. Check Query Store for historical regression

If the issue is intermittent or already passed:

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    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_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,
    qt.query_sql_text,
    p.plan_id
ORDER BY
    weighted_avg_duration_ms DESC;

Use this to find plan changes and workload regressions that live DMVs can no longer show.


8. Check open transactions when blocking or log growth exists

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Sleeping sessions with old transactions deserve special attention.


9. Check SQL Server start time before interpreting cumulative DMVs

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

This matters for:

  • wait stats
  • index usage
  • procedure stats
  • other cache/DMV-based evidence

A recent restart can make “unused” or “low activity” conclusions meaningless.


10. Check index usage only after identifying the workload

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.is_ms_shipped = 0
    AND i.index_id > 0
ORDER BY
    user_seeks + user_scans + user_lookups DESC;

Use this only after a specific query/table has been identified.

Index tuning without workload evidence is guesswork.


11. Check missing-index recommendations carefully

SELECT TOP (20)
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    migs.user_seeks
      * migs.avg_total_user_cost
      * (migs.avg_user_impact / 100.0) DESC;

Treat this as a candidate list.

Do not auto-create indexes from the DMV.

What the result means

The troubleshooting flow is more important than any single metric.

Signal Likely direction
blocking_session_id > 0 Locking/blocking investigation
LCK_M_* Lock waits
High cpu_time CPU-heavy query or workload
High logical reads Read-heavy query/access path
High signal waits CPU scheduling pressure
PAGEIOLATCH_* Data-page I/O wait path
PAGELATCH_* In-memory latch contention
WRITELOG Log flush pressure
RESOURCE_SEMAPHORE Memory-grant pressure
THREADPOOL Worker exhaustion/concurrency
Query Store plan change Potential regression
Sleeping session + open transaction Transaction management problem
No live evidence Historical/monitoring path

What to look for

Blocking explains the slowdown

If many sessions wait behind one blocker, prioritize the head blocker.

Do not waste time tuning the victims first.

CPU is high and active requests consume CPU

Investigate:

  • execution plans
  • high-frequency workload
  • scans/joins
  • parameter behavior
  • concurrency

Elapsed time is high but CPU is low

The query is probably waiting.

Look at:

  • current waits
  • blocking
  • I/O
  • memory waits
  • external result consumption

Query Store shows a plan change at the same time performance degraded

Strong regression evidence.

Validate:

  • execution count
  • parameter sensitivity
  • schema/index/statistics changes
  • workload comparability

Server appears healthy but one application is slow

Use:

  • login
  • host
  • program name
  • database
  • session/request correlation

to isolate the application workload.

Nothing looks wrong now

Do not conclude the issue was not real.

Use:

  • Query Store
  • persisted monitoring
  • Extended Events
  • application telemetry
  • infrastructure metrics

for historical evidence.

Production notes

Troubleshoot before changing configuration

Do not make server-level changes because one metric looks suspicious.

Avoid changing:

  • MAXDOP
  • cost threshold
  • memory settings
  • worker threads
  • tempdb layout
  • indexes

until the evidence points there.

Live DMVs are transient

Active-request and waiting-task data disappears when the workload finishes.

If the issue is intermittent, capture evidence or use persisted telemetry.

Query Store is often the bridge between live and historical troubleshooting

Use live DMVs for “what is happening now.”

Use Query Store for “what changed over time.”

Infrastructure still matters

SQL Server symptoms can originate outside the engine.

Examples:

  • storage latency
  • VM CPU pressure
  • memory constraints
  • network delay
  • client-side slowness
  • IIS/app-server saturation

Do not stop at the database if SQL evidence points outward.

Compare before and after one change

Avoid changing several things at once.

A controlled troubleshooting loop is:

Observe
→ Form hypothesis
→ Make one change
→ Measure
→ Keep or revert

Permissions vary by SQL Server version

For broad performance DMV access:

  • SQL Server 2019 and earlier commonly use VIEW SERVER STATE
  • SQL Server 2022+ commonly use VIEW SERVER PERFORMANCE STATE

Azure SQL platforms have different scope and permission rules.

Common mistakes

1. Starting with indexes

Indexes are important, but not every slowdown is an indexing problem.

Blocking, CPU pressure, I/O, memory grants, and plan regressions can all look like “slow SQL.”

2. Killing sessions too early

Killing a blocker can create rollback and application impact.

Understand:

  • transaction age
  • business operation
  • rollback cost

before acting.

3. Looking only at one metric

CPU without waits, waits without queries, or reads without plans can all mislead.

Use multiple signals.

4. Using cumulative waits as current evidence

Instance wait stats may reflect days or weeks of workload.

Use current waiting tasks and time-window deltas for incident analysis.

5. Treating a missing index recommendation as the answer

Missing-index DMVs are advisory.

Validate against:

  • existing indexes
  • query plan
  • write cost
  • workload importance

6. Ignoring the application and infrastructure layers

A SQL query can look slow because:

  • the client reads rows slowly
  • the API is overloaded
  • IIS/app server is saturated
  • storage is latent
  • the VM is CPU-starved

End-to-end context matters.

7. Making multiple changes at once

If performance improves, you will not know what fixed it.

If performance gets worse, rollback becomes harder.

Next step

Use this flow:

  • Blocking present → find the head blocker and inspect open transactions.
  • High CPU → identify active/high-historical CPU queries and inspect plans.
  • High reads → inspect predicates, scans, joins, and indexes.
  • Waits dominate → follow the wait category.
  • Plan changed → compare plans and consider temporary plan forcing if justified.
  • No live evidence → move to Query Store and persisted monitoring.
  • SQL looks healthy but app is slow → inspect app server, network, and client behavior.
  • One change fixes it → validate under normal workload before declaring resolution.

← All cheat sheets