Columnstore Partition Management–Dealing With Non-Empty Partitions

Dmitri Korotkevitch shows a way of dealing with non-empty partitions on columnstore indexes:

The common, by the book approach recommends dropping columnstore index, splitting or merging partitions and recreating the index afterwards. As you can imagine, it would lead to extremely inefficient process with huge amount of unnecessary overhead on large tables. After all, you have to drop and recreate columnstore index, converting table to Heap, while just subset of the partitions needs to be rebuilt. Fortunately, you can minimize the overhead with simple workaround:

  1. Switch partition(s) to split or merge to the separate staging table

  2. Split or merge partition(s) in the main table. You can do that because partitions will be empty after the previous step

  3. Drop columnstore index in the staging table, split/merge partition(s) there and recreate the index afterwards

  4. Switch partition(s) back from staging to the main table.

Read on for a detailed walkthrough of these steps.

Related Posts

Batch Mode Normalization

Paul White digs into batch mode normalization and its consequences for performance: I mentioned in the introduction that not all eight-byte data types can fit in 64 bits. This fact is important because many columnstore and batch mode performance optimizations only work with data 64 bits in size. Aggregate pushdown is one of those things. There are […]

Read More

Clustered Columnstore and Azure SQL DB

Arun Sirpal takes us through online clustered columnstore index creation in Azure SQL Database: What tier do you need to create one of these things? Let’s see. CREATE CLUSTERED  COLUMNSTORE INDEX cciSales ON [SalesLT].[ProductModelProductDescription] WITH ( ONLINE = ON ) But I get this message, Msg 40536, Level 16, State 32, Line 1‘COLUMNSTORE’ is not […]

Read More

Categories

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