January 2013 - Posts

Columnstore Indexes Overview
02 January 13 10:10 AM | GavinPayneUK | with no comments

In December 2012, I presented about “Accelerating the Data Warehouse Using xVelocity Columnstore Indexes” at a SQLSanta event in London.

My presentation’s purpose, and demos, were to show how this type of database engine index, that’s new in SQL Server 2012, significantly improves the performance of data warehouse style queries. 

Columnstore indexes, and their complimenting Batch-Mode Query Processing feature, are used transparently by SQL Server once they’re created so the benefit for SQL Server professional is that that there’s no need to change existing data warehouse applications or queries to benefit from these performance improvements. 

Slides

The presentation’s slides are available here.

Session Questions

Below are some of the answers to questions that I remember giving during the session:

  • Even though the levels of internal compression of data within a Columnstore index are affected by the cardinality of the index’s data, the performance benefits of Columnstore indexes are so great compared to a non-clustered index that your data’s cardinality is not likely to influence whether you use Columnstore indexes or not;
  • You can use Columnstore indexes with AlwaysOn Availability Groups;
  • The SQL Server query optimiser will automatically consider using a Columnstore index and Batch-Mode Query Processing itself, you don’t have to re-write queries to use start using them;
  • Batch-Mode Query Processing only works with Columnstore indexes;
  • The column order, and number of Columnstore index keys, do not affect the performance of a query that using the Columnstore index.