Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. Confirm what is running right now
- 2. Check blocking immediately
- 3. Check current waits
- 4. Check top cumulative waits
- 5. Check active CPU-heavy requests
- 6. Check active read-heavy requests
- 7. Check Query Store for historical regression
- 8. Check open transactions when blocking or log growth exists
- 9. Check SQL Server start time before interpreting cumulative DMVs
- 10. Check index usage only after identifying the workload
- 11. Check missing-index recommendations carefully
- What the result means
- What to look for
- Blocking explains the slowdown
- CPU is high and active requests consume CPU
- Elapsed time is high but CPU is low
- Query Store shows a plan change at the same time performance degraded
- Server appears healthy but one application is slow
- Nothing looks wrong now
- Production notes
- Troubleshoot before changing configuration
- Live DMVs are transient
- Query Store is often the bridge between live and historical troubleshooting
- Infrastructure still matters
- Compare before and after one change
- Permissions vary by SQL Server version
- Common mistakes
- 1. Starting with indexes
- 2. Killing sessions too early
- 3. Looking only at one metric
- 4. Using cumulative waits as current evidence
- 5. Treating a missing index recommendation as the answer
- 6. Ignoring the application and infrastructure layers
- 7. Making multiple changes at once
- Next step
- 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.
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.