PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

The Case of the Vanishing Index Compression

Recently, we started adding compression to some of our larger tables in our data warehouse where columnstore indexes didn’t make sense. (For example, some very large dimension tables, and a few facts where they are not used for large aggregates in the common manner).

The changes were checked into source control, we monitored performance, all seemed good. Then next time we were doing a code review/compare source control with our development instances, we discovered changes that we were not expecting. Indexes were no longer compressed in some cases.

So I pulled out my Sherlock Holmes hat (which I guarantee you doesn’t exist, and if it did it would have Mickey ears on it), and started sleuthing. To recreate the crime, I will create the following index in the WideWorldImporters database, just a simple index with page level creation turned on:

So now, the index is compressed, right? If you are wondering what the answer is to this question, the answer is yes, clearly, but there is a slight problem with that logic. Is the index really compressed? Check the metadata of the index:

The only mention of compression is “compression_delay”, which still might give you the feeling that compression is a part of the index metadata. What happened in our case was that in our processes to truncate and reload a table, we were disabling one or more indexes, loading the data, then rebuilding the index, using a process such as:

Now the index should be right back to where it started, right? If you query sys.indexes, everything will look the same, but remember how sys.indexes had nothing about compression? The compression is no longer in effect, as you can see in sys.partitions.

This returns:

Partitions are where compression information is stored, because partitions (even if the table/index is not technically partitioned it has one partition), are compressed. Compression delay is part of a columnstore index, which is always compressed, and how long it waits to move the changes from the delta store to a columnstore segment is controlled by the delay. For more on that, see Niko Neugebaur’s blog here:):

The way you have to rebuild the index if you wish to keep it compressed after a disable, is to specify the data compression again:

Checking again in sys.partitions you have:

Problem solved and fairly easy to handle if you are seeing this problem in a typical OLTP database. In these cases, if you are disabling and reenabling indexes on a regular basis, it should be only a few indexes, (and if you are commonly disabling them first, you may need to question your processes.)

Just executing the following code to rebuild your index:

Effectively recreates the index, but it does not change the compression of the object’s partition, because a REBUILD doesn’t drop the index in the same way that a DISABLE does. If your physical structure is partitioned, this will be possibly be more obvious to you, in that you can compress only certain partitions. (If you are using the code that follows, you would need to extend it if your tables are partititoned, and it includes protections that stop it from working on partitioned tables).

Note that the way you change the compression status of the index is to do a rebuild.

The way we combatted this is in our data warehouse, non-partitioned case was by creating a table of indexes that are compressed and using that information our index rebuild stored procedure. This works fine if every programmer and DBA realizes this method and doesn’t use direct DDL to do this process.

Now to disable the index using:

Now you can check the compression on the partition, and what you will see is that it doesn’t even exist, because for indexes, the partition is deleted in the disable process.

You can also see in the metadata, that the index will have PAGE compression when the index is rebuilt using the tool we are about to create:

This returns:

Then this stored procedure will be used to reenable the index, with the last compression status

Execute the procedure:

Now you can check the compression on the partition, and what you will see is that it is not compressed with PAGE level compression.

Mystery solved. It is funny how much fun finding things like this can be once you get started. So when you find something seems wrong in your configuration, never panic, just start looking at the system catalog views and look for the clues. The fact that the index had no compression information was my first clue that this was going to be a problem with how compression is done with indexes.