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.