A New Query Hint to Override Batch Mode on Row Store Heuristics
Video Summary
In this video, I share an exciting new query hint that I recently learned from a fellow data enthusiast, who prefers to remain anonymous for now. This hint allows you to override SQL Server’s batch mode heuristics, which can be particularly useful when the default settings don’t quite meet your needs. I demonstrate how this works by showing a simple example where applying the “use hint override batch mode heuristics” option changes an index scan and top sort from row mode to batch mode on rowstore, highlighting its potential benefits. Additionally, I point out some quirks in SQL Server Management Studio 2022 that might affect your experience when using this feature, such as a keyboard shortcut issue with the execution plan tab. Overall, this hint offers a handy workaround for situations where other methods of enabling batch mode on rowstore are not feasible.
Full Transcript
Erik Darling here with Darling Data. And we have an exciting video for you today. Because, not me, but someone out there in the world who is very handy with a debugger, I’m going to protect their name because I don’t necessarily want them to get yelled at by Microsoft in case this is explosive information. But there was a new query hint that I was made aware of from Russia with love. And it is something that I think I am going to find very, very useful in my life. And it is a query hint that allows you to override the batch mode heuristics. And by this I mean SQL Server in 2019 introduced the batch mode on rowstore feature.
The problem is that even with that feature, like SQL Server 2019 Compat Level 150 Enterprise Edition, even if you check enough boxes to get to the point where SQL Server will start applying heuristics to your queries, those heuristics may not always kick in when you want them to. And you may like have to find yourself doing stupid things in order to have batch mode on rowstore kick in for your queries. And you may not want that because the heuristics might be stupid. If you remember my parameter sensitivity training videos, I talked a lot about the parameter sensitive plan optimization and how it has heuristics and decides when or when not to kick in, which you may or may not disagree with.
And then furthermore, of course, we talked about its poor bucketing strategies, but that is way too much for this video. We have other cool stuff to talk about. So down in the video description, you’ll see all sorts of helpful links. You can hire me for consulting, you can buy my training and you can become a supporting member of the channel.
These are all things that do require you giving me money. You can do things for free. We all like free things.
You can ask me office hours questions. You know, I do appreciate a thoughtful question from the audience. And of course, if you enjoy this content, you can like, subscribe, tell a friend, all that good stuff.
If you want to see me out in the world, it’s going to be a few months, but hey, that’s okay. It’s going to be cold and you’re not going to, you know, you wouldn’t like me when I’m cold anyway. Data Tune in Nashville, March 6th and 7th.
Tickets are on sale for pre-cons there. And Data Saturday, Chicago. Well, tickets are on sale for pre-cons and a regular event in both of these things. And then Data Saturday, Chicago, March 13th and 14th.
I’ll be there as well with the pre-cons. So buy my book. Buy my book. Buy my book.
And so come see me out in the world when I’m nice and warm. Maybe I’ll wear some shorts. Who knows? We might get crazy. Anyway, I remembered to change my deck image because we are now in the December month. And I better watch out.
I think there’s a mistletoe on there. So there might be some smooches in your future. But it is a Christmas miracle. All of our friends have all of their arms. I think little Timmy over there grew his arm back.
So we can all… We still have a ghost in here. A leftover ghost. A Christmas ghost. Not sure if this is past, present, or future.
But we have a Christmas ghost. Leftover from Halloween. So I guess we didn’t take all the decorations down. Some of our decorations have been repurposed.
Anyway. Let’s talk about this hint. So first thing I’m going to do is just show you this wonderful hint. It is a use hint.
So we need to do the whole option thing. And we need to say use hint. Override batch mode heuristics. Rolls right off the tongue as many of these hints do. This hint will not appear in the sys.dm exec valid use hints DMV.
Much like some of our other favorite hints like enable parallel plan preference. Which, you know… Again, since Microsoft started using this in their code to create the disk and vector indexes.
I assume it’s safe for everyone to use in production. Because Lord knows they don’t test crap. But we’ve got this one now.
How nice. How lovely. So the first thing I want to do is just show you the query plan for this without the hint. Applied.
Applied. And if we run this and we look at the execution plan. It’s nothing terrible. But we can see that this index scan… This is not like, wow, look at how much better performance is. This is just to show you that it’s there and functional.
Then this is like the first demo query that I had where I could show you that quickly. And I just wanted to get this out quickly. So, you know, I guess more interesting stuff will happen in the future. But we can see the actual and estimated execution mode for this index scan is row.
Likewise, we can see this top end sort also occurring in row mode. All right. Cool.
Most of the other operators in the plan are not eligible for batch mode anyway. For example, the repartition streams. But rather the parallel exchanges. So gather streams, distribute streams, gather streams, blah, blah, blah.
Nested loops, not eligible. This sort would be eligible. But this top would not be eligible. But now… Actually, this is a good time to tell you about a strange buggy thing that currently is a problem in SSMS 22.
SQL Server Management Studio 2022. Is that control and R, the keyboard shortcut that allows me to quickly sort of hide results, doesn’t work from the execution plan tab. I’ve opened up an issue about it.
But you have to either click back to the script or click back to messages or results in order to hide them. Which is a little strange. I don’t know how or why that happened. But I’m sure Aaron and co will get that fixed very quickly.
But anyway, if we allow this hint to work its magic and do its thing. Override batch mode heuristic. Look how patriotic this is.
Look at this red, white, and blue over here. Hello, dark mode. If we run this now and we look at the execution plan, we will see this index scan now operates in batch mode on rowstore. Right?
There’s batch mode and there’s rowstore. And we will also see that our top end sort over here is also in batch mode also on rowstore. But you can see the batch happening there.
This sort I don’t think gets it. Oh, thanks tooltip. No, this sort for some reason remains batch mode free. But these two operators over here changed to batch mode. So if you are in a situation where, and again, control and R coming back to bite me.
If you’re in a situation where you want to see if batch mode on rowstore will work for you. And for some reason the other more common tricks for getting it to work are not options for you. Such as creating an empty non-clustered columnstore index filtered to a result that can produce no rows.
Or, you know, doing the left join on 1 equals 0 to a columnstore organized object that is empty. And you can’t make those changes. Then, you know, this hint might be the one for you.
I think the other nice and convenient thing about this hint is that you can create plan guides and stuff in query store. Or using it. And then you could, you know, have SQL Server use query plans and stuff that apply the hint.
So there are upsides to this that perhaps the other tricks do not make available to you. Anyway, something I learned. Something I thought I’d pass on to you.
Because I like you. I think you’re smart and you’re funny. You’re good looking. Everyone likes you. And you, I don’t know.
Let’s just roll out some superlatives here. I think you’re the most. What? Just that. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about something equally compelling in the world of SQL Server. All right.
Goodbye.
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.