With the introduction of Columnstore indexes we got Vertipaq Compression and Batch mode as the execution mode. The latter results in much faster query execution where large number of rows need to be processed e.g aggregation in a DW query. Naturally the question then becomes why not use a Columnstore index if any way you want to run a DW query. A Columnstore index already supports the Batch mode so problem solved right?
What problem is this feature trying to solve?
Sometimes you can’t put a Columnstore index on a table because the table has frequent updates to it or the table uses features that aren’t compatible with Columnstore indexes. A typically example would be some kind of meter table where it store meter readings from Smart meters all over the country. The table has frequent inserts / updates to it and is fairly narrow however certain aggregation queries need to run frequently against the table so summarize the meter reading at different intervals. Since the table is part of the OLTP design it needs to be available for all OLTP queries while at the same time being available for DW summary queries. The frequent DML do cause a performance issue if we used Columnstore indexes. So we are stuck using traditional indexes which means we forego any IO improvements that could be gained by Vertipaq compression.
How this feature solves the Problem?
Batch mode execution is independent of the Index storage format since it relies on the batch of rows already present in the RAM. By accessing thousands of rows in one fetch (rather than 1 row at a time using Row Mode execution) we are able to avoid having to do Parallelism in the first place. If you use the latest compatibility level 150 the feature is enabled by default.
What does this look like?
Notice the below screenshot where a count (*) is being performed on a table using a traditional index. In the plan details you will notice that SQL Server database engine performed “Actual Execution mode” as Batch. This is basically what the feature is all about.
Execution times
CPU time = 109 ms, elapsed time = 51 ms
Next let’s try changing the compatibility level of the database to 140 and run the query once more.
Notice how the execution mode is now ROW
CPU time = 297 ms, elapsed time = 122 ms.
As can be seen from the execution times even for a relatively small table with just 1 Milllion rows the batch mode does provide significant improvements in execution time.
Prerequisites
- Compatibility level 150
- No Columnstore index on the table
- Fairly large tables
- DW type queries with aggregates etc.
Please Consider Subscribing
