13

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?

22

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
  • 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: sqlservercentral.com/Forums/2017336/Databases-Lost-Compression Totally at a loss for how this happened. – Marvel Jan 22 at 15:17
  • Hi @Marvel, it could be that some other process recreated indexes on the databases? For example some applications do 'upgrades' where they drop and create countless indexes. However I do not think that anybody will be able to give a clear-cut explanation without more details. Next time it happens, you could find the index creation date, and find if they where recreated (e.g. with the query in this link: stackoverflow.com/questions/7579932/…. Otherwise you could always ask as a question, but I do think that you would need to provide more info. – Randi Vertongen Jan 23 at 19:17
  • 1
    Thanks, Randi! I set up SCHEMA_OBJECT_CHANGE_GROUP auditing on the databases but this will definitely help me to parse through the logs faster. I have already found one of the culprits- the owner of the databases, the one who requested the compression has been modifying tables & indexes constantly. He did not realize that when he created new tables & moved the old data in & creating new indexes would cause compression to be lost. :( I have provided him with the correct way to create his tables & indexes. I don't think he is the only culprit, however. I cannot imagine that he has done this to e – Marvel Jan 25 at 16:58

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.