5

I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management.

Background

The target database is a DW database hosting 3 big tables in clustered columnstore index (CCIX). The biggest table called Analog. It holds ~37 billion rows and ~600 GB of high compressed data. Based on our initial estimate using smaller table, a 150 GB CCIX table could expand to 5 TB, so we provisioned additional 29 TB for this rebuild.

  • SQL Server Enterprise 2016
  • Recovery mode = Simple

Why do we have to do this?

In the first version of our ETL service, the data are not consolidated and sorted first before they are loaded to CCIX. This results to non-optimal use of CCIX as many row segments are not fully filled before they are compressed. An optimal segment must have compressed 1,0485,76 rows and must be sorted in Time so time-based queries can get the most row-group elimination in sql server and less segments to process. We’re learn more CCIX as we go and as more documentatons are available.

Excerpt of my alignment is here https://drive.google.com/file/d/0BzGLNskaj70UQUtZYW9CZF9iUUk/view?usp=sharing

The new ETL guarantees that data are consolidated and sorted in time before they are loaded to CCIX. So future loads will be sorted, we are concern here with existing data loaded during First Load.

For full case description, please read here. https://drive.google.com/open?id=0BzGLNskaj70URmZURlVDWVNYd2M

This is our second attempt and I can see the log file is still piling up despite the new script having partition-based index rebuild. We need to keep this in check.

My questions are:

  1. I already executed the 2nd attempt (in progress), is it possible to check which partitions are already processed and sorted? I have OFFLINE = ON in the rebuild script.

  2. How can we manage the size of transaction log while a partion-based index rebuild is in progress? We need to keep this in check and regularly truncated every partition if possible.

  3. The index rebuild failed initially due to insufficient space in log file. We added a new log file and use a separate disk. But how we make make sure this wont happen again in this second run? Will the partition-based rebuild sufficient to guarantee we can rebuild successfully?

Appreciate your help.

Rebuild scripts by partition (2nd attempt) – IN PROGRESS

Elapsed: 17 hrs, 34 mins

--create a new clustered row index (CRI)
CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex] 
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
) 
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
) 
ON [AnalogMonthlyPScheme]([LogTime])
GO

enter image description here

1

I would process this one partition at a time. At a high level:

  1. Create a new empty target table with a partitioned clustered rowstore index
  2. Create a working table with an unpartitioned clustered columnstore index
  3. SWITCH partition 1 from the source table to the working table
  4. Convert the working table to a rowstore clustered index using DROP_EXISTING
  5. Add a CHECK constraint to the working table that covers the range of values in partition 1
  6. SWITCH the working table into partition 1 of the target table
  7. Drop the empty working table
  8. Repeat from step 2 for the next partition
  9. Once all partitions are processed, drop the original table, and rename the new

This makes maximum use of minimally-logged operations, minimizes workspace, and allows the transaction log to be cleared between partitions if necessary.

Demo

Partitioning

CREATE PARTITION FUNCTION [AnalogMonthlyP] (datetime2(0))
AS RANGE RIGHT FOR VALUES(
N'2014-09-01T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-11-01T00:00:00.000',
N'2014-12-01T00:00:00.000', N'2015-01-01T00:00:00.000', N'2015-02-01T00:00:00.000',
N'2015-03-01T00:00:00.000', N'2015-04-01T00:00:00.000', N'2015-05-01T00:00:00.000',
N'2015-06-01T00:00:00.000', N'2015-07-01T00:00:00.000', N'2015-08-01T00:00:00.000',
N'2015-09-01T00:00:00.000', N'2015-10-01T00:00:00.000', N'2015-11-01T00:00:00.000',
N'2015-12-01T00:00:00.000', N'2016-01-01T00:00:00.000', N'2016-02-01T00:00:00.000',
N'2016-03-01T00:00:00.000', N'2016-04-01T00:00:00.000', N'2016-05-01T00:00:00.000',
N'2016-06-01T00:00:00.000', N'2016-07-01T00:00:00.000', N'2016-08-01T00:00:00.000',
N'2016-09-01T00:00:00.000', N'2016-10-01T00:00:00.000', N'2016-11-01T00:00:00.000',
N'2016-12-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2017-02-01T00:00:00.000',
N'2017-03-01T00:00:00.000', N'2017-04-01T00:00:00.000', N'2017-05-01T00:00:00.000',
N'2017-06-01T00:00:00.000', N'2017-07-01T00:00:00.000', N'2017-08-01T00:00:00.000',
N'2017-09-01T00:00:00.000', N'2017-10-01T00:00:00.000', N'2017-11-01T00:00:00.000',
N'2017-12-01T00:00:00.000', N'2018-01-01T00:00:00.000', N'2018-02-01T00:00:00.000',
N'2018-03-01T00:00:00.000', N'2018-04-01T00:00:00.000', N'2018-05-01T00:00:00.000',
N'2018-06-01T00:00:00.000', N'2018-07-01T00:00:00.000', N'2018-08-01T00:00:00.000',
N'2018-09-01T00:00:00.000', N'2018-10-01T00:00:00.000', N'2018-11-01T00:00:00.000',
N'2018-12-01T00:00:00.000');

