Recent Improvements to sp_QuickieStore
Video Summary
In this video, I wanted to share an exciting improvement I made to SP QuickieStore, a tool designed for analyzing SQL Server Query Store data. While working on my GitHub repo and experimenting with Cloud Code, which was mostly successful but occasionally hit some snags, I decided to focus on enhancing the utility of SP QuickieStore. Specifically, I introduced a feature that aggregates metrics by query hash, providing a more comprehensive view of how often queries are executed and what their overall impact is. This change is particularly beneficial for those dealing with ad hoc queries that generate different execution plans but share the same query hash, offering insights beyond just the top 10 queries displayed in Query Store. If you’re interested in supporting my work or learning more about SQL Server performance tuning, you can find all relevant links and information in the video description below.
Full Transcript
Erik Darling here with Darling Data, and this is going to be a sort of short video. So I’ve been doing a lot of work on my GitHub repo yesterday. Yesterday, these like past two weeks, I’ve slept none whatsoever.
And like part of what I’ve been doing is like general repo improvements like readmes and code refactoring. QuickieStore was the beneficiary of a very large code refactoring to get rid of a lot of stuff. And as part of it, I wanted to experiment with Cloud Code, which was like 70% good.
I tried Cursor before that because everyone was talking about Cursor, but Cursor just completely like broke down on me. But I’m going to do a video about like the improvements that I made using that. It’s not going to be like a live video of me talking through that stuff because like honestly, it’s really boring like watching me type stuff into a command prompt and then watching like the thing spin and charge me 50 bucks and spin and charge me 50 bucks to do stuff.
But like I think the results of it for some things is pretty cool. The results for other stuff was way more mixed. So I’m going to talk through that in a different video.
But for now we’re going to talk about some improvements that I made to SP QuickieStore and the results. And this is actually a very, very useful change for people who have a lot of sort of ad hoc queries that hit SQL Server. So what I do in this one, and I do need to preface this and say that this is like this can be wrong because of a bug in SQL Server.
And it’s not just a bug with QueryStore. It’s a bug that goes back to plan guides. Paul White recently blogged about it where when you like create a plan guide and or like you force a plan in QueryStore, something in the SQL Server code swaps query hash and query plan hash.
So this is subject to like weird bugs that are not my fault. So if you see weird results in here, it’s not me. It’s Microsoft.
Blame them. Have them fix it. Call them up and yell at them. Get the pitchforks out. Whatever. But this is very useful for people who have lots of sort of ad hoc queries that hit SQL Server because what it does is it groups it like aggregates metrics for each query that would be in your results anyway. And like adds a column that shows you like like like like the totals by query hash, not just for like the query ID and plan ID combination that you’re looking at.
So that’s very, very useful for some people where you’ll see like way different numbers than you would see just in the regular results. Be like what this query ran once who cares. Like no, you can find find out this query ran like 3000 times and it got the same query hash and just got different query plans.
So neat stuff there. But before we get into that, let’s talk about this channel and you and me and birds and bees and butterflies and what’s that poem from Boogie Nights? Something about the slide or something.
It’s a slide, I think. Water slide. Anyway, if you like this channel and you like my content and you like my SQL Server stuff, you can sign up for a number ship for as little as few as $4 a month. That’s like a shot of New York espresso.
Link to do that is down in the video description. And I just want to preface most of this by saying almost everything that you are like is a actionable link is going to be in the video description. If you don’t have $4 per month to give to a handsome, charismatic fellow like me, you can like, you can comment, you can subscribe.
And you can ask me questions for my Office Hours episodes at that link, which is again down yonder. If you need help with SQL Server, if you find yourself struggling with performance or other issues, I’m available as a consultant to do all this stuff. And as always, my rates, they are reasonable.
If you would like some very reasonably priced training, you can get all of mine for about 150 US dollars. That’s with that 75% off code. The link again down in the video description for you to get all that fun stuff with.
SQL Saturday, New York City, 2025, May the 10th in Times Square at the Microsoft offices. Come, I don’t know, write something in the bathroom stall, I guess. I don’t know what you people do in bathrooms.
But with that out of the way, let’s talk about SP Quickie Store. There’s not a ton to show in here. Just really this one thing that I want to point out. But it’s a big enough improvement for me to want to talk about it like a lot because it’s something that a lot of people are going to find useful.
Like with all of my store procedures, there is a help parameter. And that help parameter will give you all sorts of information about what the store procedure does, document things within the store procedure. Like these are weight stat categories for query store.
But down here a bit, this thing will let me see. If this thing will scroll in the right pane and not just be a pane, you will find this include query hash totals thing somewhere. Where are you hiding?
There we go. There we go. Include query hash totals. So one fun thing about SSMS is that now copies the column header with everything. So that’s nice. But I just want to show you real quick kind of what this does in the code.
And we just need to get past a few things in here. This should be it. So what this does is when you say I want to include query hash totals, this thing will look at all your query store data for the queries that you get in your table of queries, like the top 10 queries that you would have gotten in here anyway.
My initial implementation of this was okay, but left something to be desired. So at first I was just doing like the query hash total for what was like in the results. And like that’s okay as long as there’s like duplicate queries, duplicate query hashes in the results.
This goes way back and finds everything. Aggregates like executions, duration, CPU time, reads, writes, memory, rows, everything across like all of your query store data, but only for the plans that end up in there. Otherwise this thing would be hell on earth to run query stores incredibly slow and painful to query.
So that’s what this thing does. And then in the end results, you will see the include query hash totals columns. What that looks like for my query store thing right now at this very moment in time is this.
So one thing that I, one thing that I change in the results when you say include query hash totals is to include the query hash column. I don’t, there’s at least a couple instances of there being duplicate query hash stuff in here. So we have that going for us, but if we come over and we look at the results a little bit further over where we start getting to like the places where you will start seeing these count executions by query hash type columns.
You’ll see that they are different from the columns in some other places. So this one found another query hash there. This one found two more.
This one found another one. This one found three more. This one found two more. So we like, you can already see where like something like aggregating things by query hash gives us way more information about like how many times a query did something. This also plays into like comparing like the total for some of these columns where the results are different.
Like this one here that goes from two to five, like the total duration in milliseconds for the two executions that we have in here is about 11 seconds. That’s about five and a half seconds a pop. But the total duration for this thing is 22 seconds, right?
So those extra three executions did way more. So I don’t do like the averages and stuff with also the total duration by query hash involved. I think just showing like the totals like with all the query hash stuff aggregated is enough.
But this column will repeat for all of the useful metrics in here. So it’ll do it for CPU. It’ll do it for reads, even though reads are a stupid meme and you shouldn’t pay attention to them.
It’ll do it for writes and physical reads, which are not a stupid meme. You should pay attention to those. So basically everything over here will end up with a total by query hash column that will complement other columns in here.
So that’s very, very useful for people who have lots of ad hoc queries that generate different query plans or have generated different query plans with the same query hash and whatever, because you get a much better idea of just how much these things are happening outside of just the results that you see in the top 10 by whatever metric you choose to order by in QueryStore. So, again, quick video just to give you a heads up on this new thing.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will turn on QueryStore and use sbquickiestore to navigate it. And again, you can get this code at my GitHub repo.
It’s code.erikdarling.com. That link will be in the video description as well. And again, that’s where I do all my support and stuff from. So if you have questions, you hit bugs, you have other problems, then file the stuff there. Don’t email me because I’ll just tell you to go to GitHub anyway.
So deal with it. Anyway, thank you for watching. Now, I’m learning to smile and smile and see you. I hope that that houffLife is happening. I hope that 노 amplifier해� dessus time, cause you might get things presente. I think, Maroon conditions on Internet says… Like a community number one or whatever.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.