Batch Mode Hacks for Rowstore Queries in SQL Server

What’s batch mode?

Batch mode was introduced as a way to help SQL Server process data from columnstore indexes faster. The whole idea with columnstore is that you pull big compressed sets of rows out for aggregation or other operations in big chunks.

Batch mode is a way that operators can work on a “batch” of up to 900 values at a time, instead of working on individual rows. Batch mode can reduce the overhead of metadata and make more efficient use of your CPUs.

Batch mode requires a columnstore index be present on a table in the query (but it doesn’t require it to be used in the plan)

We’re in a weird situation in SQL Server with batch mode at this point.  (As of this writing, “current” released version is SQL Server 2016 SP1). Batch mode operators will only show up in your query plan if you’re referencing a table with some kind of columnstore index.

However, your query plan doesn’t have to use the columnstore index.

This means there’s a few ways to trick SQL Server into using batch mode without really using columnstore.

Hack #1: the impossible filtered nonclustered columnstore index

Itzik Ben-Gan wrote about this hack in his article on the Batch Mode Window Aggregate operator.

Basically, the hack is to create a filtered nonclustered columnstore index that can’t have any rows in it, like this:

CREATE NONCLUSTERED COLUMNSTORE INDEX nccx_agg_FirstNameByYearState ON agg.FirstNameByYearState (FirstNameId) WHERE FirstNameId = -1 and FirstNameId = -2; GO

See that filter? Ain’t no rows getting past that!

This hack only works on SQL Server 2016+, because that’s when we got filtered nonclustered columnstore indexes.

While that nonclustered columnstore index is going to have very minimal overhead, you do open yourself up to the potential of hitting weird execution plans – although in my test case, SQL Server wisely chose to ignore the index.

Hack #2: the bogus left join to an empty columnstore table

To get batch mode to show up, you need to reference a table with columnstore somewhere in the query. But that’s a pretty lenient rule, because:

• The table doesn’t need to be used in the execution plan at all, it can be ruled out
• You can get batch mode operators for OTHER tables just by referencing that columnstore table in your TSQL

This hack basically looks like this:

CREATE TABLE dbo.hack (i int identity); GO CREATE NONCLUSTERED COLUMNSTORE INDEX nccx_hack on dbo.hack(i); GO /* and then in the query you’re hacking… */

LEFT JOIN dbo.hack on 1=0

I’m not sure if Niko Neugebauer, Paul White, or some other scoundrel came up with this brilliant mess, but that bogus outer join to dbo.Hack won’t show up in an execution plan. It’s valid TSQL but the optimizer sees it for the garbage it is and just optimizes it out.

You can even do this trick with a temp table #Hack.

This hack requires changing your code, but doesn’t require SQL Server 2016+.

Hack #3 (not recommended): database compatibility level change to get batch mode

First up: just changing your compat level doesn’t get you batch mode. You’ve got to have a legit columnstore index on a table reference in your query, or be hacking one in somehow.

But sometimes that’s not enough. Niko has written about some cases where batch mode is available (due to columnstore existence), but isn’t being used in SQL Server 2016 compat level 130 due to hints in the query.

In some cases lowering the database compat level to 120 might help that query use batch mode, but DANGER! If you do that, you’ll lose significant improvements made for batch mode that you get at compat level 130.

If you’re facing that problem, you probably want to find a different way to work it out.

Not a hack: you could just use columnstore

This is hopefully obvious, but potentially you could use batch mode in a query with a real, populated columnstore index. These hacks are just for times where columnstore isn’t a fit for your workload.

Want more batch mode? Vote up this suggestion in Microsoft Connect

Suggestion: implement batch mode for rowstore

It sounds like this is going to get implemented, but developer time is precious and your vote would help.

Demo video

Demo code from the video is at the bottom of the page

Related reading

Columnstore Indexes – Query Performance: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance

Important Batch Mode Changes in SQL Server 2016 by Niko Neugebauer: http://www.nikoport.com/2016/06/21/columnstore-indexes-part-85-important-batch-mode-changes-in-sql-server-2016/

What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1 by Itzik Ben-Gan: http://sqlmag.com/sql-server/what-you-need-know-about-batch-mode-window-aggregate-operator-sql-server-2016-part-1

What exactly can SQL Server 2014 execute in batch mode? Answer by Paul White: https://dba.stackexchange.com/questions/97650/what-exactly-can-sql-server-2014-execute-in-batch-mode

Demo code