CREATE PARTITION SCHEME [AnalogMonthlyPScheme]
AS PARTITION [AnalogMonthlyP]
ALL TO ([PRIMARY]);

Numbers table setup (if required)

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT TOP (10000000) 
    n = IDENTITY(int, 1, 1)
INTO   dbo.Numbers
FROM   Ten T10,
       Ten T100,
       Ten T1000,
       Ten T10000,
       Ten T100000,
       Ten T1000000,
       Ten T10000000;

ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_dbo_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

Source table and data

-- Source table currently partitioned clustered columnstore
CREATE TABLE [dbo].[Analog]
(
    LogTime datetime2(0) NOT NULL,
    CTDID integer NOT NULL,
    WindFarmId integer NOT NULL,
    StationId integer NOT NULL,

    INDEX [Analog_ColumnStoreIndex]
        CLUSTERED COLUMNSTORE
)
ON [AnalogMonthlyPScheme](LogTime);

-- Some test data
INSERT dbo.Analog WITH (TABLOCKX)
    (LogTime, CTDID, WindFarmId, StationId)
SELECT
    DATEADD(SECOND, N.n, '20140801'),
    CHECKSUM(NEWID()),
    CHECKSUM(NEWID()),
    CHECKSUM(NEWID())
FROM dbo.Numbers AS N -- ten million rows
OPTION (MAXDOP 1);

Target table and work table

-- The final table we want - partitioned rowstore clustered index
CREATE TABLE [dbo].[AnalogNew]
(
    LogTime datetime2(0) NOT NULL,
    CTDID integer NOT NULL,
    WindFarmId integer NOT NULL,
    StationId integer NOT NULL,

    INDEX CCIX_Analog
        CLUSTERED (LogTime, CTDID, WindFarmId, StationId)
)
ON [AnalogMonthlyPScheme](LogTime);

-- Working table
CREATE TABLE dbo.AnalogSwitch
(
    LogTime datetime2(0) NOT NULL,
    CTDID integer NOT NULL,
    WindFarmId integer NOT NULL,
    StationId integer NOT NULL,

    INDEX CCI CLUSTERED COLUMNSTORE
);

Partition 1

-- Process the first partition
ALTER TABLE dbo.Analog 
SWITCH PARTITION 1 
TO dbo.AnalogSwitch;

-- Optional, may help parallel plan
CREATE STATISTICS s ON dbo.AnalogSwitch (LogTime);

-- Convert to rowstore clustered index
CREATE CLUSTERED INDEX CCI
ON dbo.AnalogSwitch (LogTime, CTDID, WindFarmId, StationId)
WITH (DROP_EXISTING = ON);

-- Specify data range
ALTER TABLE dbo.AnalogSwitch
    ADD CONSTRAINT CK 
    CHECK (LogTime >= '20140801' AND LogTime < '20140901');

-- Move to target table
ALTER TABLE dbo.AnalogSwitch 
SWITCH TO dbo.AnalogNew 
PARTITION 1;

-- Recreate switch table
DROP TABLE dbo.AnalogSwitch;

CREATE TABLE dbo.AnalogSwitch
(
    LogTime datetime2(0) NOT NULL,
    CTDID integer NOT NULL,
    WindFarmId integer NOT NULL,
    StationId integer NOT NULL,

    INDEX CCI CLUSTERED COLUMNSTORE
);

Partition 2

-- Process the second partition
ALTER TABLE dbo.Analog 
SWITCH PARTITION 2
TO dbo.AnalogSwitch;

-- Optional, may help parallel plan
CREATE STATISTICS s ON dbo.AnalogSwitch (LogTime);

-- Convert to rowstore clustered index
CREATE CLUSTERED INDEX CCI
ON dbo.AnalogSwitch (LogTime, CTDID, WindFarmId, StationId)
WITH (DROP_EXISTING = ON);

-- Specify data range
ALTER TABLE dbo.AnalogSwitch
    ADD CONSTRAINT CK 
    CHECK (LogTime >= '20140901' AND LogTime < '20141001');

-- Move to target table
ALTER TABLE dbo.AnalogSwitch 
SWITCH TO dbo.AnalogNew 
PARTITION 2;

-- Recreate switch table
DROP TABLE dbo.AnalogSwitch;

