I think Batch Mode is quite spiffy for the right kind of query, but up until SQL Server 2019, we had to play some tricks to get it:
- Do a funny join to an empty table with a column store index
- Create a filtered column store index with no data in it
If you’re on SQL server 2019 Enterprise Edition, and you’ve got your database in compatibility level 150, you may heuristically receive Batch Mode without those tricks.
One important difference between Batch Mode Tricks™ and Batch Mode On Rowstore (BMOR) is that the latter allows you to read from row mode tables using Batch Mode, while the former doesn’t.
Tricks have limits, apparently.
To cut down on typing, I’ll often create a helper object like this:
CREATE TABLE dbo.t ( id int NULL, INDEX c CLUSTERED COLUMNSTORE );
If you read this post, you’ll understand more why.
Now, let’s compare these two queries:
SELECT p.OwnerUserId, COUNT_BIG(*) AS records FROM dbo.Posts AS p LEFT JOIN dbo.t ON 1 = 0 WHERE p.Score < 50 GROUP BY p.OwnerUserId HAVING COUNT_BIG(*) > 2147483647 OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'), MAXDOP 8); SELECT p.OwnerUserId, COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.Score < 50 GROUP BY p.OwnerUserId HAVING COUNT_BIG(*) > 2147483647 OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'), MAXDOP 8);
One executes in compatibility level 140, the other in 150.
There are a couple interesting things, here.
Even though both queries have operators that execute in Batch Mode (Filter, Hash Match), only the second query can read from the row store clustered index in Batch Mode. In this case, that shaves a couple hundred milliseconds off the seek.
There is likely some additional invisible benefit to not having to convert the row mode seek to a batch mode hash join at the next operator, since one executes for 501ms, and the other executes for 278ms. There’s nothing in the query plan to signal that happening, so you’ll just have to use your imagination.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.