Today we are going to see a real-world conversation about Data and Index Compression in SQL Server. I recently discussed this with my client of Comprehensive Database Performance Health Check. Let me share the same with you all of you.
Logical Answer for Data and Index Compression
Let us start with an important question – Should we compress our data and index to save space?
The answer is very simple – If you need to save space, you can go ahead with data and index compression.
Compressed table and index take more CPU cycles than non-compressed data to write to disk. This means, if you have a CPU pressure, the compression will further degrade the performance.
However, if you have a slower disk issue or IO contention and no CPU problem, you can go ahead with the compression of the table and index as it will increase your system throughput by reducing I/O.
Real-World Scenario
Now, the logical answer totally makes sense. However, if you have a mixed workload and you have lots of simultaneous queries going on in your system, I suggest that you keep the table compression off. If you still want to compress your data, I suggest, you do for the table which is extremely large and rarely used. If you have a table that is frequently used, I suggest that you keep it uncompressed.
Regarding Indexes, I have a clear opinion that one should not consider compressing them. Think of the scenario where you are reading the data from the index and the query also has a keep lookup on the clustered index. In that case, you will have to go through multiple de-compression scenarios which eventually will lead to slower performance. I have tried this with many of my clients and I have figured out that it is best to leave the indexes uncompressed.
Here are few additional blog posts which are related to Data and Index Compression.
- 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
You can always reach out to me on Twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
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.