sp_QuickieStore Performance Trouble Shooting

Posted on by Erik Darling

Four Hours


Thanks for watching!

Video Summary

In this video, I delve into setting up performance troubleshooting mechanisms within a stored procedure named `SP_quickie_store`. I introduce a bit parameter called `troubleshoot_performance` that defaults to zero to avoid unnecessary overhead. The setup includes logging queries and their execution times in a simple table with computed columns for timing details. By using dynamic SQL, the process can be easily extended if more logging is needed without altering existing code. Additionally, I demonstrate capturing query plans specifically from parts of the stored procedure where performance issues might arise, using `STATISTICS XML` to focus on relevant queries rather than overwhelming data. This approach provides a practical solution for diagnosing and improving SQL Server performance, especially when dealing with complex or frequently executed procedures.

Full Transcript

Erik Darling here with Erik Darling Data. Having a grand old time. There is some sort of thunderstorm going on outside. So hopefully if you get sick of listening to me you can just tune out and listen to the lovely white noise machine storm that is currently brewing on the East Coast. In this video we want to look at how I set up some performance troubleshooting things, what to call them, captures, trap, I don’t know, whatever, in SP underscore, underscore, underscore, quickie store. So the first thing that you might notice is a parameter that you can pass into SP quickie store called troubleshoot performance and that it is a bit which defaults to zero because you don’t want it turned on by default I don’t think. Now, one of the first things that I do is declare a couple parameters here. Oh wait actually there’s something else that I do up here that I think I might have just skipped past a little bit. Is I have a logging table for things that I want to log as queries run to capture information about them. It’s a pretty simple table. It’s an identity. The current table which we saw in the last video is where I used to figure out where I am in the world. Start time and end time is what I do.

Start time and end time and then a computed column that tells me the number of milliseconds between start time and end time and I do format that number to stick some commas in it just in case things get on the long side and we want to figure out the exact scale and whatnot of things. And then the next thing I do is I declare a couple variables to hold dynamic SQL and it’s not terribly dynamic SQL and this section really is only here to shorten the number of, shorten the code a little bit. You know, like the, I have, if you go down to where the code actually lives, I have right down here and insert that puts data into a, into that temp table, the troubleshoot performance temp table and that passes in the current table thing and then I have an update that updates the end time where the current table equals a current table. And so this is what allows me to figure out how long a statement ran for. But that’s not all that I do. If we scroll down a little bit further, what I do and all of these blocks and I just go back a little bit. So the reason that I have this part set up in dynamic SQL is because I, in just in case I want to add more columns to the table and more stuff that I want to log, I only have to, I have to make minimal changes to do that. I don’t have to find every time I would do that insert and change it.

I can just change the one insert and the one update and then I can go get, you know, I can plop that in there and it executes the new SQL and all the places that I care about. So the next play, next thing that it does is if troubleshoot performance is set to one, then I will fire off that dynamic SQL block with the current table that I’m working on, which again, that gets set right here to tell me which block of code I’m in. And then I set statistics XML on. So this is a command that I can use to capture query plans that I care about.

Like, so if I care, like, you know, like from troubleshooting performance, you know, I don’t want to just set statistics XML on for the whole procedure because it’s going to capture a bunch of nonsense. It’s going to capture a bazillion things. I don’t need that. The only things that I care about, the statements that I know that might hit issues, are ones that go and touch the query store views because those are the ones that are subject to the awful design and indexing and implementation of query storage views.

Sorry, it’s just the truth. They suck. That’s why I wrote this because every time I had to, every time I was working with a client, I’d be like, how do I do that in query store again? It would just be like this process. It’s not something that you shouldn’t have to deal with stuff like this. This stuff shouldn’t be harder than it already is.

Like, it’s hard enough dealing with SQL Server performance, a million different things that go wrong. Finding out what went wrong shouldn’t be as hard as it is. So, this will set statistics XML on, and then after the code runs, right, we see the SP execute C will define, see if the query store exists.

Then if troubleshoot, again, troubleshoot performance one being set to on, the first thing I do is turn query plans off. Now, the reason I do this in here is because I don’t care about the query plan for inserting into the temp table. All I care about is the query plan for inserting into the query store exists thing, right?

And then, so this is troubleshoot performance set to one, then I set statistics XML back off, and then I run the update to update the current time and place and all the other good, fun things that are in there. Now, I can’t remember if I had recreated this since I added that, but what I’ll show you what it looks like real quick.

So, if you hit troubleshoot performance, then you will get back. This doesn’t perform terribly on my computer because my computer doesn’t have a lot of query store data in it, but other people are going to have a tougher time.

So, first thing you get back is this set of things that will show you the query plans for the queries that executed in the parts of the store procedure that we might care about performance in. There are kind of a lot of them, and it’s a little annoying that since we’re executing dynamic SQL to do an insert, for a lot of these things, what you’re going to see is this insert query from a parameter scan, and I can’t really do a lot about that.

You know, like we see this is the query that ran, that we pulled data from, but then like the insert that we did to pull the results of it, that is a separate execution plan. So, it’s a little annoying.

It is a little sort of, you know, a little bit more data than I’d want to return from the query plans, but there’s not a lot I can do about that. Then once you get past the end of the query plans, something that is a little bit more helpful that will at least help you figure out which plans you should focus on is the results of that table that show how long each one of the steps ran for.

Now, of course, my query store is not in terrible shape, so, you know, things turn out okay here for me. If I can just make this a little bit bigger so you can see what’s going on here. I have all of the steps that my procedure hit when it ran.

Again, this might look different for you if you use different parameters, search for different things, exclude different things. But this is what it looks like. We see the start time and end time.

We see how long each one took. Then we can kind of say, okay, well, you know, this is the sixth one down. It, you know, took 20 milliseconds. Oh, boy, you better call the police. And then we can, you know, kind of get a feel for where in this we should start looking. One, two, three, four, five, six.

You know, it’s not going to be exact because we’re going to have to deal with the separate insert plans, but we can at least start to figure out where things might have taken 20 milliseconds and start focusing on that. So, anyway, that was kind of a neat thing that I thought, neat extra for this.

I do anticipate people having weird problems with query store performance searching some of this stuff, you know, especially if your query store is very active or very large, then you’re most likely going to hit some oddities trying to query it.

It’s been my experience dealing with query store data, even using the GUI. Like, this isn’t supposed to be a replacement for the GUI. This is an alternative to the GUI because even when I use the GUI to access some of the reports, whether it’s regressed queries, like, you know, top resource consumers, stuff like that, it’s very, very, very slow sometimes.

So, anyway, that’s my story there. That’s how I implemented performance troubleshooting into this. And, you know, if you run into performance issues, you get the query plans back, you know, maybe you see something that I messed up, something I could do better, you know, feel free to let me know on GitHub.

That’s where I do all my, you know, troubleshooting, stuff like that. So, feel free to let me know there. Be happy to take any feedback from the general public. Otherwise, it’s just me kind of feeling lonely working on this stuff.

Anyway, that’s my video about implementing performance troubleshooting. In the next one, we will look at how I implemented passing lists of strings into the stored procedure in a safe way. So, that’s what we’ll cover next.

And the final video in this series will be how I implemented debugging. So, crazy, fun, sexy times for you and me ahead. Choo-choo.

Yeah. That carpet’s on fire. All right. I’m going to go now. Thank you for watching. I hope you enjoyed yourselves and learned things. And I will see you over in the next video.

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