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.
I’m new, i need lecture notes
Lecture notes for what?
Thanks for this video…
Hi Brent – I just wanted to congratulate you on your fantastic videos. I find your style very easy to follow and have been really impressed with the depth of your knowledge on SQL Server. Please bring out more SQL Server based training videos: )
Many thanks for making it interesting to learn!
Dino
Thanks, Dino, glad I could help! I’m working on bringing more video-based training to the site in 2012.
I’ve purchased SQL Server 2008 internals and troubleshooting-I’ll add a recommendation on Amazon. I have to say it’s hugely better written and much more applicable to problems SQL practitioners might face as opposed to most of the other books I’ve seen/bought.
Keep-up the good work!