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.

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

Now, disable the index and check the compression again:

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

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 251 times, 1 visits today)

1 thought on “Disabling An Index Drops Compression

  1. Pingback: Disabled Indexes Tell No Compression Tales – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.