SQL Server 2016: Batch Mode Operators and the Query Optimizer


Previously, we’ve spoke of the value of batch mode processing in achieving optimum performance for analytical queries. Of course, a feature is worthless if the SQL Server query optimizer does not decide to use it. We also mentioned the curious fact that while the presence of a columnstore index is required for the optimizer to utilize batch mode, the columnstore index does not have to actually participate in the query execution plan.

First, let’s look at a conventional analytic query run against the ContosoRetailDW database.

SELECT d.CalendarMonth

, SUM(fs.SalesAmount)

FROM DimDate d

JOIN FactSales fs

ON fs.DateKey = d.DateKey

GROUP BY d.CalendarMonth

With only a B-tree clustered index on the FactSales table, we get a query plan with an estimated cost of 34.35.

Query Plan Before Creation Of CoulmnstoreIndex

Without doubt, we will get the best performance if we drop the B-tree clustered index and replace it with a clustered columnstore index.

Plan with Clustered Columnstore Index on FactSales Table

Here the estimated cost is 1.83.

We might, however, prefer to have a B-tree clustered index on this table, and so we will examine some other possibilities. What sort of benefit might accrue from a nonclustered columnstore index on this same table? We must first return the FactSales table to its original state with a B-tree clustered index on the SalesKey column.

Here is the script to create a nonclustered columnstore index on the B-tree clustered index FactSales. The administrative columns such as ETLLoadID have been removed.



















If we run the same query again after creating the nonclustered columnstore index, we see a mixed plan. The data were read from the B-tree clustered index; this must be a row mode operation. However, downstream we see that the optmizer has made use of batch mode operators.



Row mode must be used to retrieve data from the B-tree clustered index.
Downstream operators, like the hash match show here, can use Batch mode.

The estimated cost is 17.12, indicating that the batch mode performs about twice as well as the row mode for this query. Notice that the query plan does not actually use the nonclustered columnstore index at all. The mere presence of a columnstore index on one of the tables in this query is sufficient to permit the optimizer to generate a batch mode plan.

We can now use this fact to do something quite surprising.

Whiskey Tango Foxtrot

The nonclustered columnstore index created for this query consumes resources. It takes up space and it must be updated as the underlying table is changed. We can get the performance benefit of a columnstore index at the cost of no overhead by dropping the real columnstore index and creating a fake one.


After creating the “Barney” table, we won’t add any rows, providing us with a peculiar table indeed; a clustered columnstore with one column and no rows. This is all we need. We now modify the original query, joining the fake table with an impossible join condition.

SELECT d.CalendarMonth

, SUM(fs.SalesAmount)

FROM DimDate d

JOIN FactSales fs

ON fs.DateKey = d.DateKey

LEFT JOIN Barney ON 1=2

GROUP BY d.CalendarMonth

Query Plan with Clustered Columnstore Index on “Barney” Table

The SQL Server query optimizer recognizes that there are unlikely to be any rows where 1=2 and does not bother to do the join. However, the presence of a table with a clustered columnstore index in the query still permits the optimizer to generate a plan using batch mode, in this case the same plan as it generated when the “real” columstore index was present on the FactSales table. The performance is still twice as good as when there was no columnstore index, but in this case we have paid nothing for the benefit.


Each new version of SQL Server has provided us with a more powerful and more sophisticated query optimizer. However, as the optimizer gets more sophisticated its behavior can become more unpredictable. This continues to leave room for human administrators to tweak systems and queries to achieve better performance.


Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.