When ‘columnstore’ indexes were introduced in SQL Server 2012, they were solidly in the data warehouse and analytic camp. While providing impressive performance improvement for many queries of types commonly used in analysis, tables with ‘columnstore’ indexes were not able to be updated, rendering them useless for transaction processing applications. In SQL 2014 and again in 2016, Microsoft introduced new columnstore index options to make them more useful in real-time analytic databases, that is, transaction processing databases on which advanced analytic queries can be run effectively.
In SQL Server 2012, columnstore indexes were non-clustered, meaning they could only be applied to conventional ‘rowstore’ tables. One of the new features of SQL Server 2014 was the clustered columnstore index, providing the ability to update data stored in columnstore structure. Now, in SQL Server 2016, Microsoft has filled out the columnstore options in two significant ways. Non-clustered columnstore indexes built on conventional rowstore tables are now able to be updated, removing the most significant impediment to their use. It is now also possible to create conventional B-tree rowstore indexes on a clustered columnstore index. B-tree indexes are best for “needle-in-a-haystack” seeks where a small subset of rows need to be retrieved from large table; scans of the clustered columnstore index in such a situation will provide superior performance for analytics queries taking aggregates over large numbers of rows.
Much of the discussion of columnstore indexes has focused on the value of storing only single-column data on each data page. This means that the required column data can be read without the need to read values in other columns not necessary for a particular query. This operation, called “column elimination,” combined with the compression algorithms used for columnstore data immediately provide performance benefits.
Much less attention has been paid to batch mode processing. When processing data from a columnstore index, SQL Server has the option of collecting column values into batches composed of up to 900 rows. This batch size was chosen to take advantage of L2 cache on the CPU chips. Memory chips run substantially slower than the CPU for several important reasons, including limitation of the memory bus, heat dissipation, and good old-fashioned economics. Memory built on the same technology as the CPUs themselves would be enormously expensive. For this reason, modern day CPUs provide on-chip cache memory to provide better performance. Reading data from the cache is far faster than reading data from machine RAM, but the amount of cache memory is small, so an important challenge for software engineers is to keep the cache full of what the CPU needs to reduce the time the CPU must spend waiting data to arrive from external RAM.
Prior to SQL Server 2016, a batch execution plan was only uses in conjunction with multiple cores in a parallel plan. Now, even single-core plans can take advantage of batch mode execution. Curiously, batch mode requires the presence of, but not necessarily the use of, a columnstore index.
Most CPUs have three levels of cache memory. L1, smallest and fastest, accesses memory in roughly a single clock cycle. L2 cache is larger and roughly 14 times slower than L1 cache (details vary with the chip, of course). L3 cache is still larger and roughly 4 times slower than L2. (We won’t take time to talk about non-uniform memory architecture effects here – our topic is still columnstore indexes!) Access to external memory is perhaps 3 times slower than L3 cache. Reading from L2 cache, then, immediately provides more than a tenfold increase in speed compared with reading data from external memory.
In batch mode execution (not to be confused with a “query batch”) the 900-row batches are loaded into cache and provide an immediate 2 to 4-fold improvement in query performance. Not all operators required for query execution are capable of employing batch mode. One of the many improvements in SQL Server 2016 in the inclusion of more query operators in the list of batch-enabled operators. Perhaps most important among these is that sorts are now batch-enabled.
It is easy to determine if your query plan is making use of batch-mode operators by looking at the pop-up window for each operator in the Management Studio.
SQL Server 2016 dramatically extends the practicality and range of usefulness of columnstore indexes for both classic analytic and real-time analytic applications.