Every busy SQL Server has waits, and most are not a crisis. Reading the top five wait types on your server gives you a starting map when the numbers are filtered and timed.
Know What a Wait Measures
A worker records a wait when it cannot continue immediately. The wait type describes a resource or coordination point, not necessarily the root cause. Cumulative counters in sys.dm_os_wait_stats grow since service start or reset. Ranking raw totals after a long uptime can highlight background waits that have no user impact.
I start with the application symptom and a measurement window. Did requests slow during the same period the wait category grew? A top-five list without time is a history of the instance, not a current diagnosis. Ask what changed during the complaint. The wait list helps narrow the next check, but it cannot answer that question alone.
Read a Baseline Snapshot
The DMV exposes wait_time_ms, signal_wait_time_ms, and waiting_tasks_count. Capture a snapshot with the time and server start time. The example filters several known background categories for readability. Every exclusion should be reviewed for your version and workload. A copied exclusion list can hide a relevant issue.
I keep the raw snapshot too. It allows later reclassification without losing data. Do not reset wait statistics casually in production just to make a dashboard look fresh. Two timed snapshots give a safer interval view.
SELECT SYSDATETIME() AS sample_time,
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
N'SLEEP_TASK', N'LAZYWRITER_SLEEP',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH'
)
ORDER BY wait_time_ms DESC;Compare an Interval
Subtract an earlier snapshot from a later one for each wait type. The difference tells you what accumulated during that window. Handle a restart or manual reset: a negative difference means the baseline is no longer comparable. Record exact start and end times. An interval that covers both a maintenance job and business traffic needs more context.
I prefer a short, representative window during the reported slowdown and another during normal traffic. Compare both. A high cumulative wait that did not grow during the incident is not the immediate issue. The top of the interval list tells you where workers spent time while the problem was visible.
Read the Top Five Wait Types in the Interval
Order the interval wait-time deltas and inspect the top five as a working shortlist. A top category can be caused by one heavy query, many small requests, or a system-wide condition. Review waiting task count and average per wait, but do not rely on averages alone. A few extreme waits can hide inside a calm-looking average.
The query below shows the current top five wait types after the sample filter. It is a snapshot of cumulative values, useful only when paired with a prior capture or server uptime. I include it as a quick first look, not a final report.
SELECT TOP (5)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
N'SLEEP_TASK', N'LAZYWRITER_SLEEP',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH'
)
ORDER BY wait_time_ms DESC;
Separate Resource and Signal Time
Wait time includes time waiting for a resource and time ready to run but waiting for a scheduler. Signal wait can point toward CPU scheduling pressure when it rises broadly, but it needs context from CPU use, runnable tasks, and workload. A high signal component in one category does not prove the server needs more processors.
I look at active requests and schedulers during the same window. If a query is blocked on a lock, the lock holder matters more than a CPU graph. If many workers are ready but not running, examine CPU pressure and parallel worker behavior. The wait name is the label on the line, not a full explanation of why the line formed.
Translate Common Categories Among the Top Five Wait Types
PAGEIOLATCH waits relate to reading pages from storage into memory. PAGELATCH waits protect in-memory pages and can expose tempdb allocation contention. LCK waits point to lock blocking. WRITELOG relates to log flush activity. CX-related waits describe parallel worker coordination. Each category has several possible causes and needs query-level evidence.
I avoid saying one wait equals one fix. A PAGEIOLATCH increase can come from a new scan, reduced cache, or storage trouble. A LCK increase needs the blocking chain. A WRITELOG increase calls for log I/O and transaction pattern review. The category chooses the next investigation, not the final change.
Find the Work Behind the Wait
Current requests show wait_type, wait_resource, blocking_session_id, SQL text, and database context. Query Store gives historical resource use for completed statements. Pair the top interval wait with the statements active then. A server-level counter cannot identify the query by itself.
I have seen a team replace storage after a read wait grew, while a new query had simply started scanning far more rows. The storage was innocent and expensive. Check plans and logical reads before buying hardware. Conversely, a healthy query can suffer on a degraded volume. Evidence from both layers decides.
Avoid Background-Noise Traps
Idle and background waits can dominate an unfiltered ranking. Filtering them makes the report readable, but do not hide a wait simply because it is familiar. Review the exclusion list after upgrades and new features. Keep system tasks and user tasks separate where your monitoring allows it. A changed background wait can still reveal a configuration problem.
I label the filter in every report. A chart that lists the top five wait types without showing exclusions invites false confidence. The next DBA should know what is absent and why. A short note beside the list saves an argument later about whether the server was truly waiting on nothing else.
Close Each of the Top Five Wait Types with a Hypothesis
For each top wait, write the time window, delta, affected workload, and next check. Example questions are whether a blocker held a transaction, a query began scanning, or log flush latency changed. Test the most plausible cause and compare another interval after the fix. Do not claim success because a different wait moved into fifth place.
What user-visible delay did the wait help explain? If you cannot connect the two, keep investigating. Wait statistics are a map of time spent by workers. They become useful when you walk from that map to actual requests and a measured change.
Related reading on this blog: Top 3 Wait Stats from Real-World and Wait Stats Collection Scripts : Updated March 2021.
A top wait type is not a diagnosis, it is a direction for the next evidence check.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