CREATE TABLE dbo.AnalogSwitch
(
    LogTime datetime2(0) NOT NULL,
    CTDID integer NOT NULL,
    WindFarmId integer NOT NULL,
    StationId integer NOT NULL,

    INDEX CCI CLUSTERED COLUMNSTORE
);

...and so on. This is fairly straightforward to script.

2

The most important advice I can give you is to test against a smaller table first. Don't just dive into converting a 37 billion row table. You can refine your method against a smaller table and use that to get an estimate on converting the large table. Also recovering from mistakes is far less costly (no more 24 hour rollback operations). You may also be able to use the smaller table to test query performance to verify that the conversion that you're doing is worth the effort.

The second most important advice is to get someone to check out your storage configuration. If you recently added a lot of storage it may not have been done in a good way to maximize throughput.

To be honest, it's not clear to me why you're seeing so much transaction log activity. Converting the CCI to a rowstore should be a minimally logged operation since you're using a recovery model of simple.

I'll walk through what I know about how SQL Server implements the following statement:

CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex] 
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
) 
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
) 
ON [AnalogMonthlyPScheme]([LogTime]);

Before writing any rows SQL Server needs to sort the entire table's data by LogTime, CTDIID, WindFarmId, and StationId. The size of the sort will be massive. I suspect that it will be about the size of the uncompressed clustered rowstore table. It will probably spill over from tempdb to disk. Perhaps that part of the operation is not minimally logged? I can't really guess as to why else you are seeing so much log activity. Note that sorting on LogTime would be sufficient for your conversion and might make the sort cheaper but I don't think it would reduce the workspace required for the sort.

Along with the sort the data will be written to a new table. The old CCI table will be dropped after the new table is finished. For your biggest table you estimate that this will be 20 TB. It could take a long time to write 20 TB of data.

If you want to convert your data avoiding huge sorts and huge transactions it should be possible to do, but messy. Before going into this I need to point out that if your CCI is already partitioned the right thing to do is to use partition switching to better organize your rowgroups. Based on your question it seems like tables aren't partitioned yet but I want to be sure. I also have not tested any of the following.

Create a partitioned rowstore heap or a partitioned view. The partition scheme of these needs to match your future CCI partition scheme. Delete the TOP N rows from the CCI into the partitioned heap using the TABLOCK hint. That should get you minimal logging for the insert. You will not get minimal logging for the delete against the CCI. However, a delete against a CCI is just changing values in a bitmap so it should require far less logging than a standard delete.

The above insert should require a sort for the heap and will probably require a sort for the partitioned view as well. However, since you control the value for N you can limit the size of the sort to fit it within memory + tempdb.

Note that as you continue to do loops your query will probably take longer and longer to read data from the CCI. This is because it needs to scan past the deleted rows. Periodically you should issue a REORGANIZE against the CCI to clean up those deleted rows.

After the loops are finished you'll have all of your data in the correct partitions but unsorted. At this point for each partition or table you can partition switch into a heap, build the rowstore clustered index on the table, build the CCI with DROP_EXISTING = ON and MAXDOP 1, and finally partition switch into the new CCI table. After you are done with all of the partition switching you should have a partitioned CCI with excellent rowgroup elimination by LogTime.

Two notes about loading new data into a partitioned CCI. If you want perfectly ordered, full rowgroups you can't use parallel inserts. That's because the parallel reads of the source data will lead to unevenly distributed rows on threads and each thread will load to a different rowgroup. Also, plan your partitions carefully so that you don't need to split a partition with data. You can't directly SPLIT a partition without dropping the CCI which you obviously don't want to do.

  • first thank you for comprehensive response. I have 3 tables and the first 2 big tables were rebuilt successfully, i verified the alignment of RGs they have have improved significantly. I had some test queries and i am trying to consolidate the results. I'll get back on this shortly today. What I may have missed in the estimate is the storage needed for log files. – rdagumampan Mar 10 '17 at 5:59
  • The first attempt failed, I think because i am not doing the rebuild on partition scheme and this creates one single huge transaction and it fills up the log file. In this second attempt, I expect the tranlog to be stable or as I expected the logfine to be automatically truncated every partition. I hope you can take the time to see the attached/linked document. It describes the sequence of the queries and partition scheme/functions. – rdagumampan Mar 10 '17 at 5:59
  • @rdagumampan The create index is a single transaction. It is not true that the log will be truncated for each partition. – Joe Obbish Mar 10 '17 at 11:22
  • thats really sad to know... the last thing i would like to do is to create data transfer script as its quite messy as you said. i wish to keep the transaction log small while index is in progress and i can afford to lose any recovery data if anything fails. – rdagumampan Mar 10 '17 at 13:36
  • Building a new index is not the issue for transaction log usage, but rebuilding a clustered index will rewrite the whole table. If that should be not logged creating a new one is maybe an option. – eckes Mar 14 '17 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.