Today we will talk about the Compression Delay for Columnstore Index which I got first-hand experience with my client of  Comprehensive Database Performance Health Check.
Root Cause of Issue
One of my clients who has many tables ranging in the Terabytes size is a heavy user of the ColumnStore Index. The issue which they were facing was that after inserting update and delete into the huge table when they had to modify the data which were just inserted, it was taking a bit more time. The reason we investigated and it was pretty simple.
We figured out that the issue was because we had Data Compression enabled for column store index and due to that reason and heavy transactions on the inserted data, there was a performance penalty for it.
This is quite common when huge data is inserted into a table with a column store index and the same data is modified. Thankfully the solution is simple – Compression Delay.
Compression Delay
Let us first learn in simple words what is actually compression delay. When new data is inserted into a table with a column store compressed index, the data gets to the delta file first and immediately moves to the compressed column store index. However, this can be expensive for performance if you have to modify the same data multiple times before it requires no more change it.
In this scenario, we can run the following command and increase the delay of the delta file to move to the compressed column store index.
ALTER INDEX NameofColStoreIndex ON ColStoreTable SET (COMPRESSION_DELAY = 10 Minutes);
When you run the script above it will keep the delta which is not compressed for around 10 minutes before the data is moved to the disk-based column store index. You can adjust this delay based on your business need.
When we enabled this for our client with 10 minutes their problems disappeared and their performance increased multifold. You can always reach out to me on LinkedIn if you have any questions about SQL Server Performance.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
