The Best Way To Troubleshoot Slow SQL Server Queries
Video Summary
In this video, I delve into the process of troubleshooting code found in SQL Server’s QueryStore or plan cache, focusing on how to correctly reproduce and test it. Using a stored procedure as an example, I walk you through creating a temporary stored procedure from the query text and XML plan retrieved from QueryStore. This method allows for easier testing and modification without affecting the original stored procedure, making it particularly useful when dealing with complex queries or dynamic SQL. While this approach may feel like an anti-pattern compared to using actual stored procedures, I find it more straightforward and less error-prone, especially during live tuning sessions where quick changes are necessary.
Full Transcript
Erik Darling here with Darling Data. I just noticed something weird under my thumbnail. We don’t have to talk about that though. In this video, I’d like to talk a little about troubleshooting code that you might find in QueryStore or the plan cache and the correct way to reproduce it. Now, this works. I’m using a stored procedure in this case. It works equally as well for parameterized queries that you might find from an ORM or another application. It does have some limitations though, of course. If you only have part of the query plan or you only have a partial fragment of the code, you might be missing things like temp tables, local variables, variables, table variables that you would need to execute it, but at least in most fairly straightforward cases, it’s pretty easy to do. I’m just going to use an example that I wrote quickly to use QueryStore with the stored procedure because it just happened to be easier that way. I could have used just as easily written parameterized dynamic SQL on the same thing, but I decided to do something else instead.
Okay. So, what we have here is the output from SP underscore QueryStore for a procedure that I wrote called OhMyGodWhy. Note that because this procedure lives in the DBO schema, I did not have to fill in the procedure schema here. If you have different custom schema, you would have to supply that. So, QueryStore knows where to look. So, what you get back from QueryStore that we’re going to focus on are two columns. One of them is the query text and the other one is the query plan. And we need to get elements from both in order to assemble them in a way that we can test this code. Now, a lot of people out there will test this by declaring local variables and trying to rerun stuff, but I’ve done enough videos on local variables on local variables on local variables on local variables now that if you’re still doing that, I ought to just come talk to you nicely about why you shouldn’t.
So, the first thing you get is query text. And the way that you get query text is not terribly, terribly helpful to executing it. So, if you stick this in a new window, it looks a little bit like this. And you can’t do much with that. But what you can do is create a temporary stored procedure. So, if you do this, alter procedure, wow. That’s 303, wow. And that should be procedure, not proc.
And if you do this and you add in an as, then you have what will basically be a fully functioning stored procedure. The problem is, if you want to execute this stored procedure, you need to get the parameter values to execute it with from somewhere. All of that stuff lives in the query plan. So, let’s get rid of this window because we don’t need that anymore.
If we go into the query plan, now, I fully admit that if you have like one or two parameters, it might be easy enough to just go grab the values. If you get the properties of the select operator, whatever the root operator is, way over there. Tragic green screen accident hand.
See, well, server has values for all of these things available. But the thing is, I don’t want to try and copy and paste stuff out of here because this is a pain in the ass to do. I don’t like it.
So, that’s not where I would get this information from if I had several parameters the way I do here. All right. So, I’m just not going to do that. Not going to do that at all.
What I am going to do is grab the query plan XML. If we scroll way down to the bottom, we have this blob of information in the XML that we can use to pull parameter values out of. Now, one really tragic downside of the plan XML is that it stores the parameters in the exact opposite order that they appear in in the list of parameters for the stored procedure.
So, answer count is last. Creation date is second to last. Last activity date third.
Owner user ID and score. That’s the exact opposite of the way they are here. They also have a whole bunch of stuff, XML thingies in them, that sort of make for a tragic set of circumstances. When you want to get the values out, like all of the integers, small ints, tiny ints, big ints.
I believe other numeric types are surrounded by parentheses. I mean, dates are fine. That’s just a string anyway.
But you can grab all this information out of there, and then what you can do is get rid of that thing. We don’t need that anymore. But I have a sort of fully formed version of that here, right, where I’m selecting. Oh, that’s off by one, isn’t it?
There we go. Where I’m selecting from posts, which is basically I have all the stored procedures set up the way it was set up as an actual stored procedure. Except now I have a temporary stored procedure where it is totally safe to make changes to. And I can test changes to the temporary stored procedure without affecting the actual stored procedure.
Granted, I could always make a copy called like, oh my god, why underscore Eric? But, you know, I don’t always want to go around creating objects. Some people are sensitive to change management, new code entering the database without proper guidance, things like that.
But if I run, I want to turn on query plans, and I run, oh my god, why? As a temporary stored procedure. I’ll get the actual execution plan back with all of the actual execution timings.
It looks like SQL Server is recommending an index to help this stored procedure out. Let’s see what the details are on this. All right, so SQL Server thinks that we need, well, I mean, all of the key columns.
That at least kind of makes sense, because we’re searching for those, but then SQL Server also has this somewhat boneheaded idea that we should include every column in the table in our nonclustered index, which, again, if you watched my video on SP Blitz Index recently, you’ll know my thoughts on the missing index request feature. I would not do this to a table unless I absolutely had to.
It’s cruel and astoundingly unusual to create an index this big. There’s a couple few big strings in there, including body, which is an Embarkar max, and we just don’t want to do that. So the temporary stored procedure thing I know feels like an anti-pattern when we have an actual stored procedure, but like I said, some people are sensitive to adding new code into a database without it going through change management and other stuff like that.
Other times you might just find a piece of dynamic SQL, or you might find application code. And, you know, you could, granted, again, you could rewrite it as dynamic SQL, like parameterized dynamic SQL to do this. I just find this to be a little bit easier to do.
Granted, there’s some assembly required no matter which way you do it, but I just find this to be an easier way to do stuff and make changes. Because if you rewrite this as dynamic SQL, you’re probably also going to have to deal with, like, changing single ticks to double ticks. And you’re going to have to, like, you know, deal with, like, maybe debugging stupid, like, you’re missing a parenthesis or comma errors in dynamic SQL, which just don’t show up.
Like, like, IntelliSense and, like, SQL prompts and other tools like that. Just don’t check dynamic SQL for syntax errors, and it’s just a lot easier to kind of deal with it this way. So as much as I love dynamic SQL, I do love temporary stored procedures.
In this case, a lot better. It’s just a lot less fiddling and tinkering and looking like a fool while you’re trying to tune queries in front of a live audience. So anyway, that’s what I do.
Maybe it would be useful for you to do it that way, too. I don’t know. I don’t know your life.
You might hate this whole thing. Anyway, I hope you enjoyed yourselves. I hope you learned something. I hope that you will find it in your hearts to like and subscribe.
Like this video and subscribe to my channel. And thank you for watching. I don’t know if I’m done for today, but I certainly feel done for today.
So who knows what will happen, though. All right. Thank you for watching.
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.