First off, I understand if you read the headline and you have a knee-jerk reaction. I totally understand that there are a lot of people out there who hate AI, and believe me, I hate most uses of it. Lots of its advice can be misleading at best, and absolute garbage at worst. However, if…
Say we have a stored procedure that has two queries in it - the second query uses a recompile hint, and you might recognize it from my parameter sniffing session:
[crayon-6a6b6919a224b207328860/]
The first query will always get the same plan, but the second query will get different plans and return different numbers of rows depending on which reputation we pass in.
In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I've also talked about the dynamic management views (DMVs) that let you query what's in SQL Server's memory. Let's put those two things together and find out what queries have been running on the server lately.
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.
All the cool kids Know that when you're trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog's "Slow in the Application, Fast in SSMS", because it's an excellent…
Shhh, and pay close attention. In 76 seconds, I'll teach you something intriguing about the way SQL Server caches execution plans:
https://www.youtube.com/watch?v=KfdfNRrIsU0
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
The most popular way of using sp_BlitzCache® is to just run it - by default, it shows you the top 10 most CPU-intensive queries that have run on your server recently. Plus, it shows you warnings about each of the queries - if they're missing indexes, experiencing parameter sniffing issues, running long, running frequently, doing implicit conversion, you name it.
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…
sp_BlitzCache is a fantastic tool for finding the worst queries in your SQL Server. Did you know that it has a number of advanced options available to you? These options can help you dig into your worst queries, slice and dice the data, and get back exactly what you need, without wading through hundreds of results.…
