ColumnStore Index

What’s this?
Columnstore index is a new type of index available from the future release of SQL Server known as Denali aka SQL Server 2011. Columnstore index helps data warehouse queries to execute faster than the normal indexes.This index is suited if the volume of data is high and same type of query is used to retrieve the data. Columnstore index is read-only hence DML statements are not possible on the base table hence no OLTP tables please.

How it works?

The name itself gives you an idea how it works. Yes this type of index will store each column on an separate pages there by storing only that particular column values into a page and it can accommodate more column values into a single page. In other words each column has separate page. Now if you perform any aggregation function on a column it will be done better than the normal indexes as it can get most of the values from a single page and there is no need to read each row. Vertipaq technology is used on column store index.

In normal method rows are stored (called as row store) on the pages hence to retrieve a particular column it need to read more number of pages where as in column store you will most of the column values from a single page hence reads are reduced here. Good compression can be obtained as redundancy of data can be found in the column hence less usage of buffer memory. Difference between row store and column store is shown below, the pictures I have posted is just to visualize the difference.

CREATE TABLE Table1(Column1, Column2)

Row Store Column Store

Availablity?
This feature is available from SQL Server Denali CTP2 (as of now its not public release). If you try to evaluate this in CTP1 then you will get the error message below

CTP3 is already released for public, download it from http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Msg 343, Level 15, State 1, Line 1
Unknown object type ‘COLUMNSTORE’ used in a CREATE, DROP, or ALTER statement.

How to create column store index?

The syntax is below

CREATE COLUMNSTORE INDEX ON dbo.TableName ([col1],[col2],.[coln])

Testing the performance using ColumnStore

I have already created a database and a table which has around 10 billion records, now lets do some aggregation testing on that table with columnstore and normal index. I have a test table (FactFinance) where Ive already populated 10 million records to this table. Im going to retrieve average value of amount based on datekey column. First lets do this with normal index and then with columnstore index

Normal Index Row Store

Table ‘FactFinance’. Scan count 3, logical reads 27690, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5460 ms, elapsed time = 3760 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 14 ms.

In the normal index it took approximately 10 seconds to fetch the data. From the above output you could see the logical reads are high and CPU time is also high.

ColumnStore Column Store

CREATE NONCLUSTERED COLUMNSTORE INDEX [NonClusteredColumnStoreIndex] ON [dbo].[FactFinance] 
( 
    [DateKey], 
    [Amount] 
)

Table ‘FactFinance’. Scan count 2, logical reads 471, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 904 ms, elapsed time = 802 ms.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

In columnstore index it executed soon in couple of seconds. From the output above you can see that the logical reads are very much reduced and also it took very low CPU time.

Conclusion:

Column store index is one of the new addition to SQL Server database engine from Denali. Long story short, column store will store each column in a separate page and its read only index because of maintenance cost. Hence this index is best suited for OLAP application and not to OLTP however check all the possibilities for your application.