Changing The Default Output Of sp_QuickieStore
Video Summary
In this video, I delve into the customizable output of SP Quickie Store, a powerful tool for SQL Server database professionals. I demonstrate how to leverage the `help` parameter to uncover detailed information about the stored procedure’s capabilities and parameters, making it easier to tailor your queries according to specific needs. Additionally, I explore ways to modify the default query results by adjusting the number of returned queries with the `top` parameter and changing the sort order using various metrics like executions, memory usage, and duration. By sharing these tips, I aim to help you more effectively analyze and optimize your SQL Server performance.
Full Transcript
Erik Darling here, all by myself. We’re going to talk a little bit about how you can change some of the default output of SP Quickie Store. Along the way, I’m sure that we’ll see lots of fabulous, fantastic things. So the first thing that I want to show you is how you, as a data professional, can learn more about or learn the full capabilities of SP Quickie Store. And most of it is going to be by using the help parameter. I know that a lot of us in the tech world have trouble asking for help, unless it’s Google. But I put this in here for a reason. It’s because we hold these truths to be self-documenting. Just like PowerShell code is self-documenting. And if you hit the help parameter, those P’s are really going to tell me how far off the audio sync is on this thing. You’ll get a little bit of information, a little greeting up here. Tells you a little bit about what Quickie Store is, what it can do. And then the second output is all of the parameters that you can put into SP the data type of the data type of parameters, a description, valid inputs, and what the default value for them all is. So if there’s stuff that you want to filter on specifically, you can do that. We’re going to use some of them today. We’re not going to use all of them today. Some of them will be in a different video. Specifically, I think the ones about looking for a procedure and specific plan IDs and stuff will be the next video. But this one here is just a little bit about how you can change the default output. So obviously, passing in a database name, pretty critical step since query store is on database by database. I recently added a get all databases parameter to a quickie store. So if you want to scavenge all of your query store databases, you can do that. So the first thing I’m going to show you is how you can change the number of queries that you can change the number of queries that you can change the number of queries that get sent back to you. And that is by using the top parameter. By default, top will just give you the top 10. I try to keep it to a reasonable number because often I find that if you start bringing back 20, 30, 40, 50 lines, the further down the results are, sort of the less valuable they get. It’s a little bit overwhelming. There are only so many store procedures you can tune at a time.
Anyway, but if you want to get more back and this can actually be helpful if you have sort of like longer stored procedures where you feel like there might be like if you get the top 10 back and you’re like, well, there are like 30 queries in the store procedure looking at the top 10 is good, but we kind of want to see all of them, you can get a more expansive result set for that. But I’ll show you about this store procedure thing in the next video. But we get the top 10 or sorry, we changed top to 20. We will now get 20 results back. So I’m going to go back here and you can see that I’ve really been pounding on kind of the same query over and over again. Another thing that you can change is the sort order. Now I’m going to go back to the help parameter real quick. Because I want to paste in and show you all of the different sort orders that you can actually use when you’re searching through a query store.
So you can use CPU, logical reads, physical reads, writes, duration, memory, tempDB, and executions. These will all sort by the average, not the total. Only because what I find in my day to day consulting is that if you sort by totals, you tend to get stuff that executes a lot, but doesn’t take a very long time to run. And usually I want to find the stuff that does the worst in the average execution. You might find some outliers where they had like one thing where it did like a billion reads or something or used a billion CPU ticks.
And then the rest of the time it’s pretty quick. But, you know, these are good things to sort of figure out. So these are all the possible sort orders you can use. Again, that’s all available. If you use the help parameter, you can see all the valid inputs for everything.
But this time we’re just going to use executions as an example. Remember, by default, it uses average CPU. And if we look by executions, this might actually prove my point a little bit where we have a lot of executions for some of these things. Well, not even like a lot for what we’re looking at, but the average duration and the average CPU time is pretty high.
What I find the executions parameter is good at showing you is stuff like scalar UDFs, because scalar UDFs don’t execute once per query unless they’re in line 2019, 2022, Freud, fun stuff. If they’re in line, then they don’t execute technically as often.
But if they are not in line or not in lineable, then scalar UDFs will execute once per row. You know, you put it in a select list with the returns like top 10,000, that UDF is going to have to execute 10,000 times. If you put it in like a where clause or something, and let’s say your table has a million rows in it, and all million rows come out, that UDF is going to have to run a million times once per row, produce a result, and then have the predicate applied to it.
So those can really rack up executions very quickly, and that’s usually what I use the executions sort order for. Probably the other sort order that I use the most day-to-day is memory, because I think I said in the last video that finding queries that use a lot of memory is often a good way to find queries that are ripe for tuning.
Usually you’ll find like a big sort or something in there, and you can, you know, index your data more appropriately to make the sort not require memory. You know, index order, match sort order, sometimes you can fix that.
So that’s another good thing to look at. Another thing that actually has been kind of cropping up a bit in consulting work is if you have a query that has a lot of like derived joins, subqueries, other things like that, sometimes you’ll find that like SQL Server has a cap for the memory grant, right?
Typically like a query can ask for, unless you have resource governor enabled or using the min or max grant percent hints, SQL Server has a hard cap of like 20, 25% of your max server memory setting for a single query. And so if your query has a lot of subqueries in it, whether it’s, you know, multiple CTE, multiple, you know, derived joins, subqueries, stuff like that, things where, you know, you could break up those parts of the query, you can often get the memory grant as a whole to be smaller because you have a bunch of small queries asking for memory grants rather than contributing to one giant memory grant in one big query.
I’ve always found myself quite against big monolithic queries, but this is another good reason to do that because if query operators in a big plan are unable to share memory from, you know, one operator to another, sometimes that happens if you don’t have any, you know, sort of stopping points between them like sorts, hashes, other things like that, then queries will ask for very, very large memory grants to do things where if you break them up, you can get the memory grant reduced quite a bit.
So those are the words of wisdom I have for you. Thanks for watching. Another thing that you can do to limit or to change the default results is to use the start and end date parameters. That’s right down here.
So let’s say that you have some other monitoring that showed a CPU spike or, you know, getting a bunch of errors and other stuff on like a weekend and you want to just look at a weekend, then you can use the start and end date parameters to set caps on which portion of data you want.
Like I said in the last video, by default, you get the last week of data, the last seven days. But if you want to change that to focus in on a particular day, particular event, you can absolutely do that too and just hope to, hope to whatever, whatever you have faith in that you, you, you query store captured what you want for that.
So if you, if you wanted to see what I was up to last weekend, it was not a whole hell of a lot. This is all just background system queries. I apparently, apparently I wasn’t home much.
I wasn’t doing much last weekend. I think, I think I was out and about quite a bit working on my tan, you know, my bar tan. Uh, and so I was not, uh, hammering SQL Server with, uh, all sorts of, um, you know, bad queries.
Anyway, uh, that’s sort of a few different ways that you can, uh, look at, uh, change the default, uh, output ordering, what we’re focusing on, uh, and the, uh, default span of time that we are analyzing when we’re looking at query store. Uh, that’s about it for this one.
Next video, we’re going to talk about filtering to specific procedures, hashes, plan IDs, query IDs, stuff like that. Um, and so I hope you’ll join me there. Uh, remember, like, subscribe, love me, please love me.
I mean, or just kind of, I mean, just like me enough to watch the video, I guess. That’s all I really need. View, views, I guess, right?
Big views, big view, big view energy. Anyway, uh, thanks for watching. I’m going to get working on the, the scripts for the next one, and I’ll see you over there. Goodbye.
Hopefully not forever.
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.