Frequently Asked Questions.
– What are the Columnstore Indexes?
The Columnstore Indexes are a special type of indexes, which stores the data of each column separately in the internal structures, allowing a great increase in the performance and resources consumption for the analytical queries dealing with big amount of informations (millions of rows)
According to Microsoft Documentation: Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.
– What is the Batch Mode ?
The Batch Execution Mode is the way how SQL Server handles some of the Columnstore Indexes execution, by grouping large chunks of data and applying the same operation on it at the same, thus potentially improving the performance by the orders of magnitude because of less wasted time on the data preparation.
One of its differences to the traditional Row Mode is that it is processing data joined together in chunks from 64 up to 912 units instead of traditional 1 by 1, thus guaranteeing more CPU efficiency for the big amounts of data. Since Columnstore Indexes are being used specifically for the tables with a lot of millions of rows, this is exactly where the Batch Mode is shining.
The Batch Mode query execution was introduced in SQL Server 2012 specifically for the Columnstore Indexes and is being greatly enhanced for every consequent SQL Server version and especially for Azure SQL Database.