Wait Stats: Lose Wait Fast

Perfmon and Profiler are a great way to get started with performance tuning, but before you start attacking code or hardware, make sure you’re focusing on the real bottleneck.  In this hour-long session, Buck Woody and I explain how to query SQL Server wait stats to determine bottlenecks the easy way, plus cover free tools like sp_WhoIsActive, SQLdiag, and SQLnexus.

The tools we discuss in the video are:

  • 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.
  • Glenn Berry’s diagnostic scripts – not actually mentioned in the video, but this is my favorite wait stats tool these days.

And check out my other free SQL Server training videos.