24 June 2020

31736 views

31 0

Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines

In the second article of this series, Edward Pollack demonstrates some ways to design and populate a columnstore index to get even better performance.

The series so far:

A discussion of how columnstore indexes work is important for making the best use of them, but a practical, hands-on discussion of reality and how they are used in production environments is key to making the most of them. There are many ways that data load processes can be tweaked to dramatically improve query performance and increase scalability.

The following is a list of what I consider to be the most significant tips, tricks, and best practices for designing, loading data into, and querying columnstore indexes. As always, test all changes thoroughly before implementing them.

Columnstore indexes are generally used in conjunction with big data, and having to restructure it after-the-fact can be painfully slow. Careful design can allow a table with a columnstore index to stand on its own for a long time without the need for significant architectural changes.

Column Order is #1

Rowgroup elimination is the most significant optimization provided by a columnstore index after you account for compression. It allows large swaths of a table to be skipped when reading data, which ultimately facilitates a columnstore index growing to a massive size without the latency that eventually burdens a classic B-tree index.

Each rowgroup contains a segment for each column in the table. Metadata is stored for the segment, of which the most significant values are the row count, minimum column value, and maximum column value. For simplicity, this is akin to having MIN(), MAX(), and COUNT(*) available automatically for all segments in all rowgroups in the table.

Unlike a classic clustered B-tree index, a columnstore index has no natural concept of order. When rows are inserted into the index, they are added in the order that you insert them. If rows are inserted from ten years ago, then they will be added to the most recently available rowgroups. If rows are then inserted from today, they will get added on next. It is up to you as the architect of the table to understand what the most important column is to order by and design schema around that column.

For most OLAP tables, the time dimension will be the one that is filtered, ordered, and aggregated by. As a result, optimal rowgroup elimination requires ordering data insertion by the time dimension and maintaining that convention for the life of the columnstore index.

A basic view of segment metadata can be viewed for the date column of our columnstore index as follows:

SELECT

tables.name AS table_name,

indexes.name AS index_name,

columns.name AS column_name,

partitions.partition_number,

column_store_segments.segment_id,

column_store_segments.min_data_id,

column_store_segments.max_data_id,

column_store_segments.row_count

FROM sys.column_store_segments

INNER JOIN sys.partitions

ON column_store_segments.hobt_id = partitions.hobt_id

INNER JOIN sys.indexes

ON indexes.index_id = partitions.index_id

AND indexes.object_id = partitions.object_id

INNER JOIN sys.tables

ON tables.object_id = indexes.object_id

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

AND column_store_segments.column_id =

columns.column_id

WHERE tables.name = 'fact_order_BIG_CCI'

AND columns.name = 'Order Date Key'

ORDER BY tables.name, columns.name,

column_store_segments.segment_id;

The results provide segment metadata for the fact_order_BIG_CCI table and the [Order Date Key] column:

Note the columns min_data_id and max_data_id. These ID values link to dictionaries within SQL Server that store the actual minimum and maximum values. When queried, the filter values are converted to IDs and compared to the minimum and maximum values shown here. If a segment contains no values needed to satisfy a query, it is skipped. If a segment contains at least one value, then it will be included in the execution plan.

The image above highlights a BIG problem here: the minimum and maximum data ID values are the same for all but the last segment. This indicates that when the columnstore index was created, the data was not ordered by the date key. As a result, all segments will need to be read for any query against the columnstore index based on the date.

This is a common oversight, but one that is easy to correct. Note that a clustered columnstore index does not have any options that allow for order to be specified. It is up to the user to make this determination and implement it by following a process similar to this:

Create a new table.
Create a clustered index on the column that the table should be ordered by.
Insert data in the order of the most significant dimension (typically date/time).
Create the clustered columnstore index and drop the clustered B-Tree as part of its creation.
When executing data loads, continue to insert data in the same order.

This process will create a columnstore index that is ordered solely by its most critical column and continue to maintain that order indefinitely. Consider this order to be analogous to the key columns of a classic clustered index. This may seem to be a very roundabout process, but it works effectively. Once created, the columnstore index can be inserted into using whatever key order was originally defined.

The lack of order in fact_order_BIG_CCI can be illustrated with a simple query:

SET STATISTICS IO ON;

SELECT

SUM([Quantity])

FROM dbo.fact_order_BIG_CCI

WHERE [Order Date Key] >= '2016/01/01'

AND [Order Date Key] < '2016/02/01';

The results return relatively quickly, but the IO details tell us something is not quite right here:

Note that 22 segments were read, and one was skipped, despite the query only looking for a single month of data. Realistically, with many years of data in this table, no more than a handful of segments should need to be read in order to satisfy such a narrow query. As long as the date values searched for appear in a limited set of rowgroups, then the rest can be automatically ignored.

With this mistake identified, let’s drop fact_order_BIG_CCI and recreate it by following this set of steps instead:

DROP TABLE dbo.fact_order_BIG_CCI;

CREATE TABLE dbo.fact_order_BIG_CCI (

[Order Key] [bigint] NOT NULL,

[City Key] [int] NOT NULL,

[Customer Key] [int] NOT NULL,

[Stock Item Key] [int] NOT NULL,

[Order Date Key] [date] NOT NULL,

[Picked Date Key] [date] NULL,

[Salesperson Key] [int] NOT NULL,

[Picker Key] [int] NULL,

[WWI Order ID] [int] NOT NULL,

[WWI Backorder ID] [int] NULL,

[Description] [nvarchar](100) NOT NULL,

[Package] [nvarchar](50) NOT NULL,

[Quantity] [int] NOT NULL,

[Unit Price] [decimal](18, 2) NOT NULL,

[Tax Rate] [decimal](18, 3) NOT NULL,

[Total Excluding Tax] [decimal](18, 2) NOT NULL,

[Tax Amount] [decimal](18, 2) NOT NULL,

[Total Including Tax] [decimal](18, 2) NOT NULL,

[Lineage Key] [int] NOT NULL);

