sp_QuickieStore Improved Performance Troubleshooting

Posted on by Erik Darling

Things Are Getting Better


Thanks for reading!

Video Summary

In this video, I share my latest improvements to the SP_to_the_Underline_QuickieStore stored procedure for performance troubleshooting in SQL Server. After realizing that navigating through the initial version was cumbersome and required jumping around between tables and XML data, I decided to streamline the process. By formatting query information into XML and displaying it directly after fetching query plans, users can now easily identify slow queries without having to hunt through multiple lines of code or confusing table structures. This update makes troubleshooting much more efficient, allowing for quicker identification and resolution of performance issues in your databases.

Full Transcript

Erik Darling here with Erik Darling Data. And it would figure that as soon as I thought I had closed the book on the initial round of coding for SP to the underscore, what’s it called? Quickie store? Something like that? At least it rhymes so I can remember. I had these like, like, like epiphanies last night. He’s like, Joan of Arc visited me and hit me with a sword and said, do better. Because I was going through the video yesterday about, like, the performance troubleshooting bits.

I was like, ah, it’s a little clunky because you’ve got to look down at this table and see what was slow and then go up here and figure out where it was slow. And I was like, man, that sucks. Like, if I had to do that, I wouldn’t want to do it. I’d be demoralized. I wouldn’t want to deal with that. It’s stupid. It’s dumb.

Dumb. So what did I do to make things better? Well, I think, I think I made things better anyway. The good news is you no longer have to jump around on your screen in order to figure out what was slow and then hunt and peck through a million lines of XML to figure out what was slow.

And I’ll show you how I did that. Wonderful magic trick. So if you’re a troubleshooting performance and you use a troubleshoot performance parameter, you’re going to get some different, a different layout of your information back.

All right. So looking down here, we still create a temp table to hold data about what we did, right? We still have a temp table that holds the current table data, the start time, end time, and then the formatted runtime in milliseconds. So if we have something that runs for over a thousand milliseconds or one second, then we will put some commas in to make numbers a little bit more readable.

And then if we go in a little bit further, this is the change I made that I think makes things a lot better for everybody. If we go down here, what I’m doing instead of dumping stuff into a table and making you deal with it and jump around is I am formatting the information about the query in the runtime into XML. And I’m going to display that to you right after we get the query plans for whatever executed.

So you can see what I’m doing in here is I am hitting that. So what you’ll see in a minute, but it’s going to be after we update the troubleshoot performance table to get information. I’m going to pull data from there based on what the current table situation is. So we’ll get the runtime, the current table, the length of the SQL, and then the statement text of the SQL that just ran.

If you go down a little bit further, just to show you where this happens, and this will happen for every single block too. So we’re going to execute that SQL to generate that XML and just select it. Right. So that’s what I did.

And I think we’re working out pretty well so far. But if we run SP to the underscore quickie store and we see what comes back, it’s a lot cleaner to deal with the performance, any potential performance issues. Granted, there aren’t any here because, again, I keep my query store tight and light.

But in your query store implementations where you have hundreds of thousands of rows, maybe this might be a lot might look a lot different performance wise. So it’s still not perfect because I still can’t separate the insert query from the dynamic SQL select in some cases. So in some instances, you’re going to see the XML for the query that ran.

Right. It’s going to be this. And then right below that, you’re going to see this current query line. And if you click on current query, we’re going to see the milliseconds runtime.

We’re going to see the current table. That’s the process that we’re currently executing. And then we’re going to have the full statement text for that. And the reason I did this is because I wanted it to be a little bit easier to figure out what was slow and where in the XML, like where the query plan for the slow thing is.

Now, where this still isn’t perfect is in cases where I use dynamic SQL to insert into a temp table. So in some cases like this one right here, you might see query plan, query plan, current query. And current query is going to be this insert.

So you can see in this insert, we are selecting a distinct group of plan IDs from query store plan. And we are selecting stuff that is not like this, you know, crappy maintenance stuff. But then if we look at the query plan directly above that, it’s just going to be this insert that didn’t really look like it did much of anything.

Right. We don’t even see the table that we hit in there. But if we look at the query plan just above that, that’s where we’re going to see that we hit the table that looks at where that holds the query text that we were filtering against. So in some cases, you will still have to do a little bit of noggin thinking.

And if you see a current query that took an amount of time that is alarming to you, then you would have to like, you know, skip one and then go up to the one right below the previous one. So that’s what that’s where it is so far. You know, it’s probably about as good as I’m going to get it because I can’t think of a good way to not get the query plan for the insert, not have like the double query plan.

But hopefully you won’t run into so many performance issues that you have to deal with this all that often. But this this is pretty reliable. And if we go all the way down to, you know, I think some of the larger queries, we’ll still get the full query text in there.

And so this is, you know, at least I think a pretty decent way to get you, you know, have you be able to see very quickly the runtime in milliseconds for whatever query. So if you see like some big number right here, then you know, oh, I’m just going to go take a look at that. And then you can look at what ran and you can say, oh, I guess that was slow.

And then, you know, go get the query plan for it and tell me how to performance tune things, because apparently I’m such a bad performance tuner that I have to build all this performance tuning apparatus into my store procedures so that I can troubleshoot performance on them. I guess I guess I’m just that goofy. Sorry about that.

Anyway, that is the new and improved performance troubleshooting for SP to the underscore quickie sore. And I don’t know, I hope you think it’s at least interesting, even if you never have to use it or deal with it and all that others. I don’t know.

It’s fun, isn’t it fun? Fun to do things. Have fun doing nice things for people. I don’t know. Maybe you’ll take this and build some performance troubleshooting framework for your own store procedures where where where things actually matter.

And you can say, thanks, Eric, darling. Thanks for. Thanks for that awesome idea.

All right. It’s bye. That’s enough. You know, just goofy stuff. OK.

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.

Related Posts