An Introduction To sp_QuickieStore
Video Summary
In this video, I dive into SP_quickie_store, a handy stored procedure that provides insights into query store without much setup. I walk through the default output, explaining how it surfaces poorly performing queries and offers detailed execution metrics over the past week. I highlight key columns like query ID, plan ID, and various performance metrics such as CPU usage, reads, writes, and physical reads. While the procedure is straightforward to use out of the box, I also touch on its limitations, particularly in terms of memory grants, which aren’t fully visible within query store. Throughout the video, I share my frustrations with the query store GUI, finding it cumbersome and sometimes counterintuitive, and I promise more detailed explorations of SP_quickie_store’s capabilities in future videos.
Full Transcript
Erik Darling here with Darling Data, currently the only person with Darling Data. It’s a little lonely sometimes. Maybe I should talk to ChatGBT more, have some conversations, figure out what’s going on in the world around me, what’s in the news, headlines and all that, good stuff. Erik Darling here with me. So we finished talking with, well, we’ve talked about SP underscore human events about as much as I can talk about SP underscore human events without getting into my deep and profound frustrations with extended events. Erik Darling here with me. So we’re going to switch over to SP underscore quickie store so I can tell you about my deep frustrations with query store.
Erik Darling here with me. I don’t know. It’s not even query store. It’s the GUI. I think the GUI was designed by someone who hates people. Erik Darling here with me. That’s about it. Anyway, let’s talk about what you get from SP underscore quickie store right out of the box. Erik Darling here with me. So you don’t have to do much to get data back. We’ll talk about some things you can do to get data back a little bit differently in other videos.
But just off the bat, if you run SP underscore quickie store and you give it a database name that has query store enabled, you will get some very helpful results back. If you are using the database that has query store enabled, like if I just change the context to stack overflow 2013 and I run quickie store, it will default to the database that it’s it’ll check the database that it’s we’re currently using and see if query store is enabled. If not, you have to give it a database name. So let’s go on back to the master database and let’s hit F5 here and let’s talk a little bit about what you get back from the default results in SP quickie store.
So I’m actually going to go a little bit backwards here. I’m going to start with the bottom results because I want just to show you where you can get a little bit of information about what you got back. Mainly it’s this column right here. By default, SP quickie store goes back seven days to look for poorly performing queries. I used to have a default of 24 hours, but I found that the previous 24 hours would often not give me everything that I need.
There are some notes about support about how to get help using the procedure, how to debug and troubleshoot performance issues. And of course, version dates and all that good stuff, blah, blah, blah, blah, blah, happy times. Now let’s go talk about the top set of results, which by default will give you the top 10 queries that we found in query store over the past week ordered by average CPU.
Now, when I order by average CPU, I’m going to walk over here a little bit. Now, since I’m on SQL Server 2022, I have a lot of additional columns that you might not see in older versions of SQL Server. But when I go and look at what happens by average CPU, that’s this column right here, we find queries that used a lot of CPU, of course, on average.
These queries might not have very high execution counts. You might see a single execution. You might see, you know, just a low number of executions.
That’s sort of to be expected. There are different ways to filter the results that we’re going to talk about in another video. You can totally filter, but like set a lower bound for the number of executions for a query you care about.
But generally what I find is the higher you set that number, the lower this average CPU gets and the harder it is to find queries that you can make a meaningful difference tuning. Now, the count executions thing can be a little bit misleading. If you have queries that aren’t parameterized, if you have queries that I believe for recompile as well, they’ll still show up in query store.
But I think you’ll get different plan or different plan entry for them when they run. So you just might not have a completely accurate gauge of just how many executions a query has looking at this. So starting a little bit more close to home, you get the query ID.
So if you want to use any, if you want to use the query store GUI to track a query, or if you want to, you know, use it to force a plan or something, you have the query ID column and the plan ID column here. If your query is responsible for inserting multiple plans, so if one query ID has multiple plans attached to it, you’ll get all of the plan IDs in this column. This comes in handy, again, something we’ll cover in another video, because you can filter down to just the query IDs or just the plan IDs that you care about using different parameters in here.
This column will tell you if the query completed executing or if it failed for some reason. There are different outcomes for this here. If your query came from a stored procedure or if it came from an ad hoc source, like an application, any framework, something like that, then you will see either the object name that the query came from or ad hoc.
You also get back the query text as a handy clickable XML column. Alright, isn’t that lovely? Isn’t she pretty and pink? You’ll get the query plan back, alright, so you can get all that good information.
What’s the point of QueryStore if we’re not getting query plans? You get the current compatibility level that the query executed in, so you can figure out if you’re using an older or newer compatibility level. This is kind of good for tracking like the regressions if you change compatibility levels.
These columns are new to SQL Server 2022. If you have hints in QueryStore, if you have, if QueryStore is giving your, your query feedback, or if you have plan variants, meaning if your query is, is trying to be enhanced by the parameter sensitive plan optimization feature in SQL Server 2022, then this will tell you if there are any variants for the plan that you need to be concerned about.
If you’re forcing the plan, you’ll get that here. You’ll get the top weights for the query execution here. This is 2017 plus only, and only if you turn it on in QueryStore.
QueryStore by default will track weight stats in 2017 and up, but some folks turn it off if things get too noisy in there. Some other good columns in here to look at. First execution time, last execution time, so you know the window of time that this count executions comes from.
If your query is a frequently executing query, then we’ll track how many executions per second your query is responsible for. Again, stuff we’ll cover in other videos. I don’t, I don’t, I don’t like to make these too, too, too long.
Mainly because the nice camera that I have attached up there that makes me look so crystal clear and flawless. Overheats sometimes and turns off on its own. So I have about 30 minutes before that happens.
So now we have some metrics about the query execution. We have average duration, total duration, CPU. We go into reads, we go into writes, we go into physical reads, we go into memory.
Now, one aggravation that I have with query store, excuse me, is that the plan cache tells you how much memory the query asked for and how much it used. Query store only tells you how much it used. It does not tell you what the total memory grant for the query was.
This kind of sucks because one thing that I love trying to find is queries that asked for a lot of memory and didn’t use much of that memory. So we can start figuring out why these queries think they need as much memory as they do. But we don’t get that in query store.
There is a parameter that we’ll talk about in a later video for SP Quickie Store called expert mode, which will go to the plan cache and look for SQL handles of queries to get some in front, some plan cache information that is not available in query store. But again, we’ll talk about that down the line here. Some row count stuff, physical reads, tempdb usage, and context settings.
Context settings can be pretty useful because you might find that your applications do not use expected context settings. You may find that that inhibits the use of computed columns, filtered indexes, index views, things like that. So always good to have this here since I do most of my demo testing either with SSMS or with SQL query stress, maintained by the lovely and talented Eric EJ.
I get a pretty standard set of context settings for my queries. So that is the default out of the box what you get with SP Quickie Store. In other videos, we’ll talk about different things, other ways to use it, different ways to manipulate the output, the results, how to filter things, stuff like that.
So we’ll get to all that stuff. And you’ll love it. You’re going to love every minute of it because you love SQL Server.
You love query store. You love free stored procedures that people spend hundreds of hours writing in hopes that you’ll use them and love them. Right?
Oops. Everything except pay you to use them. Anyway, that’s SP Quickie Store. In a nutshell, we’re going to do some more videos and talk about other things that you can do with it. But this is good enough for now.
Thank you for watching. Like and subscribe if that’s your fetish. I mean, it’s my fetish when people like and subscribe to me. So you’d be doing us both a solid if you do the like and subscribe thing. And yeah, I’ll see you in the next video.
Thank you for watching. Have the best day. Thank you. you
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.
Memory grants out of query store? Goosebumps. Can’t wait.