You are here:Home » SQL Server Denali » COLUMNSTORE Index - A new feature of SQL Server Denali CTP3

COLUMNSTORE Index - A new feature of SQL Server Denali CTP3

After downloading SQL Server Denali CTP3 I have posted some new features. You can visit my earlier post for the overview with list of all features. Today I have reviewed a new feature and it is COLUMNSTORE index, which is introduced by SQL Server 2011 CTP3.

What is COLUMNSTORE index ?
It is special type of index, which stored columns in a separate set of data pages instead of rows stored in data pages. It is optimized for the improved and fast warehouse query processing. In ordinary index rows are stored in disk pages, but with column store index columns are stored in a separate set of the disk pages, that's why it is faster than ordinary index. It is mostly optimized for queries which are used aggregation, warehouse processing and BI application.

Implementation :
As per workaround created for the COLUMNSTORE index I found it better perform for the aggregate data. Let us go step by step and see the performance of normal indices against column store index with normal query which have where clause and another one query which perform the aggregation.
-- Creating table
CREATE TABLE ObjectDetails
(
ObjectId BIGINT,
ObjectName VARCHAR(200),
ObjectType VARCHAR(50),
CreateDate DATETIME
)

-- Inserting sample records
INSERT ObjectDetails
SELECT a.object_id,a.name,a.type_desc,a.create_date
FROM sys.objects a
CROSS JOIN sys.objects b

/*
(1428025 row(s) affected)
*/

-- Creating clustered index
CREATE CLUSTERED INDEX IX_ObjectId ON ObjectDetails(ObjectId)
-- Creating normal nonclustered index
CREATE NONCLUSTERED INDEX IX_ObjectType ON ObjectDetails(ObjectType)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX  IX_CS_ObjectType 
ON ObjectDetails(ObjectType)
GO
You can get the details of the columnstore index using system tables.
SELECT 
object_name(object_id) as TableName,
* 
FROM sys.column_store_index_stats 

SELECT
*
FROM sys.column_store_segments
GO

Now we will run the normal query which having WHERE condition applied and the COLUMNSTORE index created on that column which used in where clause.
-- Getting data from table using both of the index hints
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
ObjectId ,
ObjectName ,
CreateDate 
FROM ObjectDetails
WITH (INDEX (IX_ObjectType))
WHERE ObjectType = 'SYSTEM_TABLE'

SELECT 
ObjectId ,
ObjectName ,
CreateDate 
FROM ObjectDetails
WITH (INDEX (IX_CS_ObjectType))
WHERE ObjectType = 'SYSTEM_TABLE'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
/* Output :
(80065 row(s) affected)
Table 'ObjectDetails'. Scan count 1, logical reads 245571, 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 = 265 ms, elapsed time = 829 ms.


(80065 row(s) affected)
Table 'ObjectDetails'. Scan count 2, logical reads 245865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 529 ms, elapsed time = 960 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/

Now we will run the query which performs aggregation with GROUP BY applied and the index COLUMNSTORE created on a column which used in group by. You can see COLUMNSTORE index used 'worktable' which is used to handle intermediate result.
-- Getting aggregate data using both of the index hints
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT ObjectType,COUNT(1)
FROM ObjectDetails
WITH (INDEX (IX_ObjectType))
GROUP BY  ObjectType

SELECT ObjectType,COUNT(1)
FROM ObjectDetails
WITH (INDEX (IX_CS_ObjectType))
GROUP BY  ObjectType

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
/* Output :
(17 row(s) affected)
Table 'ObjectDetails'. Scan count 3, logical reads 7412, 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 = 656 ms, elapsed time = 350 ms.


(17 row(s) affected)
Table 'ObjectDetails'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 0 ms, elapsed time = 25 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/

Let us compare execution plan as well for both of the queries.
SELECT ObjectType,COUNT(1)
FROM ObjectDetails
WITH (INDEX (IX_ObjectType))
GROUP BY  ObjectType

SELECT ObjectType,COUNT(1)
FROM ObjectDetails
WITH (INDEX (IX_CS_ObjectType))
GROUP BY  ObjectType


(Click on image to enlarge)

You can see from both of the query performance outputs, COLUMNSTORE index perform well for the aggregation. There is some limitation I faced during workaround like it cannot be created as CLUSTERED even can not be created as COVERING index, the messages you can see below.
--  Creating clustered COLUMNSTORE index 
CREATE CLUSTERED COLUMNSTORE INDEX  IX_CS_ObjectType_Clust ON ObjectDetails(ObjectType)
/*
Msg 35335, Level 15, State 1, Line 1
CREATE INDEX statement failed because specifying key list is not allowed when creating a clustered columnstore index. Create the clustered columnstore index without specifying key list.
*/
--  Creating nonclustered covering COLUMNSTORE index 
CREATE NONCLUSTERED COLUMNSTORE INDEX  IX_CS_ObjectType_Cov ON ObjectDetails(ObjectType) INCLUDE (ObjectId ,ObjectName ,CreateDate )
/*
Msg 35311, Level 15, State 1, Line 1
CREATE INDEX statement failed because a columnstore index cannot have included columns. Create the columnstore index on the desired columns without specifying any included columns.
*/
You can share your experiment with COLUMNSTORE index! Enjoy SQL Server 11 CTP3.