What uncompresses tables/indexes?

Question

What uncompresses tables/indexes?

joseph_devereaux

Valued Member

Points: 66
More actions
October 31, 2017 at 10:48 am

#341006

Working with a small DW, trying to keep tables & indexes compressed. They are not dropping/creating tables/indexes. They are not adding fields or other DDL. Trying to find out what actions against a table causes the indexes/tables to uncompress. They are not using any type of uncompress functions. There are no char(max), no blobs, ....
Joe
When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:
- Data is bulk imported with bulk optimizations enabled.
- Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the table does not have a nonclustered index.
- A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.
New pages allocated in a heap as part of DML operations do not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Do you mean that you're looking at a later date and seeing the indexes as uncomressed (Compression = None)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

joseph_devereaux - Tuesday, October 31, 2017 10:48 AM
Working with a small DW, trying to keep tables & indexes compressed. They are not dropping/creating tables/indexes. They are not adding fields or other DDL. Trying to find out what actions against a table causes the indexes/tables to uncompress. They are not using any type of uncompress functions. There are no char(max), no blobs, ....
Joe

Who is 'they'?

joseph_devereaux Valued Member Points: 66 More actions · Answer 3

I have googled this a lot. Read dozens of article. Searched the archives here.

Yes, after 3 to 5 days running production, I see disk space has balooned again. I run my script to find "uncompressed" tables and indexes and can find 16 to 50 of them.

My questions is very specific. After use page compression on tables and indexes, what actions against it can cause the table and/or index to become uncompressed?

joseph_devereaux Valued Member Points: 66 More actions · Answer 4

The "they" is a small group of people that are doing both ETL and report type job functions. "they" are using SSIS and T-SQL jobs to import and export data out of the database.

joseph_devereaux Valued Member Points: 66 More actions · Answer 5

Checking my scripts, yes. I just found 12 tables that were compressed Friday now have

data_compression = 0

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Something or someone is rebuilding the indexes and removing compression, or dropping and recreating the indexes without compression.
Put some auditing in place on DDL statements and identify where the commands are coming from.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

joseph_devereaux Valued Member Points: 66 More actions · Answer 7

I will do that just to make sure.
From below
1) The operations from SSIS are not bulk import
2) Last line : new pages are not compressed. The compression on the heap has to be reset.

Please let me know if you disagree with my interpretation.

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression