Introducing sp_QuickieCache: 80/20 Plan Cache Analysis to Find Your Worst SQL Server Queries

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about, let’s see, did it happen right? It happened, right? I had to take, if you pay attention to the goings on of Erik Darling and his Darling Data life, there was a video recently about adding sort of 80-20 query analysis into SPQuickieStore, which was very, which continues to be a very, useful thing for me. Uh, it’s in the latest release of the, my, my SQL Server, uh, performance troubleshooting scripts, uh, doesn’t have a snappy name, no first responder kit, but, you know, someday, someday I’ll figure that out. Um, but, I was working with someone recently who, uh, was, was philosophically opposed to QueryStore. Much, you know, there are various reasons why, some, some, some, you know, uh, some right, some wrong, but, you know, I just refused to turn it on, and we needed a way to look at QueryStore. And I wanted to use a similar way, so, uh, I ported the find high impact section of code over to a new store procedure, and I also added a couple bells and whistles to it. So, uh, we will, we will look at that, and we will also, uh, actually, no, we’re, we’re only going to do that. That’s, that’s all we’re doing today. We don’t have, we can possibly cram another iota of interesting things into this.

Uh, down in the video description, you will find all sorts of helpful links for, uh, our lives, to make our lives intertwine wonderfully financially together. Uh, you can hire me for consulting, you can buy my training, and for as few as $4 a month, you can, you can, you can feed a starving consultant, um, uh, some tiny fraction of a New York cappuccino or espresso. Uh, for free, though, you can ask me office hours questions. Maybe you can ask me how to draw other things in SQL Server Management Studio, and we can try that. Uh, and of course, if, if your enjoyment and appreciation of me, uh, is not, is not measurable in money, uh, you can always like, subscribe, and of course, uh, tell a friend about, uh, whatever it is you find useful here.

Or, I don’t know. Maybe you just like Adidas t-shirts. Right? Could go either way. Uh, for free! Also, you can download my SQL Server performance monitoring tool. Um, no, no, no weird telemetry or email sign-up required. I don’t, I won’t spam you with crap. Uh, it’s just all the stuff that I care about monitoring, uh, performance-wise in a SQL Server, uh, collected and, and made into beautiful charts and graphs with all sorts of very user-friendly ways of, uh, looking at that data and getting root cause analysis of your SQL Server performance issues. Uh, there is, uh, also a built-in set of MCP tools that you can have your robot friends talk to about your performance data and only your performance data.

And it’s a magnificent thing in the world, right? Because you, you don’t have to let them loose into your, into your production SQL Server to, like, start running all sorts of crazy queries. They just touch the performance data that’s already been collected, and that’s much easier for them to understand, too, right? Because we, we retain all the important details. We don’t lose them, right? When various things go away and age out of SQL Server, we collect it all over time. It’s beautiful. It’s a wonderful thing, and it’s free.

I have a surprise pre-con. Um, I don’t know, I guess someone else’s airline tickets got canceled or something. I don’t know. Maybe they, sick, maybe their parakeet died. I don’t know. Uh, but I will be at Day of Data Jacksonville, Florida, May 1st and 2nd. Boy, howdy. Look at that. Um, and I will be doing my advanced T-SQL pre-con shenanigans there, so, uh, if you want to come see me in Florida, it’s been a long time since I’ve been to Florida.

Apparently, I’ll be there. Alright. Uh, other places in the world I’ll be. Golly and gosh, look at all these wonderful people who decided to let me, like, be in public, in front of people, and, uh, not stuff a sock in my mouth yet. Uh, I’ll be at Pass on Tour, Chicago, uh, May 7th and 8th. I will be at SQL Day, Poland, May 11th and 13th. Uh, I will be at Data Saturday, Croatia, June 12th and 13th. And then I will be at, in Pass Summit, Seattle, uh, November 9th and 11th. Ah, man, Pass Summit, Seattle. There we go. I am, I am not even drinking today yet.

That’s the funny part. Alright. Cool. With that out of the way, let’s look at this new store procedure. Uh, I think I have to go to Management Studio. Yeah, I remember what that looks like. Alright. Cool. So, uh, this is, this is it. SPQuickieCache. Pay no attention to the terrible red squiggly underlines.

Um, and this is what you get back. So, if, if you have been using, um, uh, SPQuickieStore and you’re on a relatively new version of it, you will find that there is a high-impact parameter, which gives you a similar set of stuff. Uh, the whole idea here is to find queries that hurt you across a variety of metrics and sort of score them and, uh, present them to you in an order in which you should tune them.

Right? Because that is a, a wise and wisdomful thing to do. So, uh, this is sort of what you get back. Um, let’s zoom in on the results here. Up at the top, we tell you how many plan cache entries and all this other stuff we captured. And, um, you know, it was good stuff, right? Like, good information up here. Uh, and then down in this section, this is where your 80-20 queries live. Um, I have taken it upon myself to do some neat things in here, like tie statements back to the procedure that they live in. Uh, I think if we scroll down a little bit further, there’s another one, but I might be wrong because I might’ve run some stuff between now and then. Yeah, it’s not in there, but, um, you know, like for like this line, you get like just create procedure. And there are some things that we don’t get back at the procedure level that we get back at the query level. So, uh, there’s all sorts of neat stuff in there, right?

Like, uh, let’s see up here, we have create procedure, any word, right? And this is like, misses the query hash, but down here we see like the queries from that store procedure, right? So that, that’s, that’s, that’s how it works. But then over to the right, we have some other things too. Uh, we have this impact score and we have, uh, the areas in which queries returned, uh, high signals, right? Uh, so, um, this one up here up at the very top spilled a lot, right? That was how many times it spilled. And if we look over here, uh, we’ll even see, uh, the total spills and the stuff like that. So, and, you know, max spills and all these other things. So we get some, some high level information back about like how much damage this thing is actually doing. So we can, we can make smart choices. Um, down here, uh, there is some information about the plan cash, right? So, um, like, like severe plan duplication and single use plan bloating, plan cash stability, right? And single use plans and all these other neat things at the database level. Um, I also added some of these database, well, I was working on this.

I added some of these database level checks as well to SP blitz cash. So if you are an SP blitz cash fan or aficionado or whatever, um, you’ll, uh, I think at some point when Brent does another release, you will see, uh, the database level breakdown of, um, of, uh, duplicate plans and single use plans. So if there’s like a single database on your server, that’s responsible for all this, it used to just be at the server level, right? And just be like, there’s a lot of plans for this query, but now it’s like for the database, like, look how bad this is. Now, just like all my other store procedures, uh, there is a help parameter where you can see how everything is set up and gets used and that it’s MIT licensed and that, you know, we, um, you know, give this stuff away for free. I give this stuff away for free. Uh, but the important part here is that it lists out all the parameters. And if you look in here, there are a couple of neat little doodads, like find single use plans and find duplicate plans because a lot of the times, Oh, we don’t need you. Uh, you’re, you’re a quickie store. You, how’d you sneak in there? Um, so a lot of the times when, you know, you see alerts like, Oh, there are a lot of single use plans or, Oh, there are a lot of duplicate plans. Uh, you’re like, well, where are they? How do I, you’re going to make me go find those? How do I, how do I do it? I’m hope lost and hope hopeless and helpless. How can I possibly be a more self-sufficient person in the world? Don’t worry. You can be, you don’t have to be, you can be codependent on me. So if we run this query, uh, or rather run SP quickie cash with find single use plans, I will return to you single use execution plans, and I will give you a command to get rid of them. Right? So that’s, that’s cool there. And then there’s also find duplicate plans, and this will give you the top 10, uh, most duplicated plans in your cache. Right now, it appears this TPC database is really, uh, just a nightmare mess of, of things, right? Look at this 1600 plans and 1600 executions, right? And the, the story doesn’t look good there. Right. But, um, you know, this will help you maybe find queries that need parameterizing, um, or maybe it will help you turn on force parameterization for an entire database to avoid the problem. But either way, uh, you get some useful information back there. Uh, this store procedure, just like all my other store procedures are available in my GitHub repo. That’s at code.erikdarling.com. Uh, again, type that out for you. So you can be even more codependent on me. You can be codependent on my code code.erikdarling.com. You can get this here. Uh, you can start using it. You don’t have to turn query store on for it. Um, uh, so that’s, that’s cool too. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in tomorrow’s video where we will do, I don’t know, something stuff. We’ll figure it out.

I do all this stuff at the last minute anyway. All right. Thank you for watching.