The Top 10 SQL Server Wait Types, Worldwide

SQL Server uses wait stats to track what queries are waiting on. Rather than monitoring gazillions of Perfmon counters and DMV objects, the easiest way to make your queries go faster is to ask, “What is my server waiting on?” Then, focus your efforts on your server’s top wait type.

When you’re learning how to do performance tuning in general, you wanna have a rough idea of what the top wait types are. Wonder no more: thanks to the data we gathered from thousands of SQL Servers around the world over the course of years, here are the top waits overall, and how much of the time SQL Server spent waiting on them:

1. CXCONSUMER – parallelism – 25%
2. CXPACKET – parallelism – 23%
3. SOS_SCHEDULER_YIELD – cpu – 6%
4. LCK_M_X – locking – 5%
5. WRITELOG – transaction log writes to disk – 5%
6. PAGEIOLATCH – reading data pages from disk – 4%
7. BACKUPIO – what it says on the label – 3%
8. LATCH_EX – reading pages in memory – 3%
9. LCK_M_U – locking – 3%
10. ASYNC_NETWORK_IO – sending data to the client app – 3%

That top 10 list really helps me focus my training classes on the most vital bottlenecks that will make the biggest difference in your tuning work, and your career. I know you’ve only got a limited amount of time to learn stuff, so I wanna concentrate your training on the problems you’re most likely to hit in your daily work. Let’s get you started with a summary of what each of those wait types mean, and how to begin reducing them:

CX% waits – your queries are going parallel to do a lot of CPU work, to read a lot of data, or both. Check to make sure your server has good default settings for Cost Threshold for Parallelism and MAXDOP, and then go past these CX% waits, and look at your next top wait types. Work on reducing those, and the parallelism will fall away because the queries won’t need to do as much work overall.

SOS_SCHEDULER_YIELD – queries are burning CPU. Run sp_BlitzCache @SortOrder = ‘cpu’, and work through the warnings. Usually we focus our query tuning on reducing logical reads, but that’s not what’s important here – focus on the operators burning the most CPU time.

LCK% waits – queries are being blocked. Have enough indexes to make queries fast, but not so many indexes that inserts/updates/deletes are slowed down. Tune your transactions to be as short and sweet as possible. Consider Read Committed Snapshot Isolation or Snapshot Isolation.

WRITELOG – insert/update/delete queries are waiting to write to the transaction log file. Your first instinct will be to make the log file faster, but hold off – instead, start questioning which queries are doing the most writes. Run sp_BlitzCache @SortOrder = ‘writes’ and sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘writes’ and ask questions about what’s happening on the server, and why.

PAGEIOLATCH – reading uncached data pages from disk. Run sp_BlitzIndex @GetAllDatabases = 1, and look at missing index requests. Run sp_BlitzCache @SortOrder = ‘reads’ and tune those queries and their indexes.

BACKUPIO – if your databases are <1TB, and you’re still doing native backups, try striping them across 4-8 files to see if you get faster throughput. As your databases approach 1TB, though, it’s time to think about storage snapshots and/or differentials.

LATCH_EX – reading cached data pages from memory, and often seen in combination with CX% waits. Start by checking sp_BlitzCache @SortOrder = ‘reads’ and sp_BlitzCache @SortOrder = ‘executions’. Can we tune these queries to read less pages, or even better, can we cache the data in the application tier instead of asking SQL Server for it repeatedly?

ASYNC_NETWORK_IO – returning results to the application. Try sampling live activity a few times to see if you notice a pattern:

Check out the server name, application name, and query. Is there a particular server, app, or query that repeatedly shows up? Is it an underpowered server that can’t digest the results quickly enough, or an app that’s running row-by-row calculations as each row comes down from the database server, or a query that’s returning millions of rows when it should be using pagination?

To learn more…

The above stuff is obviously only short summaries. I could – and do! – spend an hour or more on each of them, showing you examples of what kinds of queries trigger these wait types, and show you the various ways you can fix each wait type.

Because CX% and PAGEIOLATCH% waits are so important, so common, and so related to each other, I teach you how to fix them in my Fundamentals of Server Tuning class.

The rest of the top 10, plus more poison waits that I see often in the field, are covered in my Mastering Server Tuning class. And guess what? They’re on sale right now during my Black Friday sale! Go get ’em.