Partition based index rebuild with data compression

While doing compression on a partitioned table, be careful of rebuilding multiple index partitions in a single go. Lets explain with an example

USEmaster

GO

-- Create a database called partition

CREATE DATABASE Partition;

USE Partition

GO

-- Create a table test

CREATETABLE Test

(

c1 int identity NOT NULL

,c2 int NOT NULL

,c3 date NOT NULL

,c4 char(7000)

);



Create a partition function with 3 partitions

• partition 1 : all data less than today
• partition 2 : all data for today
• partition 3 : all data for tomorrow and afterwards

CREATE PARTITION FUNCTION pf_Test(date) AS RANGE RIGHT FOR VALUES (GETDATE(),GETDATE()+1)

-- Create the partition scheme with all partitions mapped to PRIMARY file group

CREATE PARTITION SCHEME ps_Test AS PARTITION pf_Test ALL TO ([PRIMARY]);

-- Create the clustered index on column c3. This will be the partitioning column

CREATE CLUSTERED INDEX idx_c3_Test ON Test(c3) ON ps_Test(c3);

-- Insert data in all 3 partitions

INSERT INTO Test VALUES (1,GETDATE()- 1,'a')

INSERT INTO Test VALUES (1,GETDATE(),'b')

INSERT INTO Test VALUES (1,GETDATE()+ 1,'c')





At this stage I want to take a snapshot of the pages allocated for this table.

• First partition has one data page = 110
• Second partition has one data page = 115
• Third partition has one data page = 119

DBCC IND(Partition, Test, 1);



-- Now using the below command I will rebuild only partition 2,3

ALTER TABLE dbo.Test

REBUILD PARTITION=ALL

WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(2,3))

What I see when I fire DBCC IND is that as a result of rebuild the data pages have changed. Starngest thing to note is that even though we didn't specify partition 1 to be rebuilt, it has been and thats why the page allocation for partition 1 has also changed as mentioned below.

• First partition has one data page = 121
• Second partition has one data page = 127
• Third partition has one data page = 151

DBCC IND(Partition, Test, 1);



Thus it has actually done a complete index rebuild (all partitions), which could be very expensive if done on a large table. So what is that I should have done. You guessed it right, I should rebuild the index 1 partition at a time

ALTER TABLE dbo.Test

REBUILD PARTITION= 2

WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(2))

ALTER TABLE dbo.Test

REBUILD PARTITION= 3

WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(3))

A DBCC IND would show that this time page allocation changed for only partition 2 &3, the index rebuild happened only on partition 2 and 3 as in the snapshot below.

DBCC IND(Partition, Test, 1);