Disabling An Index Drops Compression

This post is more of an FYI, and to remind myself in case I run into it again down the road.

Here at work we have a very large, very intensive data load that disables and rebuilds indexes as part of the process. We recently added compression to many of the tables and indexes in the database because it was growing quite large (around 28TB at the time). After adding compression, we got the database size down to somewhere around 17TB.

So you can imagine our surprise when the DB size jumped back up to over 30TB after the last data load! In trying to figure out what happened I discovered that most of the data compression was gone. After some investigation one of the developers discovered that when the indexes were disabled and rebuilt, they were rebuilt without compression. The reason for that is because when you disable an index, the index’s current compression setting gets changed to “none” in sys.partitions, and that is the value SQL Server will use when rebuilding the index.

Here is an example: Create an empty table (we don’t need data for this test) and add a nonclustered index to it, specifying page compression.


--create an empty table for our test
CREATE TABLE [dbo].[Compression_Test](
	[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
	[Col2] [varchar](100) NOT NULL,
	[Col3] [int] NOT NULL,
	[Col4] [int] NULL,
	[Col5] [int] NULL,
	[Col6] [varchar](255) NULL,
	[Col7] [varchar](255) NULL
 )
GO

--add a compressed nonclustered index
CREATE NONCLUSTERED INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] (Col2,Col3) WITH (DATA_COMPRESSION=PAGE);

--create an empty table for our test

CREATE TABLE [dbo].[Compression_Test](

[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

[Col2] [varchar](100) NOT NULL,

[Col3] [int] NOT NULL,

[Col4] [int] NULL,

[Col5] [int] NULL,

[Col6] [varchar](255) NULL,

[Col7] [varchar](255) NULL

)

--add a compressed nonclustered index

CREATE NONCLUSTERED INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] (Col2,Col3) WITH (DATA_COMPRESSION=PAGE);

Now, check the compression of our newly created index and it should be listed as “page”.


--check the compression of our index
SELECT name, i.type_desc,p.data_compression,p.data_compression_desc FROM sys.indexes i
LEFT JOIN sys.partitions p ON p.object_id = i.object_id
WHERE i.name = 'IX_Test_Compression'
AND i.index_id = 2

--check the compression of our index

SELECT name, i.type_desc,p.data_compression,p.data_compression_desc FROM sys.indexes i

LEFT JOIN sys.partitions p ON p.object_id = i.object_id

WHERE i.name = 'IX_Test_Compression'

AND i.index_id = 2

Now, disable the index and check the compression again:


--disable the index
ALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] DISABLE;

--recheck the compression of our index
SELECT name, i.type_desc,p.data_compression,p.data_compression_desc 
FROM sys.indexes i
LEFT JOIN sys.partitions p ON p.object_id = i.object_id
WHERE i.name = 'IX_Test_Compression'
AND i.index_id = 2

--disable the index

ALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] DISABLE;

--recheck the compression of our index

SELECT name, i.type_desc,p.data_compression,p.data_compression_desc

FROM sys.indexes i

LEFT JOIN sys.partitions p ON p.object_id = i.object_id

WHERE i.name = 'IX_Test_Compression'

AND i.index_id = 2

This time you should notice that the compression setting is set to “None”. If you were to rebuild the index now, it would be rebuilt without compression. In order to ensure that compression is enabled, you have to explicitly specify it in your rebuild statement


ALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] REBUILD WITH (DATA_COMPRESSION=PAGE);

ALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] REBUILD WITH (DATA_COMPRESSION=PAGE);

I have tested this on SQL Server 2016 and SQL Server 2019, and get the same results on each.

I’m not sure if this should be considered a feature or a bug. In my mind, when re-enabling a disabled index it should retain these settings, and the index be restored as it was when it was disabled.

Luckily, in our case, we had SQL Server Metrics Pack running on the server, so we were able to recover the scripts for all of the indexes that had been previously compressed.

(Visited 887 times, 1 visits today)

Disabling An Index Drops Compression

Related

1 thought on “Disabling An Index Drops Compression”

Share this:

Related

1 thought on “Disabling An Index Drops Compression”