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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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 performance problems quickly.
- An Overlooked Benefit Of Batch Mode With Windowing Functions In SQL Server
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Signs You Need Batch Mode To Make Your SQL Server Queries Faster