Getting More Details About Queries With sp_QuickieStore
Video Summary
In this video, I delve into the expert mode feature of SP Quickie Store, a tool that provides detailed insights into query performance in SQL Server. By enabling expert mode, you get access to numerous result sets that are typically omitted from the default output due to potential information overload. These results include memory grant feedback and compilation stats, which can be invaluable when troubleshooting complex queries or optimizing performance. I also discuss the new DMVs introduced in SQL Server 2022, such as query store hints and query variant view, offering a deeper understanding of how these features can aid in diagnosing and resolving issues related to query optimization and parameter sniffing.
Full Transcript
Erik Darling here with the wondrous, illustrious, eutiferous Darling data. Everything but cruciferous. We do not aid in digestion. We are not a fibrous vegetable in the least. We’re going to talk about today continuing in the same vein as some of the other videos that we’ve recorded recently or that I’ve recorded recently. There is no way. no we in Darling data. It’s just me alone doing Darling data things. We’re going to talk about how you can get more detail about the queries that come back in the default output for SP Quickie Store. So what we have is a parameter called expert mode. And what expert mode will do is return a whole bunch of result sets to you that it would not have made sense to put in the default output because it would have been information overload. And this is really only stuff that you want to dig into when you start dealing with other problems, deeper problems with queries. If I put this in the default output, it would mostly go unnoticed. You would have a scroll bar with a slider like this big and you would scroll for a country mile till you lose your hand in a green screen and it wouldn’t be enjoyable for anyone. I promise. It would not be enjoyable in the least. All right. So when we use this parameter and we say expert mode, please. I’m going to get back some results that you might not get back because some of these results only show up in SQL Server 2022 because SQL Server 2022 had a bunch of new DMVs diagnostic management user, whatever you want to call them added to the query store family of views that help you figure out other stuff. So we have the default output up here. All right. Down here we show or I show rather if there is any feedback happening via different various intelligent query processing features passed on to passed on to the optimizer from query store. So in this one that right now we only have some memory grant feedback, so SQL Server is adjusting the size of memory grants between runs, we can get some information about the feedback data here, which is kind of nice to have if you want to dig into exactly what the feedback is telling the optimizer to do. We see here if the feedback is good or bad, right? It could say feedback valid, it could say feedback invalid.
And then we have the created and then we have the created and then we have the created and last update times. Now there are a couple DMVs that are included in the results that right now I don’t have any data for. So there’s the query store hints DMV. So if you apply any hints to queries via query store, which is a new feature 2022, pretty cool. Actually, I think I love this feature because I can finally fix entity framework queries. And then there’s the query store. So this is a new feature. So this is a new feature笑. So this is a new feature and then there’s a new feature. So this is a new feature. So this is a new feature. So this is a new feature. And then there’s also the query store query variant view. And if you have queries that are getting parameter sensitive plan optimization feedback, those results would show up in here. Down a little bit lower are results that you will see in most any version of SQL Server, as long as it has Query Store in it.
And that is going to be stuff around compilation stats. So compilation stats is going to tell you about everything that happened during query compilation. How many times it compiled, how long the compilation took, memory bind and all this other crazy stuff.
You can kind of understand why this is not part of the default output, but can be kind of important stuff to look at if you’re dealing with, say, long compile times, other stuff like that. Now, this is one of those bum out things that I’ve talked about in other videos. Query Store does not store memory grant information the same way that the plan cache does.
Query Store only tells you how much memory a query actually consumed when it ran. It does not tell you what the full memory grant was. So there’s no way for you to say, oh, you asked for 30 gigs of memory, but you used 30 gigs of memory and you only used like 2 megs of memory.
So maybe we ought to fix something in there. There’s perhaps some poor cardinality estimation happening in there. Perhaps some parameter sniffing issue.
Who knows? Could be anything. So for this, unfortunately, there’s nothing in my plan cache. So I can’t tell you all these other juicy details because the plan cache is a horrible place. It’s volatile.
It clears frequently. I don’t know what happened to it. I didn’t change anything. My plan cache was gone. Real sad story. There’s some other stuff which I actually think is pretty cool is getting weight stats back for specific queries. So this section in here will tell you per plan ID what the top weights were.
So in here we have a lot of parallelism in the CPU. That’s probably going to be the story across most of these things. Now, one thing that I do want to point out, and I’m going to probably skip over this because this is just the total weights for all queries.
And then the next one down is your current query store settings. So this will tell you like whatever, you know, however you configure query store, all the configuration options will be in here. So one thing I want to point out is that query store weight stats, the stuff you see here, is aggregated.
Like there’s a weight category called parallelism. We don’t know if it was CX consumer, CX packet, CX sync port, CX whatever. We don’t know what it was in there.
We only have a parallelism grouping. So if we go to the help section, down at the bottom, there is a little decoder ring for which weights are included in each one of those categories. I may need to update this a little bit for 2022.
I think I checked in on that the last time I did an update on this. But now that I’m looking at it, I’m going to check it out again to make sure I did my work right. So in here is where you’ll see the types of weights that are included in each one of those.
So if you have a question or you’re unsure about what weights might be involved when you see them show up in the weight section, then this is the place to look because this will tell you which weights are included in all of the roll-ups. So that’s just a quick way to get more detail about the queries that you find in the top 10 in Query Store.
It can be very useful for troubleshooting deeper issues with a query, getting more information back about queries, especially if the plan cache is not being a useless sack of potatoes. So there you go.
Anyway, good video. Good video all around. Didn’t cough once. It’s been zero days since I didn’t cough in a video. And I guess, you know what? I’m going to record one more on Query Store.
I just have to decide what it’s going to be on. It’s going to be a surprise to everyone, myself included. If I could tell the future, I would tell you what it was going to be about. But I had to say something, didn’t I?
Anyway, like, subscribe, send me cough drops, something. Seasonal allergies. It’s a terrible time of year here in New York.
Anyway, that’s enough of me. I’m going to go figure out what to do next. And I’ll record that video and upload that video. And you’ll watch that video and you’ll like that video and you’ll subscribe to my channel. Because even if you mute me and you just want to like turn this into a weird OnlyFans thing, I don’t mind.
I don’t mind. That’s how much I love you. I don’t mind what you turn this experience into.
All right. Cool. Thanks for watching. Hope you learned something. Hope you enjoyed yourselves. I hope my allergies go away.
I hope for many things. I hope someone signs a contract for a million dollars. You know, just manifest things into our lives. All right.
See you next time.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.