SQL Server columnstore indexes are new in the SQL Server 2012 release. They are designed to improve query performance for data warehouses and data marts. This page describes query performance tuning for columnstores.
The columnstore index and batch query execution mode are deeply integrated into SQL Server. A particular query can be processed entirely in batch mode, entirely in the standard row mode, or with a combination of batch and row-based processing.
The key to getting the best performance is to make sure your queries process the large majority of data in batch mode.
Even if the bulk of your query can't be executed in batch mode, you can still get significant performance benefits from columnstore indexes through reduced I/O, and through pushing down of predicates to the storage engine.
To tell if the main part of your query is running in batch mode, look at the graphical showplan, hover the mouse pointer over the most expensive scan operator (usually a scan of a large fact table) and check the tooltip. It will say whether the estimated
and actual execution mode was Row or Batch. See
here for an example.
Obeying the following do's and don'ts will help you get the most out of columnstores for your decision support workload.
(Note: we are already working to improve the implementation to eliminate limitations associated with these "don'ts" and we anticipate fixing them sometime after the SQL Server 2012 release. We're not ready to announce a timetable yet.) Later,
we'll describe how to work around the limitations.
Follow the links to the topics listed below about how to maximize performance with columnstores indexes, and work around their functional and performance limitations in SQL Server 2012.