Xrfgtjtk

After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts past a certain fragmentation threshold) need to specify data compression again? Would the indexes otherwise be effectively decompressed?

Indexes remain compressed when rebuilding / reorganizing them.

Create table and compressed index

 CREATE TABLE DBO.TEST_INDX(id int, bla varchar(255));

 CREATE INDEX IX1 ON dbo.TEST_INDX(id)  WITH (DATA_COMPRESSION = PAGE);

Check Compression

 SELECT i.name, p.data_compression_desc 

 FROM sys.partitions P

 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id

 WHERE P.data_compression > 0 and I.name = 'IX1';

Result

name    data_compression_desc

IX1     PAGE

Rebuild the index

ALTER INDEX IX1 on  DBO.TEST_INDX rebuild 

Check Compression

 SELECT i.name, p.data_compression_desc 

 FROM sys.partitions P

 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id

 WHERE P.data_compression > 0 and I.name = 'IX1'

Result

name    data_compression_desc

IX1     PAGE

Disabling them and then rebuilding has a different result, since disabling removes the index, while keeping the index definition.

alter index IX1 on  DBO.TEST_INDX DISABLE ;

alter index IX1 on  DBO.TEST_INDX REBUILD ;

Result

name    data_compression_desc

Compression was lost, compression definition would also be lost when dropping and creating the index via SSMS without adapting the Index create script.

Why?

Because the data_compression option is not retained when scripting out the Index create statement.

however, if we disable the index , rebuild with compression and then rebuild again:

alter index IX1 on  DBO.TEST_INDX DISABLE ;

alter index IX1 on  DBO.TEST_INDX REBUILD  WITH (DATA_COMPRESSION = PAGE);

alter index IX1 on  DBO.TEST_INDX REBUILD;

Result

name    data_compression_desc

IX1 PAGE

Testing a rebuild with Ola hallengren's maintenance solution

The parameters are modified for testing purposes.

Add some data to get to one page, as it is needed for the MinNumberOfPages parameter.

INSERT INTO dbo.TEST_INDX(id,bla)

VALUES(5,'test');

go 10 

Execute the index optimize proc to print out the statement.

EXECUTE dbo.IndexOptimize

@Databases = 'TestDB',

@FragmentationLow = 'INDEX_REBUILD_ONLINE',

@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@Indexes = 'TestDB.DBO.TEST_INDX',

@Execute = 'N',

@MinNumberOfPages = 1;

Result:

Command: ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)



Comment: ObjectType: Table, IndexType: NonClustered, ImageTex

t: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1, Fragmentation: 0

Outcome: Not Executed

Duration: 00:00:00

Date and time: 2019-01-09 14:48:12

Executing the generated command

ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Compression is retained

name    data_compression_desc

IX1 PAGE

Testing a rebuild with a maintenance plan (I would strongly argue for ola's solution)

Rebuild indexes

Choose the test table

Add some test fragmentation levels.

Insert some values to get the fragmentation going

INSERT INTO dbo.TEST_INDX(id)

SELECT id from TEST_INDX

go 4

Check the fragmentation percentage

SELECT 

I.[name] AS  INDX ,

IPS.avg_fragmentation_in_percent,

IPS.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[TEST_INDX]'), NULL, NULL, NULL) AS IPS

INNER JOIN sys.indexes AS I ON I.[object_id] = IPS.[object_id]

AND IPS.index_id = I.index_id

WHERE IPS.database_id = DB_ID()

and I.name = 'IX1'

Result

INDX    avg_fragmentation_in_percent    page_count

IX1 66,6666666666667    3

Run the plan

The interesting part here, when looking at the plan report, is that the DATA_COMPRESSION = PAGE option is added to the generated REBUILD command!

Command:USE [TestDB]

GO

ALTER INDEX [IX1] ON [dbo].[TEST_INDX] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, DATA_COMPRESSION = PAGE)

Fragmentation:

INDX    avg_fragmentation_in_percent    page_count

IX1 0   2

Compression:

name    data_compression_desc

IX1 PAGE