SQL Server 2014 Performance Tuning – Columnstore Indexes

SQL Server 2014 updatable in-memory columnstore (abbreviated to xVelocity where appropriate) index is another compelling performance-related feature of SQL Server 2014. Columnstore indexes enable you to deliver predictable performance for large data volumes. Columnstore indexes were first introduced with SQL Server 2012, to significantly improve performance of data warehouse workloads. For certain data warehousing analytical queries, you can achieve up to 10x performance improvements by using in-memory columnstore indexes.

The in-memory columnstore index feature is one of the most important scalability and performance enhancements of SQL Server 2012. However, SQL Server 2012 implementation of in-memory columnstore indexes are not updatable, which means that you cannot perform DML operations on tables once the in-memory columnstore index is created on them. Therefore, underlying table in which you are creating the columnstore index had to be read-only. Moreover, to update data in the underlying table, you must first drop or disable the columnstore index, and then enable or recreate the columnstore index once the data in the underlying table is updated. SQL Server 2014 removes this restriction and introduced updatable in-memory columnstore indexes.

Unlike the SQL Server 2012 Database Engine, which only supports nonclustered columnstore indexes, SQL Server 2014 Database Engine supports both clustered and nonclustered columnstore indexes. Both these types of SQL Server 2014 columnstore indexes use the same in-memory technology, but have different purpose. SQL Server 2014 clustered columnstore indexes are updateable, meaning you can perform DML operations on the underlying table without having to disable or remove the clustered columnstore index. In this third part of the three part article series, we will take a look at SQL Server 2014 columnstore indexes.

Architecture of Columnstore Indexes

Unlike traditional B-trees indexes, where data is stored and grouped in a row-based fashion, columnstore indexes group and store data for each column in a separate set of disk pages. For example, consider the following table with 8 columns:

When we create the traditional B-tree index on this table, SQL Server stores multiple table rows per index page, as illustrated figure below:

When we create the columnstore index on this table, SQL Server stores the data for each column in a separate index page, as illustrated in figure below:

A Columnstore index does not physically store columns in a sorted order. Instead, it is based on VertiPaq compression technology that allows large amounts of data to be compressed in-memory. This highly compressed in-memory store significantly improves query execution time by improving the buffer pool usage, while reducing the total disk I/O and CPU usage, because only the column-based data pages needed to solve the query are fletch from disk and moved in memory.

Creating and managing columnstore indexes

You use CREATE CLUSTERED COLUMNSTORE INDEX statement to create clustered columnstore index, and CREATE COLUMNSTORE INDEX statement to create nonclustered columnstore index on the table. To create clustered columnstore index, you use:

CREATE CLUSTERED COLUMNSTORE INDEX index_name
ON [database_name].[schema_name].[table_name]
  [ WITH (     <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
partition_scheme_name ( column_name )
        | filegroup_name
        | "default"
        }
    ]
[ ; ]

To create nonclustered columnstore index, you use:

CREATE [NONCLUSTERED] COLUMNSTORE INDEX index_name
ON [database_name].[schema_name].[table_name]
        ( column  [ ,...n ] )
    [ WITH (   <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
partition_scheme_name ( column_name )
        | filegroup_name
        | "default"
        }
    ]
[ ; ]

When creating columnstore indexes, you must consider the following:

• The columnstore index feature is only available in SQL Server 2014 Enterprise, Evaluation and Developer edition.
• Columnstore indexes cannot be combined with following SQL Server features: page and row compression, replication, filestream, change tracking, and CDC.
• Clustered columnstore indexes must include all columns of the table.
• You cannot create column store indexes on index or indexed view.
• Columnstore index cannot have more than 1024 columns.
• Columnstore indexes cannot include sparse column, unique constraints, primary key constraints, or foreign key constraints.
• The columnstore indexes cannot include columns with following data types: ntext, text, image, varchar(max), nvarchar(max), varbinary(max), rowversion (and timestamp), sql_variant, CLR types (hierarchyid and spatial types), xml.
• Avoid creating columnstore indexes on tables that are frequently updated or that require small lookup queries. They are only suitable for read-mostly, read-intensive, large database tables.

You use ALTER INDEX statement to modify a columnstore index. You can use ALTER INDEX…REBULD with COLUMNSTORE_ARCHIVE data compression option to further compress the columnstore index, which is suitable for data archival situations. You use DROP INDEX statement to delete a columnstore index.

You can also use SQL Server 2014 Management Studio to create columnstore indexes in the same way you use it to manage normal table indexes. For example, to create a new clustered columnstore index, in Object Explorer, expand table, right click “Indexes” folder. Next, choose New Index, and then choose the new Clustered columnstore index.

]]>