Columnstore Indexes Overview

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.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.