Rebuilding large columnstore indexed tables - am I doing it right?

Question

We are planning to move the database from SQL 2014 ENT into 2016 ENT. We realized the data currently in columnstore table are not aligned in time when they are loaded, so we need to re-align the data.

Stats:

• 3 big tables (columnstore indexed)
• the big tables has 60+ billion rows
• 4 data files (round robin)
• partitioned by monthly
• SQL Server 2014 ENT, 128 GB RAM
• High Performance VM platform, 32 CPUs

File groups/datafiles:

• PRIMARY
• DATA (4 data files, each data file on 2TB disk)
• LOG

Parition details:

• Monthly partition function
• All paritiona scheme on DATA FG

Table sizes (#rows):

• T1 34,807,580,311
• T2 16,458,306,369
• T3 10,170,792,290

What I plan to do:

• drop the columnstore index, create row-store index (this is to align the data in logtime)
• then drop the row store index, convert the table into column store index (better compression and queries later)

I am concern this might took extremely long time to rebuild. Am I taking the right approach?

DDL:

CREATE TABLE [dbo].[T1](
[Id] [int] NOT NULL,
[C1] [smallint] NOT NULL,
[C2] [int] NOT NULL,
[C3] [int] NOT NULL,
[C4] [int] NOT NULL,
[C5] [datetime2](0) NOT NULL,
[C6] [real] NULL,
[C7] [real] NULL,
[C8] [real] NULL,
[C9] [real] NULL,
[C10] [real] NULL,
[C11] [datetime2](3) NULL,
[C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T1_ColumnStoreIndex] ON [T1]

CREATE TABLE [dbo].[T2](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [int] NULL,
    [C9] [int] NULL,
    [C10] [tinyint] NULL,
    [C11] [tinyint] NULL,
    [C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T2_ColumnStoreIndex] ON [T2]

CREATE TABLE [dbo].[T3](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [real] NULL,
    [C9] [real] NULL,
    [C10] [real] NULL,
    [C11] [real] NULL,
    [C12] [real] NULL,
    [C13] [real] NULL,
    [C14] [varchar](50) NULL,
    [C15] [datetime] NULL,
    [C16] [int] NULL,
    [C17] [int] NULL,
    [C18] [float] NULL,
    [C19] [tinyint] NULL,
    [C20] [tinyint] NULL,
    [C21] [datetime2](3) NULL,
    [C22] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T3_ColumnStoreIndex] ON [T3]

This may not be Q&A but please I believe I can gain valuable inputs here.

Answer 1

What I plan to do: •drop the columnstore index, create row-store index (this is to align the data in logtime) •then drop the row store index, convert the table into column store index (better compression and queries later)

You can use CREATE CLUSTERED INDEX...WITH (DROP_EXISTING=ON) to change the existing columnstore clustered index to a b-tree and then CREATE CLUSTERED COLUMNSTORE INDEX...WITH (DROP_EXISTING=ON) to change back to columnstore. This will eliminate the drop index step that changes the columnstore to a heap.

I still expect this will take quite some time with 60 billions rows. Although source data might not have been loaded in C6 order, data overall are typically loaded in roughly chronological order so there will still be a temporal relationship for data in the same proximity. I suggest you review the min and max values in sys.column_store_segments to see if this effort is justified, considering that segment elimination is done after partition elimination. The performance benefit might not be a great as you think.

Answer 2

The details I'm providing below are based on back-of-the-envelope calculations designed to explore the duration of the operations you are considering, and should not be taken as gospel since it is very difficult to know exactly how your system behaves.

T1 is 88 bytes per row for data, with all nullable columns non-null, excluding row overhead. At 20 billion rows (you haven't said if each table has 60 billion rows, or if that is the total for all three), that makes the table 1.678 TB without any additional indexes or row/page overhead, and without compression.

If you have a very fast local PCIe NVMe SSD, running at 3GB/sec, that's still around 10 minutes just to read the data. This is hopelessly optimistic, not least because it assumes sequential access, which is never going to happen when re-organizing or rebuilding an index. A more reasonable transfer rate for random 64KB transfers done on a local PCIe NVMe SSD would be in the 300MB/sec range. At that speed, simply reading a 1.678 TB table will take roughly 93 minutes. If you can afford it, I'd rebuild those indexes, as Dan Guzman suggests in his answer, using WITH (DROP_EXISTING = ON), however I'd also define the new indexes to be created on a newly created filegroup, that lives on a separate disk subsystem. With very fast disk subsystems, you're still looking at a minimum of a number of hours to reorganize a single table. If storage is not on local SSD, the time taken will be multiples of the numbers I've provided above.

Depending on the maximum duration of your outage window, I'd consider doing the rebuild partition-by-partition one at a time over several outage windows.

Assuming your tables have nonclustered columnstore indexes on a single int column, and assuming 50% compression, 20 billion rows still consumes at least 38 GB per index, which is certainly non-trivial. You haven't provided the details of your columnstore configuration; if you do that would help get a more accurate estimate.