SQL Server: Limit Use of Non-Clustered B-Tree Indexes When Using Columnstores
Normally, you don't need to use any non-clustered B-tree indexes on a table that has a columnstore index. There are the following reasons for this:
- Covering B-tree indexes are no longer needed because each column is stored on a separate group of pages. Recall that you should include every column of the table in the columnstore index. So a query that reads any subset of columns will do the minimum necessary I/O. Unused columns will not be read from disk. Covering non-clustered B-tree indexes are normally built to include all the columns needed for frequent queries in order to reduce I/O for scanning data to solve the query. A single columnstore index can be considered a universal covering index, so it eliminates the need for conventional covering B-trees.
- Extra non-clustered B-trees might lead the optimizer to choose the wrong query plan. One of the customers that visited the Microsoft lab built a columnstore index on a fact table that also had five non-clustered B-trees. For some of their queries, the optimizer chose to use a non-clustered B-tree instead of the columnstore. This was a cost-based decision and normally the optimizer makes a good choice, but in this case, the optimizer chose a less-than-optimal plan. The plan based on the non-clustered B-tree took many times longer than a columnstore plan. We were able to force the columnstore index to be used with an index hint. We then dropped the five non-clustered B-tree indexes and tried again. This time the fast columnstore plan was chosen automatically with no need for an index hint. The query optimizer works with approximate information and imperfect estimates, so with a columnstore index, if you want essentially all of your queries to use it, you can make the optimizer's job easier by reducing the total number of indexes available for it to consider. There may be situations where a non-clustered B-tree is appropriate to use with a columnstore, such as to assist with single-row lookups and TOP operations. But oftentimes a columnstore index will give you good enough performance for these operations all by itself.
- Removing non-clustered B-tree indexes on your tables that have columnstore indexes can speed up ETL and save space. That's because those indexes must be built and updated, and that takes time. If you can eliminate several non-clustered B-tree indexes you may be able to reclaim all the time it takes to build the columnstore index during your ETL process, and more. A nice side-benefit to eliminating non-clustered B-tree indexes is that you'll save disk space too.
Return to main SQL Server columnstore index tuning page.