Wait Stats

Here’s my 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.
• 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.

Explanations of Each Wait Type

• SQL Server Wait Type Repository – maintained by Microsoftie Bob Ward.
• Waits and Queues Whitepaper – this is in the top list of introductory material, but it’s here too because it’s got a long list of wait types as well.
• SQLServerPedia’s Wait Types List – a wiki that lists all of the wait types, and anyone can edit it to add more up-to-date information.

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.