Accidental DBA
Posts
SQL Server Wait Stats

SQL Server Wait Stats

What are you waiting for?

Kevin Hill
April 04, 2025

Everything in SQL Server is waiting for something…this is by design. And most people never think about it...

Until your application is waiting to complete its request, sell the product or provide “the report” to the CEO!

Most SQL Server performance issues come down to one thing: waiting. Every time a query slows down, SQL Server records what it was waiting on. These wait stats are one of the best diagnostic tools for DBAs, sysadmins, and developers - but only if you know how to use them.

What Are Wait Stats?

SQL Server tracks the time spent waiting on resources like CPU, memory, disk, or locks. Think of them as traffic reports - some delays are normal, while others indicate serious bottlenecks. If your database is slow, wait stats tell you where to start looking.

How to Read Wait Stats

A simple query can reveal where your server is spending the most time:

SELECT 
    wait_type, 
    waiting_tasks_count, 
    wait_time_ms, 
    signal_wait_time_ms 
FROM 
    sys.dm_os_wait_stats 
ORDER BY 
    wait_time_ms DESC;

A much more comprehensive script can be found here

Key columns:

· wait_type – The specific type of wait that might be affecting performance

· waiting_tasks_count – Number of times this wait has occurred

· wait_time_ms – Total time spent waiting (cumulative since last clear)

Common Wait Types and What They Mean:

Wait Type	What’s Happening?	Possible Fixes (test first)
CXPACKET	Query parallelism overhead	Cost Threshold/MAXDOP settings, Tune Indexes and Queries
PAGEIOLATCH_SH	Slow disk I/O when reading pages	Improve disk performance, add RAM, Tune indexes and queries to reduce pages needed
LCK_M_XX	Blocking and locking issues	Optimize queries, tune indexes
SOS_SCHEDULER_YIELD	CPU contention	Tune queries, add CPU resources

Many other waits exist, but these are some of the most common. Microsoft provides a full reference here.

SQL Skills has the best list of wait types and things to know I’ve ever seen. Go there and read before changing anything. MANY recorded waits are benign and not worth looking into.

Finding and Fixing Performance Issues: A guide for you to try in your development environment

Run the wait stats query to capture the current state.
Identify the top wait type and correlate it with system metrics. Research it
Apply a tuning change (indexing, parallelism adjustments, memory tuning).
Re-run the query to compare results.

This process provides a clear before-and-after snapshot of how performance improves.

The Bottom Line

Wait stats don’t tell you exactly what’s broken, but they show where to investigate. The key is to correlate them with query execution plans, server configuration, and system resource usage. Tracking these waits over time can also help spot performance trends before they become critical issues.

SQL Server is always waiting for something. The question is - what are you going to do about it? What are you waiting for?!

Get the first month of Pocket DBA® FREE from Dallas DBAs!

My Recent LinkedIn Posts:

I don’t wanna brag… - wherein I save myself $6 per month

Today is my half-birthday - well, Tuesday was. April Fools day is 6 months from my palindrome birthday 10-01

Everything in SQL Server is Waiting for Something

Interesting Stuff I Read This Week:

Stocks plunge most since 2020 | LinkedIn - Tariffs go into effect

China retaliates

Nothing else grabbed my attention in a very busy week

SQL tidBITs:

SQL server has 3 backup types most people know about: Full, Differential, Log. There are 4 others! File/Filegroup, Differential File, Partial and Differential Partial.

Reply

or to participate.