Of RECOMPILE Hints And Query Store: Where Are My Parameter Values?!
Thanks for watching!
Video Summary
In this video, I dive into the world of query tuning with a focus on recompile hints and Query Store. Erik Darling from Darling Data shares his insights based on extensive experience in performance tuning, particularly when dealing with complex queries involving temp tables or table variables. He discusses the frustrations that come with trying to tune such queries using only the data available in Query Store, highlighting issues like missing parameter values and the difficulty of reproducing slow query scenarios. To illustrate these points, Erik demonstrates how recompile hints can be used effectively, contrasting them with the limitations of traditional methods. By walking through a practical example, he shows viewers how to leverage recompile hints for better performance tuning outcomes.
Full Transcript
Erik Darling here with Darling Data. And we’re very happy. I promise. We’re thrilled. We’re thrilled beyond compare. In today’s Lent Apology Free video, we’re going to be talking about why PowerPoint, not Excel, why PowerPoint decides to double advance when I click on it. Just kidding. We’re going to talk about Recompile Hints and Query Store. So I do the majority of my performance tuning work looking at Query Store data. Most people who I work with don’t have a good third-party monitoring tool that captures the type of stuff that, like, I really get into, like, you know, performance metrics, queries, running, resource usage, yada, yada, yada, yada. It really gives me a good sense of which queries I need to go of which queries I need to go after to fix stuff. So most of the time I end of the time I end of the time in query store.
I’m dealing with a lot of sort of weird situations. Query store is wonderful. Query store is wonderful. But I think just sort of in general query tuning when certain things are going on in SQL Server can be quite frustrating.
Recompile Hints are probably the least frustrating. What can become very frustrating is when the query you need to tune uses a temp table or table variable. And the process of populating that temp table or table variable is not quite straightforward.
So, like, you’re working on code in a stored procedure and you would have to execute a whole bunch of other stored procedure stuff that’s not enough to get the, you know, temp table or table variable populated in order to execute the query that you care about that’s taking a long time. That’s the first part.
So, like, when you look in query store, you just get the query that was slow. Even if it’s in a stored procedure, you don’t get the whole, like, stored procedure plan the way you do in the plan cache. You just get that one single query.
And if there are table value parameters, table variables or temp tables in use, those don’t show up. Where it gets also frustrating is if someone is using local variables or if someone is using option optimized for unknown, the compile time parameters, I mean, the runtime parameters are never in there unless you play some weird tricks, but the compile time parameter values won’t be in there for local variables or recompile.
So that’s also a bummer. That also makes troubleshooting stuff really difficult because you have no idea how to re-execute the query to reproduce what’s slow in order to figure out what to fix. Bah!
What’s the other one that I really hate? Oh! So, if you pass in a parameter, but the parameter isn’t used in, like, a where clause, like, let’s say you have four parameters, three of them get used in the where clause, and one of them gets used in, like, a case expression in the select list, the value for the one that gets used in the select list case expression won’t be in with the rest of the parameter values because that had nothing to do with cardinality estimation.
So, three really frustrating things that you can run into when you’re trying to tune queries, and I really wish that there was, like, you know, like an instant replay button or, you know, something cached along with a plan that would make queries like that runnable so that you could figure out, like, you know, get an actual execution plan, look at the, you know, like the operator time statistics and get, like, some better clue about where in the plan you need to focus to fix things.
Sometimes you can look at a query plan and figure it out. Other times, you know, that cached slash estimated plan just lies to you in too many places and too many ways to make that useful. So, what recompile hints do is almost the opposite.
It can be, in very large execution plans, it can be frustrating, and I’m going to show you why. In smaller plans, it’s fairly easy to, you know, figure stuff out, but we’ll talk about that in a minute. Before we do, though, you might be surprised to hear, this might come as a shock to you, that you can support this channel by signing up for a membership, and that there’s a link in the video description to do that.
You can do that for as little as $4. That’s cuatro dólares. Dollares?
I’m not good at it. I’m not multilingual. I only speak like English is my second language. But if you’re unable to fork over the four bucks a month, you can keep me company in other ways that are also heartfelt, like liking and commenting and subscribing.
I think we’re at about 20 members, and we’re at about over 4,700 subscribers. So I’m feeling, feeling quite loved and adored these days. Maybe, maybe at 5,000 I will just burst with joy.
If you need SQL Server consulting help from a young, handsome fellow with reasonable rates, I am very good at all of these things. If you need me to do something else, we can discuss what that something else is and figure out if it’s anything that I am also really good at. Just, you know, make sure it involves SQL Server.
It’s the only thing that I ask of you. If you would like some high-quality, low-cost SQL Server training that beats the pants off the competition’s Black Friday rates, you can get all 24 hours of mine for the rest of your God-given life for about 75% off.
That brings it down to about 150 US bucks for you, special folks out there. If you are a live and in-person type of person, and you want to see me and Kendra Little co-present two days of performance tuning madness, you can do that this November 4th and 5th at the old Pass Data Summit in Seattle, Washington.
It’ll be fun. That’s all I have to say about that. It’ll be fun, and if you miss it, you’re going to regret it, because it may never happen again.
Just don’t know where the future will take us. But with all that out of the way, let’s do what we normally do and party hard and talk about recompile hints and query store. So we’re going to use Stack Overflow, and we’re going to get rid of some indexes that I had for something else.
And I’m going to create this store procedure that has basically the same query in it twice. Really, what these queries actually do is completely unimportant. All I need to do is show you the difference in how things look in query store.
So I’m going to give this a few runs just to make sure. Actually, we’ll see how long this takes. All right, one second.
We can give this a few runs just to make sure that everything ends up in query store where we want it, because without that, we are sunk. And now let’s use my free store procedure, SP Quickie Store, to find plans for this store procedure. You might be looking at this and thinking to yourself, wow, Eric, that’s amazing.
I can’t even find stuff by procedure name in the query store GUI. And you’d be right. That’s why I’ve spent, like, thousands of lines of code and hundreds of hours of my life working on this store procedure, to make life easier for everybody, because Microsoft won’t do it.
Isn’t it nice? So we’re going to run this, and we’re going to have two entries in here for our two queries. We’ll see two individual query IDs here.
And we’ll see, well, I mean, I suppose two different plan IDs, too. That all makes sense. And this first one is going to not have a recompile hint in it, and this second one is going to have a recompile hint on it.
We’re not, like, comparing how if recompile helped or hurt these queries, because we really just ran the query with the one parameter. It’s not like there’s going to be parameter sniffing or the thing ran in one second, so there’s not really anything to really fix.
The only thing that I want to show you is that in the plan without the recompile hint, if we go to the properties and we look over here, if we go to the properties of the select operator and we look over here, we will have the parameter list and the compile time value for the parameter here.
This makes it really easy to, you know, pull that query, pull the query text out of QuickieStore’s results, plop it in a new window, and then do something like, you know, create a temporary store procedure to execute it with that parameter value so that you don’t end up having to do anything weird, like, you know, declare a local variable which screws things up or whatever else.
Great. That’s how that happens there. Now, let’s look at, what did I just close? I don’t know what I just closed.
I might have closed the wrong thing. We’re going to have to go back and figure that out. Did I close? Yeah. Oh, no, it’s over there. Things just moved around strangely, I guess.
Anyway. Yay! Let’s look at the query plan for the query with the recompile hint in it. Now, if we go to the properties of this one, we are not going to see, oh, I actually hit the shift key.
There we go. We’re not going to see the same parameter list over here. Why? Well, option recompile does something different from, obviously, what option optimized for unknown or using local variables is, where you just have no record of what those values are in the query plan XML.
What option recompile does is it treats your parameters and local variables like literal values, and those literal values get used when you touch, well, hopefully when you touch tables and indexes.
But if we hover over this thing, we’ll see, oh, baby, easy, zoom it. We’ll see the literal value that we passed in to the store procedure here as a predicate scanning the clustered index.
Now, again, this isn’t a query tuning competition. This is just to show you how this behavior might look a little bit different. Now, where this can get annoying in really big plans is if you have a lot of different predicates or parameters that you pass in, and you have a lot of different tables that get used, and some of them have parameters and some of them don’t.
You have like 20 joins and like a where clause with like eight different predicates in it, and you hit eight different tables. It’s kind of a hassle to go to each table, figure out which literal value predicates got used there, and then reconstruct things that way.
I really wish that Microsoft would make this stuff easier, both for option recompile, option optimize for unknown, and all the other stuff. I realize that when you use option optimize for unknown, and you get this sort of density vector guesses, the message is that it shouldn’t matter what the compile time values are, because you get the same crappy guess no matter what.
But it really does help to figure out, you know, what the, like I feel like the compile time values should still be included in there, because if you’re troubleshooting a slow query, obviously the local variable guesses for the data distribution of what you searched for didn’t work out one bit, and you need to focus on reproducing that and figuring out how to fix it, like obviously aside from just nuking the optimize for unknown hint.
A great way to test that is to add an option recompile hint instead and see if you get a better plan. Anyway, before I drift too far along here, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that I do not need to apologize for the length of this video to anybody out there and the entire internet, and I will see you in the next video. Goodbye, farewell, take care of yourselves.
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.