Here’s our favorite links for performance tuning SQL Server with wait stats:
Introductions to Wait Stats
- The Ozar Family Tradition of Performance Monitoring – When my grandfather needed to tune his SQL Servers, he used Perfmon, and he passed his knowledge on down through my father to me. Today, though, we’ve got a better way – wait stats – and I explain how they’re different.
- What is SQL Server Waiting On Now? - a query from our First Responder Kit that checks SQL Server’s cumulative waits since the last restart, plus waits over the next 30 seconds.
- Waits and Queues Whitepaper – by the Microsoft SQL Server Customer Advisory Team, aka SQLCAT.
- Our Book – Professional SQL Server Internals and Troubleshooting – in chapter 3, we discuss how the SQL Server engine tracks what it’s waiting for with wait statistics.
Tools to Analyze Waits and Queues
- sp_WhoIsActive from Adam Machanic – this awesome free replacement for sp_who shows you what’s REALLY happening inside your database server including the queries, wait statistics, and locking/blocking issues. Call it with @get_plans = 1 to see execution plans, too!
- SQLNexus – when you want to analyze performance over time, use this free tool from Microsoft to generate pretty graphs of your SQL Server’s activity. Not for the faint of heart, but we discuss it in detail in chapter 13 of our book, Professional SQL Server 2008 Internals and Troubleshooting.
What is the CXPACKET Wait Type, and How Do You Reduce It? – Brent explains the single most popular – and most cryptic – wait type.