Advertisements

SQL-BOX.COM

–SQL Server Storage

ColumnStore Indexes in SQL Server 2011, Code Named “Denali”

This new type of Index is introduced with new upcoming 2011 version of SQL Server. This new index improves the query performance by hundreds to thousands of times in some cases but in routinely gives at least ten times acceleration to your queries. This index will be more useful in dataware house databases.

Why this is called as columnstore Index?

A columnstore index stores each column as a separate set of disk pages, rather than storing multiple rows per pages which are called as rowstore.

The difference between rowstore type and this new columnstore type is shown in  figure below.

 

 

 

 

 

 

 

 

Syntax:

create columnstore index [Index_name] on [table_name]([column_name], [column_name],…..)

When you create the columnstore index on a table they store all data for separate columns on separate pages. This improves I/O scan performance and buffer cache hit ratios.

When and what kind of tables benefited from this?

You will create columnstore index on a Fact table also on dimensions tables if you have extremely large dimensions for example more than 10 million rows then you will be creating this indexes and run queries against those tables you all will see wonder that they ran really very fast usually more than 100 times, said by Microsoft.

 Advantages:

  • It improves performance by decreasing the need to fetch data from disk to memory.
  • Accelerates query performance by more than 100 times to 1000 times in some cases
  • You can get interactive response time for queries against billions of rows.

Disadvantages:

  • For this year “Denali” release the table with columnstore indexes can’t be updated  directly using INSERT, Update or Merge.
  • In order to update table with Columnstore index, the index needs to be dropped then update the table and again re-index columnstore but this should be not a problem if we need high performance which saves lots of time to your business.

In short time we will comeup with more info on this columnstore index once sql server 2011 released.

Thanks for reading this post…. 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: