Sliding Window Partitioning And Columnstore Indexes

Dmitri Korotkevitch walks through setting up sliding window partitioning on tables with columnstore indexes:

The biggest difference resides in partition function split and merge behavior. With B-Tree indexes, you can split and merge non-empty partitions. SQL Server would split or merge the data automatically, granted with the schema-modification (Sch-M) table lock held in place. Other sessions would be unable to access the table but at least split and merge would work.

This is not the case with columnstore indexes where you would get the error when you try to split or merge non-empty partitions. There are several reasons for this limitation. Without diving very deep into columnstore index internals, I could say that the only option of doing split of merge internally is rebuilding columnstore index on affected partitions. Fortunately, you can split and merge empty columnstore partitions, which allow you to workaround the limitation and also implement Sliding Window pattern and use partitioning to purge the data.

With SQL Server 2017, the logic gets a little simpler, as you can directly truncate partitions instead of shuffling them off to a separate table.

Related Posts

Triggering KB 4462481

Joe Obbish shows how you can recreate the error described in KB 4462481: Consider a query execution that meets all of the following criteria: 1. A parallel INSERT INTO… SELECT into a columnstore table is performed 2. The SELECT part of the query contains a batch mode hash join 3. The query can’t immediate get […]

Read More

Preventing Issues With Columnstore Indexes

Kevin Chant has a post covering issues in his experience with the creation of columnstore indexes: Error due to online statement It’s a very common error caused usually by somebody copying a rowstore “Create Index” command. The “online=on” option you use with rowstore indexes does not work with creating columnstore indexes yet. For the record the […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930