SQL SERVER – Data and Index Compression – Real-World Conversation

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.

SQL SERVER - Data and Index Compression - Real-World Conversation IndexCompression-800x230

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.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Wait Stats Collection Scripts : Updated March 2021
Next Post
SQL SERVER – List Expensive Queries – Updated March 2021

Related Posts

2 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.

    Reply
    • 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.

      Reply

Leave a Reply

Menu