Available in SQL 2012 & 2014 Enterprise Edition
ColumnStore Indexes were introduced in SQL 2012 and are designed to improve performance in data warehouses. These indexes are good for batch processing and read only queries that use large data sets. Tables that are bulk loaded are good candidates. ColumnStore Indexes stores data in a columnar fashion and uses compression to reduce the amount of IO on disks when used. Each column of data is separated into its own segment. The segments are organized into a row group, which can contain over one million rows. If a table exceeds that amount, SQL will create more row groups and will break the column segments across them. The tuple mover checks every 5 minutes and performs the move if conditions are right.
When saving the index to disk, SQL Server compresses the data in the segments. As a result ColumnStore Indexes can achieve higher compression rates than traditional indexes. Higher compression rates = better performance, since compressed data is a smaller and faster in-memory. According to MS documentation: “Use the ColumnStore Indexes to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.”
As always, your mileage may vary based on values in each column. And keep in mind that more data held in memory also reduces disk IO. So, make sure you have plenty of memory…add if needed. The advantages can be mitigated if you have queries that seek specific data or is updated a lot.
Below is a list of limitations of ColumnStore Indexes. I have highlighted in green how it has been enhanced:
How to create a basic ColumnStore Index:
To create a ColumnStore Index go to Database – tables – indexes – new index – select your type of columnstore index you want to create.
In the new index dialog add your index name and the columns you want.
Or, in TSQL, here is code to do the same:
CREATE TABLE RunningRoutes
(RouteID [int] NOT NULL,
RaceKey [int] NOT NULL,
RaceDateKey [int] NOT NULL,
MileageKey [int] NOT NULL)
CREATE CLUSTERED INDEX cl_simple ON RunningRoutes (RouteID)
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_runningroutes
(RaceKey, RaceDateKey, MileageKey)
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!