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.




Array

No comments yet... Be the first to leave a reply!