Index Compression needs workload measurements of storage, I/O and CPU. A customer conversation prompted my earlier advice.
SELECT s.name AS SchemaName,t.name AS TableName,i.name AS IndexName,
p.partition_number,p.data_compression_desc
FROM sys.partitions p JOIN sys.tables t ON t.object_id=p.object_id
JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.indexes i ON i.object_id=p.object_id AND i.index_id=p.index_id
ORDER BY s.name,t.name,p.index_id,p.partition_number;ROW and PAGE compression can reduce storage and page reads. Encoding and decoding add CPU work. Results depend on data, access paths, modifications, cache and hardware. CPU pressure calls for testing rather than an automatic rejection.
My blanket advice against compressed indexes and frequently used tables was too broad. A frequently read index can benefit from a smaller working set. An infrequent table can still have expensive writes or rebuilds. Configure clustered and nonclustered indexes independently.
Estimate savings on a representative copy. Compare CPU, reads, duration, writes and maintenance under concurrency. Columnstore and archive compression are separate choices. The linked compression resources cover those distinctions.
Reference: Data compression choices.
Related reading
- Comprehensive Database Performance Health Check
- How to Estimate Space Saved by Data Compression in SQL Server?
- Data and Page Compressions – Data Storage and IO Improvement
- Compression Delay for Columnstore Index
- Script to Enable PAGE and ROW Level Compression – Disable Compression
- Finding Compression Ratio of Backup
- COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression
Smaller storage is not a universal performance guarantee, it is one benefit to compare with processing and maintenance costs.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
Hi, this time I must heavily disagree.
Most benefits come from the fact that data is compressed also in the buffer cache, so you get a lot more datapages into buffer cache, and, your queries logical IO reduces accordingly.
‘
Data is in compressed format even in CPU cache levels L3 and L2, so huge benefits come from those superfast always limited sized caches.
Almost always compression makes your queries much faster, and one reason besides getting more datapages into buffer cache is that you get a lot more datapages also into those superfast, near cpu core caches L3 and L2.
Only when CPUs are near 100% usage, I would agree not to go ahead and compress before testing.
In real world, I have been working a long time making SQL Server faster in Microsoft Dynamics AX 2009-2012 and D365 environments and compression benefits are huge there. Even MS recommends compression and D365 comes already compressed.
Hi Vesa,
In theory and practice, I totally agree. The goal of the blog post is to share my experience and the similar way I welcome your experience and thought process.
This conversation helps users to make the best possible decision for their application and server.
hello,
we are trying to compress a large table and its indexes but the transaction log size fills the whole disk space. the database is in simple model. we execute checkpoint in between to get shrink of the trxn log but fails.
we are at sql server 2017. any advice? thank u
Bit late but if anyone else is experiencing this, it must be know that a database being in simple mode does not stop the transaction log from filling up. It just means the transaction log can be truncated immediately after the transaction has been completed.
If expanding the hard drive is not an option then you may need to batch move rows by copying then deleting a few thousand rows at a time into a new table and then using that table in replacement for the current one.
This will take a long time if the database is huge.