I got 128 GB of RAM just to develop sp_BlitzCache with! Also, thanks to @RichBenner @BrentOzar and @douglane for code, and @LitKnd and @HenrikStaunPoulsen for great ideas!
Grab the newly updated First Responder Kit, and here’s what’s in it:
sp_Blitz Improvements
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
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
First, thanks to 5 community volunteers for contributing code this month. In addition to lots of bug fixes, small stuff, and a new Github issue template (thanks, Konstantin Taranov) here's the big improvements: sp_Blitz Changes @OutputServerName writes output to remote server (Haris Khan) - for years, these stored procs have had an @OutputServerName parameter just waiting to…
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…
Be honest, here You don't care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions... All you're here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma'am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…
So you'd like to fix a bug or contribute code to the First Responder Kit, but you're new to Github. Let's get started.
1. Open a new issue on Github.
Go to the Github issues list for this repo, and start a new issue. Here's the example that I'm going to work on today:
In the issue title, put the name of the script, and some basic details so someone can see at a glance what's going on.
If you're using SQL Server 2016's awesome new feature, Query Store, there's a new bug with automatic cleanup. Books Online explains: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If…
We've released the first fully open source version of our SQL Server First Responder Kit: sp_Blitz, sp_BlitzCache, sp_BlitzIndex, sp_BlitzRS, sp_BlitzTrace, and the newest member: the newly renamed sp_BlitzFirst.
I wanted to rename sp_BlitzFirst because as an open source project, it's going to have more than just my own answers as to why your SQL Server is slow. So I asked for your naming suggestions, and we got over 300! Here's my favorites in no particular order:
You have a monitoring tool, but you've set up an email rule to dump all the alerts into a folder.
You're not particularly proud of that, but it is what it is. You're just tired of the spam.
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.…
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.
News broke recently of a dangerous data loss bug in SQL Server 2012 and 2014, and Aaron Bertrand explained which patch levels are affected. It's kinda tricky, and I'm afraid most people aren't even going to know about the bug - let alone whether or not they're on a bad version. I added build number checking into…
Finding the worst queries in SQL Server is easy, right? You just query one of the dm_exec_* DMVs, bring in the SQL text and the execution plan, and then you spend hours looking at execution plans trying to figure out what’s wrong in the query, much less how to fix it. All that’s changing - we’re introducing sp_BlitzCache®.…
sp_BlitzIndex® just can't keep a secret. That's a feature, not a bug. I wrote sp_BlitzIndex® specifically so that it would rat out some things that SQL Server is doing behind the scenes to secretly help you out-- because it's really better if you know about it. What are secret columns? SQL Server needs a unique…
