Table of Contents IntroductionFundamentals of Columnstore Index-Based PerformanceDOs and DON'Ts for using Columnstores EffectivelyMaximizing Performance and Working Around Columnstore Limitations Ensuring Use of the Fast Batch Mode of Query ExecutionPhysical Database Design, Loading, and Index ManagementMaximizing the Benefits of Segment EliminationAdditional Tuning Considerations
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.
Go to Columnstore FAQ.