Formatting, Debugging, and Troubleshooting Performance Of sp_QuickieStore
Video Summary
In this video, I delve into some useful features of QuickieStore, a handy tool for extracting data from SQL Server’s Query Store DMVs and exporting it to formats like Excel. I highlight how QuickieStore can help you format output in a more readable way by adding commas to large numbers, making it easier to understand the scale of your query statistics. Additionally, I explain how to use parameters like `debug` and `troubleshoot performance`, which provide detailed information about parameter values, temporary tables used, and execution plans for each query run through QuickieStore. These features are invaluable for debugging issues or troubleshooting performance problems when working with Query Store data.
Full Transcript
Erik Darling back again, still with Darling Data, at least for the time being. Who knows? Maybe I’ll be with MongoDB in six months. See what happens. Just kidding. I’ve heard it’s not a real database, despite all their best efforts. Just kidding. My dear friend Joe Sack works at MongoDB, and I would never besmirch the product he works for, because I did enough of that when he worked at my Microsoft. He worked for it worked on SQL Server. So in this video, what I would like to talk about is some different ways you can use QuickieStore if you need to put the data into something like maybe Excel, or if you run into problems with SP, QuickieStore. So again, all of this stuff is detailed under the help parameter. If you scroll down a little bit, we’re going to be dealing with things sort of towards the end, around formatting output, debugging issues, and troubleshooting performance. So, what we get, if we run QueryStore, sorry, if we run SP QuickieStore, by default, normally, without any interventions whatsoever, you’re going to see something startling.
That’s just a real deal. If you want to see something like that. Now, if the people who create and work on SQL Server Management Studio cared about you in the least, they would put commas into numbers.
because when you put commas in numbers, you can very easily judge their scale when they get really large. And that can be pretty important if you’re the type of person who deals with big problems. So let’s scroll over to the right a little bit, and let me start showing you some numbers with commas in them.
Starting here with weight stats, we can see that there were 186,915 milliseconds of parallelism weights in this query very easily. If this were all smushed together, it might be a little bit harder. This will happen whenever a query breaks the thousand, or sorry, whenever a number breaks the thousand mark.
You can see all that in here. Where things are in the thousands, we have a comma, so we can see that very easily. Where things are under the thousand mark, we don’t need a comma, because there’s no such thing as 3,49 unless you’re dealing with weird currency issues. But we are not. We don’t talk about query cost because query cost is a meme, and query cost should be ignored to the fullest extent of the law.
So, scrolling over a little bit further, we have lovely, lovely commas in all of these columns, so that we can see exactly what kind of numbers we’re dealing with very easily. Right over here, this logical read, look at this, 1.1 million logical read, very easy to tell what that is. Now, I’m going to tell you something about milliseconds.
It’s going to bake your noodle. If you chop off the last three digits, that’s how many seconds it was. Pretty impressive, huh?
Average duration, 24 seconds. Total duration, 48 seconds. Because there were two executions. Pretty sweet. So, while we wait for the lovely, hardworking, intelligent people who keep SSMS afloat for us to put commas into numbers, SP Quickie Store is out there in the world doing it for you.
You’re welcome. All of you are welcome. Now, let’s say you run into a problem with SP Quickie Store. Let’s say you run into some issue executing it, and you get an error, and you want to figure out what’s going on.
A great way to do that is to use the debug parameter. So, if we use the debug parameter, up front, we’re going to get a bunch of stuff. We are going to get all of the parameter values that were passed in to the stored procedure.
All right, it’s going to show us what the starting value was for every parameter. It’s going to show us what the declared variables were set to inside of the stored procedure, which can be very helpful for figuring out if anything got set incorrectly along the way.
All right, lots of good stuff in here. Lots of helpful, useful things in here. And then if we go down a little bit further, we’re going to get the contents of all of the temporary tables that were used to filter, join, get data out of our Query Store DMV.
So, this distinct plans table is the one that drives most of what we pick up out of Query Store. I use a lot of temp tables in this store procedure because I found that just naturally querying the Query Store DMVs directly led to a lot of performance issues.
I can still run into performance issues querying them in this way, but I run into far fewer of them. And I’m going to tell you something here. There is almost nothing more embarrassing than talking a client into turning on Query Store because it’s nice, lightweight, almost no overhead.
You won’t even notice it. And then you open up Query Store, and like the first three queries you see in it, there’s Query Store querying itself to populate the Query Store GUI.
It’s real tough. It’s a real egg on face moment. So, if we scroll down, we’re going to see all of the temp tables that got used.
Maintenance plans is what I use to screen out, you know, non-query activity that can be harsh on a server. You know, create table, alter table, not really create table, but like alter table, index maintenance, stats maintenance, stuff like that. I use that to filter out stuff here.
I use this to figure out what the Query Store options are for a database. This is sort of the raw query store data or some of the raw data that I output and format in different ways and other result sets. You know, just sort of like how each temp table was populated along the way.
So, there’s lots of useful stuff in here to figure out. Like, wait, well, this should have been in there. Why wasn’t it in there? You can kind of track down and figure out why things didn’t end up where they should have or why you’re seeing what you’re seeing in the results.
That’s the first part of it. The second part of it, over in the Messages tab, prints out every single query that gets run by Dynamics SQL. It’ll print it out here.
Before each one of these queries is a number. This number signifies the number of characters in the query. So, if you see something that is, you know, cut off or you see something that, you know, may have been some dynamic SQL that did not concatenate cleanly.
Perhaps there was some implicit conversion that happened that, like, caused the string to truncate. This is a good way to troubleshoot some of that stuff. There isn’t a lot of terribly long dynamic SQL in SP Quickie Store.
But, you know, there was enough for me to care about exactly what was going on in here. So, like, this is, like, 3,000, 2,800 characters. And this is the query that got executed in there.
And this is all the stuff that it did. And this can also be very useful because if you hit an error, it will tell you which query through the error. And then you can rerun that query to see if you can reproduce it, see if you can figure out exactly what’s going on.
That’s what I do with a lot of this stuff when I’m working with, when I’m trying to debug things. A lot of this was in here just during development just to make things easy. But I kept the debug thing in because, you know, if you run into anything while you’re using it, you know, I want to make it easy to troubleshoot for you.
So if you go ahead and open up an issue for me in GitHub, you can tell me exactly where the problem is, what you ran into. And we can, I can try to help you fix all of that stuff. Or I can help me fix all of the stuff that I need to fix in my query.
So good stuff there. Some of these, because of the way I print things out, like some of the strings are quite long and they don’t print in one thing. Print is a little more forgiving than raise error.
Raise error has a bigger cutoff. So you might see some points where the query text is not exactly the way it should be. That’s not me being bad at my job. That’s just me not having any control over how print sticks things into the messages tab.
So, excuse me. That’s a good way to troubleshoot if you run into any issues with SP Quickie Store. If you start running into performance issues, you can use the troubleshoot performance parameter.
And this will do some interesting stuff. So if we say troubleshoot performance, the first set of output is going to be every query that runs, that hits the query store DMVs, and the actual execution plan for that query.
So that’s all this section in here. I’m going to talk about what’s in there in a second. But you can see there’s a whole bunch of queries in here. This thing finished in zero seconds because, you know, I can use temp tables and the GUI can’t, I guess.
But anyway, what comes out of here is kind of cool. So there’s a pair for just about every line here.
We’ll get a query plan back that’ll show us exactly what the query was, what the execution plan was. I mean, this all finished in zero everything seconds.
So we don’t need to worry about this one. So we have that, which is cool. But then we’ll also have, also have for every query that, that ran and produced a plan, this information, which is very, very useful.
We have how long it took. We have what the current activity is. So we can control an F for this in the script. And then we have the query that executed so that we can look at it and say, hmm, maybe, maybe, maybe I could have done better here.
I don’t know. Maybe I could, maybe I couldn’t. I just don’t know. It’s impossible to tell. Pretty sweet. Huh? All right. So, and you’ll get that for every query that executes.
And, you know, you’ll see, again, the query information about how long it ran for, how long the dynamic SQL was. So you’ll see some of this, some of this information is a little repetitive, but it is all very useful when trying to figure out if there’s a performance issue with one of these queries that is fixable.
There’s a lot, a lot, a lot, a lot, a lot of times querying these DMVs, especially on very busy, very active servers, where, or servers with a lot of query store data in them, where this thing can slow down a little bit because there are just some things that do not perform well on those types of servers.
Specifically hitting some of these, some of these table valued functions that are, that sort of are masked by the dynamic management views.
A lot of the times trying to get stuff out of there, like you can’t just filter it directly. Like you can’t push predicates to some of these query table valued functions directly. You end up with like, you know, scanning a whole lot of rows and then filtering stuff out later.
That’s what this kind of combo is showing you. And does it kind of give you an idea of the kind of stuff that gets filtered in here? Sometimes it’s not even a predicate that I pass in. Sometimes it’s just something like this, where like, I didn’t, I didn’t ask for that.
I didn’t, I didn’t ask for this. This is just part of the built-in view definitions or the table valued function definition. So all good stuff to be aware of, all good stuff to try and help you if you run into performance issues while using SP Quickie Store to query the query store DMVs.
If you find anything in here that you think, you know, you can, you can show me and it’s something that is fixable by me. I’d love to look, but a lot of the times it is background stuff that I just can’t do much about.
Anyway, I think that’s probably good for this one. Again, formatting output. If you, if you want to, you know what?
I don’t think I ever talked about what happens if you put the format to zero, it takes the commas out. So if you need to like paste this into Excel or something, you can turn format output off and get the numbers without commas.
So yeah, there was that. I got so excited about debugging. I forgot to tell you that. So anyway, that’s my video. I do, I do hope you enjoyed yourselves.
I do hope that you learned something. I do hope that you will like and subscribe to my cough-free channel. Not, not throat-clearing-free channel because there’s still pollen in the air and my face feels like a brick, but that’s okay.
Do it all for you anyway. Thank you for watching. Take care of yourselves or else, or else you won’t be around to watch more videos from me.
What would be the point of life if you were dead? Thank 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.