By Paul-Sebastian Manole


2019-01-09 12:54:41 8 Comments

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?

2 comments

@Marvel 2019-01-22 15:17:11

I came across you post when I found that 3 databases I had compressed had all lost their compression & I had to reapply it. As part of that work, I tested & confirmed your results but have no idea how this happened. The only other possibility that I have come across is that, if indexes are disabled, they lose compression when rebuilt. This does not appear to be the case, per our ETL team. I've also posed this question on SQLServerCentral:

https://www.sqlservercentral.com/Forums/2017336/Databases-Lost-Compression

Totally at a loss for how this happened.

@Randi Vertongen 2019-01-09 13:03:16

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

enter image description here

Choose the test table

enter image description here

Add some test fragmentation levels.

enter image description here

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

enter image description here

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

Related Questions

Sponsored Content

2 Answered Questions

3 Answered Questions

[SOLVED] index rebuild/reorganize frequency

2 Answered Questions

1 Answered Questions

[SOLVED] Alter Index within a Script

1 Answered Questions

3 Answered Questions

[SOLVED] SQL Server 2012, rebuild not lowering avg fragmentation

3 Answered Questions

[SOLVED] Database Indexing - Maintenance Jobs

1 Answered Questions

[SOLVED] FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

1 Answered Questions

[SOLVED] Rebuild or Reorganize indexes based on fragmentation

Sponsored Content