SQL Server 2014 – What Can Be Executed in Batch Mode?

batch modecolumnstoresql serversql server 2014

When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) query plan where a surprising number of things executes in batch mode (green):

enter image description here

(This is an estimated plan. I used the actual plan to verify that the actual execution mode is indeed batch.)

Note, that only the build side of T1 uses a columnstore index. All probe inputs (T2 and T3) are rowstores. Their data seems to transition into batch mode. I always thought that batch mode is being used for the data stream that runs through the probe side only.

It seems that data can transition into batch mode even if it does not originate from a columnstore index. That poses the question: Why doesn't SQL Server use batch mode for rowstore-only queries as well? Could be beneficial for some of them. Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode? Could we maybe add a zero row dummy table with a columnstore index to induce batch mode and realize performance gains?

What exactly can run in batch mode as of SQL Server 2014?

Best Answer

What exactly can run in batch mode as of SQL Server 2014?

SQL Server 2014 adds the following to the original list of batch mode operators:

  • Hash Outer join (including full join)
  • Hash Semi Join
  • Hash Anti Semi Join
  • Union All (Concatenation only)
  • Scalar hash aggregate (no group by)
  • Batch Hash Table Build removed

It seems that data can transition into batch mode even if it does not originate from a columnstore index.

SQL Server 2012 was very limited in its use of batch operators. Batch mode plans had a fixed shape, relied on heuristics, and could not restart batch mode once a transition to row-mode processing had been made.

SQL Server 2014 adds the execution mode (batch or row) to the query optimizer's general property framework, meaning it can consider transitioning into and out of batch mode at any point in the plan. Transitions are implemented by invisible execution mode adapters in the plan. These adapters have a cost associated with them to limit the number of transitions introduced during optimization. This new flexible model is known as Mixed Mode Execution.

The execution mode adapters can be seen in the optimizer's output (though sadly not in user-visible execution plans) with undocumented TF 8607. For example, the following was captured for a query counting rows in a row store:

Row to Batch to Row adapters

Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode?

It is today, yes. One possible reason for this restriction is that it naturally constrains batch mode processing to Enterprise Edition.

Could we maybe add a zero row dummy table with a columnstore index to induce batch mode?

Yes, this works. I have also seen people cross-joining with a single-row clustered columnstore index for just this reason. The suggestion you made in the comments to left join to a dummy columnstore table on false is terrific.

-- Demo the technique (no performance advantage in this case)
--
-- Row mode everywhere
SELECT COUNT_BIG(*) FROM dbo.FactOnlineSales AS FOS;
GO
-- Dummy columnstore table
CREATE TABLE dbo.Dummy (c1 int NULL);
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.Dummy;
GO
-- Batch mode for the partial aggregate
SELECT COUNT_BIG(*) 
FROM dbo.FactOnlineSales AS FOS
LEFT OUTER JOIN dbo.Dummy AS D ON 0 = 1;

Plan with dummy left outer join:

Batch mode with dummy table

Documentation is thin

True.

The best official sources of information are Columnstore Indexes Described and SQL Server Columnstore Performance Tuning.

SQL Server MVP Niko Neugebauer has a terrific series on columnstore in general here.

There are some good technical details about the 2014 changes in the Microsoft Research paper, Enhancements to SQL Server Column Stores (pdf) though this is not official product documentation.

Related Question