Query Store Hint and Plan Guide Annoyances in SQL Server
Video Summary
In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.
Full Transcript
Erik Darling here with Darling Data. And today we’re going to talk about some of my annoyances with query store hints and plan guides. If you have a problem with that, you can leave. Before you leave, before I talk about that, there are some links down in the video description. If you want to hire me as a consultant to make your SQL servers faster, in exchange for money, you can do that thing. You can also buy my training if you want to try to do that on your own, learning from me, because I’m good at it. You can also support this YouTube channel because, I don’t know, I mean, I guess the camera’s already paid off, but, you know, these ring lights, electricity ain’t getting any cheaper, right? Something like that. If you want to ask me office hours questions, I do that every Monday. I answer five of them, as long as there are five to answer. If there are five to answer. If there are not, I sit in a corner and cry and wonder why no one likes me. But, anyway, I’ll be leaving the house starting in March of 2026, specifically going to Nashville and Chicago, one weekend after the other. Data Tune Nashville and Data Saturday, Chicago. Good times ahead. I will be teaching advanced T-SQL pre-cons at both of those.
So, buy those tickets now, because they don’t last forever, kids. Didn’t always have it this good, did we? Couldn’t just always leave the house and go get some SQL Server training outside. I don’t know, right? Anyway, let’s talk about why these things are annoying. So, that’s not what we needed. We needed Management Studio, not Hyper-V. Hyper-V is an unserious technology for consultants like me. Anyway, let’s truncate this table called bad idea, and let’s stick five rows in the bad idea. And so, what I want to show you here is, I mean, it’s just kind of a funny hint, right? This no wait hint. Under normal circumstances, no wait won’t do anything, right?
But, like, if we, like, say, start a transaction, and we try to insert a row into this table, we don’t close the transaction. Then no wait does kind of a funny thing. It actually just does, like, what a lock, like, the lock timeout thing does just immediately, right? I mean, it still gives us, like, the first five rows, but then on the sixth row, it bails, right? It’s like, ah, there was a lock. I’m gone, right?
So, it doesn’t return results. We could select, sorry, we can’t select ID6, because ID6 is the row that we have locked for the insert, but we could select ID5. Why we have this hint, well, I mean, I don’t really know or care all that much, but we’re here to talk about plan guide annoyances. Now, let’s say that we wanted to get rid of this hint. Let’s say that this hint was ruining our lives.
Let’s say that this hint were completely destroying everything that we believed in as far as SQL Server goes. We’re like, why does this query just, like, just die the second it encounters a lock? Is that, should we be doing that? Is that correct?
Like, maybe we should use lock timeout, make it wait a little bit longer. But, so, Query Store has the ability to add hints to queries via the option mechanism. I don’t know why ZoomIt is being difficult, having some trouble starting up. Apparently, it’s cold.
But, if we were to try to add a table hint, the way that we can add a table hint with plan guides, well, Query Store gives us a rather infuriating error. Query Store says, no, you cannot set query hints, or rather, setting query hints table hint in Query Store is not supported.
Query Store says, no, you cannot set query hints. Well, Microsoft didn’t think very hard about this one, did they? I mean, they thought hard enough to make an error message, but what a giant gap in capability.
Query Store says, no, you cannot set query hints. Someday, competent people who care will work on SQL Server again, I’m sure of it. We don’t know when, don’t know how, but someday it’ll happen for us.
Now, with plan guides, you can supply table hints, but plan guides also have a rather annoying thing with them. Where, if we tried to create a plan guide like this, and the cool thing about like table hints is you can use them to ignore a lot of stuff. Like, if there was like an index hint on this thing, and you were like, wow, that index hint is stupid, you could use a table hint like this, and just point the table hint to the alias for the table, and that index hint would effectively be ignored for the query.
But we can’t do that with like walking hints, right? If we try to run this, SQL Server will say, well, let’s put this in a way that is a little bit more easy to digest when I zoom in. Let’s see.
Cannot execute query. Semantic affecting hint no weight appears in the with clause of the object bi, but not in the corresponding table hint clause. Change the option table hints clause so the semantic affecting hints match the with clause.
Now, I might very well be using a plan guide specifically because I want to override that semantic. Maybe I hate that semantic. Maybe that semantic is hurting things.
Maybe I want to get rid of it. I wish that this thing had like the PowerShell like dash force parameter for it. Because it’s like, no, I specifically want this to not happen.
I’m trying to make this not happen. Why are you making me do this? So that’s my annoyance there.
With query store, it’s just like, no, no table hints whatsoever. With plan guides, it’s like, no, but you need to keep this thing because we say so. And you’re like, no, I want to get rid of this thing.
This thing is actively hurting me. Now, like, you could do that with a query like this. And you could, like, if there was an index hint, you could say, hey, like, let’s ignore that index hint.
That is completely successful. And, of course, we could get this to work with the table hint down here. But it would completely undo what we’re trying to do, which is not have the no wait hint on here.
And this will happen with, like, lots of dumb semantic affecting hints because SQL Server is like, oh, no, the semantics. But you’re like, I want to get rid of the semantics. But you’re making me keep these semantics that I don’t want.
So it would be really nice if with, you know, query store, we had the ability to supply table hints because what kind of half-baked situation is it that we don’t have that? And with plan guides, it would be nice if you could say, no, plan guide, I really do want to override that semantic affecting hint because I don’t like it. I think it’s bad and I don’t want it there.
So that’s that. Neither one of these things exactly, neither one of these plan forcing features exactly covered itself in glory here. Query store, of course, with the completely half-baked query hints and plan guides with not allowing me to get rid of the semantic affecting hint that I intended to get rid of with a plan guide.
So thanks for both of those. Nice Christmas presents. All right.
Happy Merry. Thanks for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where I think we’re going to talk about how Wob data makes for some weird locking. And we’ll have a lot of fun with that, won’t we?
All right. Thank you for watching. Thank you. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.