What exactly can SQL Server 2014 execute in batch mode?
24
votes
1
answer
4946
views
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):
(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?

Asked by usr
(7390 rep)
Apr 12, 2015, 02:11 PM
Last activity: Sep 2, 2018, 04:55 PM
Last activity: Sep 2, 2018, 04:55 PM