Get More Out of Clustered Column Stored Indexes

To get best performance out of CCI the query should be running Batch mode and Segment Elimination is done in efficient manner.   

Batch Mode Execution

In batch mode execution data is processed in batches of 1000 rows (by CPU). In traditional row mode the data is processed one row at a time.

To verify that query is running in batch mode, look at the execution plan and check for “Actual Execution Mode”, it should indicate “Batch”.  To enable batch mode in SQL 2014, Parallelism should be enabled on the server.

Efficient Segment Elimination

To encourage segment elimination, presort the data and then build the clustered column store index. Presort should be done on column subject to heaviest join or aggregate operation.

To presort, create clustered index on the presort column. Once clustered index is created, create clustered column stored index with Drop_Existing=ON Option.

create clustered index cix_FactInternetSalesCCI on  [dbo].FactInternetSalesCCI(OrderDateKey)

create clustered columnstore index cix_FactInternetSalesCCI on FactInternetSalesCCI with (Drop_Existing = ON)