Software Vendor Mistakes With SQL Server: Not Using Batch Mode

Posted on by Erik Darling

Just Add Batch Mode

There are a few ways to get Batch Mode involved in a query:

  • Be on SQL Server 2019+ Enterprise Edition and hope Batch Mode On Rowstore heuristics think your query is eligible
  • Be on any supported version of SQL Server Enterprise Edition and…
    • Left join to an empty table with a column store index on it on 1 = 0
    • Create an empty filtered nonclustered columnstore index on a table in your query
    • Create an actual columnstore index on your table

Since I’ve used the prior tricks in many posts many times, I’m going to actually create an index this time:

CREATE NONCLUSTERED COLUMNSTORE INDEX magick 
    ON dbo.Posts
(
    OwnerUserId, PostTypeId, Score
);

And now our query plan looks like this:

SQL Server Query Plan
smush

When Should I Use Batch Mode?

Batch Mode can be really powerful under the right conditions, especially when you have a column store index as a data source for your query.

Here’s some generic guidelines for when you should try things out:

  • Column store indexes
    • Big tables, over a couple million rows or so
    • Tables in data warehouses
    • Tables that support custom user searches
    • That wouldn’t otherwise fit in the buffer pool uncompressed
  • Queries
    • That process millions of rows
    • With multiple DISTINCT aggregate
    • That aggregate large numbers of rows
    • That are generated from custom user searches
  • Query plans
    • That have a tough time figuring out the right memory grant and would benefit from Batch Mode Memory Grant Feedback
    • That may be parameter-sensitive and benefit from Adaptive Joins
    • That use windowing functions and may benefit from Window Aggregate operators

Even if the end query result is not millions of rows, Batch Mode can be useful to get you to your result faster.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% 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 performance problems quickly.

Related Posts