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)
