Hi Michael,

If you have a columnstore index on a large table, then the optimizer will automatically create an execution plan that uses the columnstore index. If you are not getting the performance improvements you were hoping for, there may be several causes.

1. The columnstore index is not used. That is possible if the query is such that, according to the estimated costing done by the optimizer, it will actually take more time than using any of the other indexes. That can be true (there are actually queries that perform better with a traditional index), or it can be a mis-estimate.

2. The columnstore index is used, but the plan uses row mode execution instead of batch mode execution. That means that the query probably gains some performance from the columstore index, but far less than when batch mode would be used. This is especially common on SQL Server 2012, where batch mode was very limited and a lot of query constructions would cause SQL Server to fall back into row mode. I will cover this in detail in one of the later levels of the stairway.

If you can post a repro script (CREATE TABLE statements, a few INSERT statements with sample data, CREATE INDEX statements, and the actual query that does not perform as expected), I can take a quick look and see if I can give more details. Please test your repro script on an empty test database with case sensitive collation and US-English language settings to ensure that I can simply copy, paste and execute it on my end. Also please understand that I can take a quick look, but cannot afford to take many hours out of my consulting work to assist individual readers of this article - so if you are going to post a 10,000-line monster query, I can only respond by sending you my rates.