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:
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;
-- Dummy columnstore table
CREATE TABLE dbo.Dummy (c1 int NULL);
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.Dummy;
-- Batch mode for the partial aggregate
FROM dbo.FactOnlineSales AS FOS
LEFT OUTER JOIN dbo.Dummy AS D ON 0 = 1;
Plan with dummy left outer join:
Documentation is thin
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.