CREATE CLUSTERED INDEX CCI_fact_order_BIG_CCI

ON dbo.fact_order_BIG_CCI ([Order Date Key]);

INSERT INTO dbo.fact_order_BIG_CCI

SELECT

[Order Key] + (250000 * ([Day Number] +

([Calendar Month Number] * 31))) AS [Order Key]

,[City Key]

,[Customer Key]

,[Stock Item Key]

,[Order Date Key]

,[Picked Date Key]

,[Salesperson Key]

,[Picker Key]

,[WWI Order ID]

,[WWI Backorder ID]

,[Description]

,[Package]

,[Quantity]

,[Unit Price]

,[Tax Rate]

,[Total Excluding Tax]

,[Tax Amount]

,[Total Including Tax]

,[Lineage Key]

FROM Fact.[Order]

CROSS JOIN

Dimension.Date

WHERE Date.Date <= '2013-04-10'

ORDER BY [Order].[Order Date Key];

CREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_order_BIG_CCI

ON dbo.fact_order_BIG_CCI WITH (MAXDOP = 1, DROP_EXISTING = ON);

Note that only three changes have been made to this code:

A clustered B-tree index is created prior to any data being written to it.
The INSERT query includes an ORDER BY so that data is ordered by [Order Date Key] as it is added to the columnstore index.
The clustered B-tree index is swapped for the columnstore index at the end of the process.

When complete, the resulting table will contain the same data as it did at the start of this article, but physically ordered to match what makes sense for the underlying data set. This can be verified by rerunning the following query:

SELECT

SUM([Quantity])

FROM dbo.fact_order_BIG_CCI

WHERE [Order Date Key] >= '2016-01-01'

AND [Order Date Key] < '2016-02-01';

The results show significantly improved performance:

This time, only one segment was read, and 22 were skipped. Reads are a fraction of what they were earlier. This is a significant improvement and allows us to make the most out of a columnstore index.

The takeaway of this experiment is that order matters in a columnstore index. When building a columnstore index, ensure that order is created and maintained for whatever column will be the most common filter by:

Order the data in the initial data load. This can be accomplished by either:
1. Creating a clustered B-tree index on the ordering column, populating all initial data, and then swapping it with a columnstore index.
2. Create the columnstore index first, and then insert data in the correct order of the ordering column.
Insert new data into the columnstore index using the same order every time.

Typically, the correct data order will be ascending, but do consider this detail when creating a columnstore index. If for any reason descending would make sense, be sure to design index creation and data insertion to match that order. The goal is to ensure that as few rowgroups need to be scanned as possible when executing an analytic query. When data is inserted out-of-order, the result will be that more rowgroups need to be scanned in order to fulfill that query. This may be viewed as a form of fragmentation, even though it does not fit the standard definition of index fragmentation.

Partitioning & Clustered Columnstore Indexes

Table partitioning is a natural fit for a large columnstore index. For a table that can contain row counts in the billions, it may become cumbersome to maintain all of the data in a single structure, especially if reporting needs rarely access older data.

A classic OLAP table will have both newer and older data. If common reporting queries only access a recent day, month, quarter, or year, then maintaining the older data in the same place may be unnecessary. Equally important is the fact that in an OLAP data store, older data typically does not change. If it does, it’s usually the result of software releases or other one-off operations that fall within the bounds of our world.

Table partitioning places data into multiple filegroups within a database. The filegroups can then be stored in different data files in whatever storage locations are convenient. This paradigm provides several benefits:

Partition Elimination: Similar to rowgroup elimination, partition elimination allows partitions with unneeded data to be skipped. This can further improve performance on a large columnstore index.
Faster Migrations: If there is a need to migrate a database to a new server or SQL Server version, then older partitions can be backed up and copied to the new data source ahead of the migration. This reduces the downtime incurred by the migration as only active data needs to be migrated during the maintenance/outage window.

Similarly, partition switching can allow for data to be moved between tables exceptionally quickly.

Partitioned Database Maintenance: Common tasks such as backups and index maintenance can be targeted at specific partitions that contain active data. Older partitions that are static and no longer updated may be skipped.
No Code Changes: Music to the ears of any developer: Table partitioning is a database feature that is invisible to the consumers of a table’s data. Therefore, the code needed to retrieve data before and after partitioning is added will be the same.
Partition Column = Columnstore Order Column: The column that is used to organize the columnstore index will be the same column used in the partition function, making for an easy and consistent solution.

The fundamental steps to create a table with partitioning are as follows:

Create filegroups for each partition based on the columnstore index ordering column.
Create database files within each filegroup that will contain the data for each partition within the table.
Create a partition function that determines how the data will be split based on the ordering/key column.
Create a partition schema that binds the partition function to a set of filegroups.
Create the table on the partition scheme defined above.
Proceed with table population and usage as usual.

The example provided in this article can be recreated using table partitioning, though it is important to note that this is only one way to do this. There are many ways to implement partitioning, and this is not intended to be an article about partitioning, but instead introduce the idea that columnstore indexes and partitioning can be used together to continue to improve OLAP query performance.

Create New Filegroups and Files

Partitioned data can be segregated into different file groups and files. If desired, then a script similar to this would take care of the task:

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2013_fg;

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2014_fg;

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2015_fg;

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2016_fg;

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2017_fg;