Everybody tells you to capture baselines - but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…
Your client or boss just said, "Find out why this SQL Server is slow." You've already done a free SQL Server health check, and now you wanna investigate deeper.
Step 1: Download & run sp_BlitzFirst.
Start with wait stats, which tell you what the SQL Server has been waiting on since the service was last started, or since someone cleared wait stats with DBCC SQLPERF, whichever is more recent.
Compiles Aren't The Same As Recompiles
If you're seeing high RECOMPILES, this post isn't for you. We'll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles.
Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason that a new query plan had to be compiled, despite the presence of what was once a perfectly good query plan.
There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use 'em:
How to Use sp_Blitz for a Health Check
https://youtu.be/5QAL9itupMA
How to Use sp_BlitzCache to Find Slow Queries
https://youtu.be/EkLuXURMwso
How to Use sp_BlitzIndex to Check Your Indexes
https://youtu.be/8Wo5M7kYO20
Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…
I love free tools. I also love analyzing SQL Server's wait statistics. But I'm not a fan of Activity Monitor, a free tool in SQL Server Management studio, which helps you look at wait stats.
Activity Monitor just doesn't give you the whole truth.
