Real-Time Operational Analytics: Compression Delay option with NCCI and the performance

  • Article

The previous blog https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/  showed the scenario and benefits of compression-delay option for NCCI. In this blog, I describe an experiment on a transactional workload representing Order Management to measure the effectiveness of compression delay.

Workload

It is an order management application. New orders are inserted and they go through multiple updates over the next 45 minutes and then they becomes dormant. We ran this workload in two distinct phases. In phase-1, the concurrent transaction workload is run for a fixed duration creating/processing new orders. At the end of phase-1, we measure how many orders were processed. In phase-2, we run a fixed number of concurrent analytics queries in a loop and measure how long it took to complete them. This experiment was run both with compression delay (a) 0 and (b) 45 minutes.  Here are the results

Compression Delay = 0 minutes (default)

  • Total numbers of orders processed =  466 million
  • Total on-disk storage for NCCI = 13.8 GB
  • Total time taken to run the fixed set of analytics queries = 03:06:09

Compression Delay = 45 minutes

  • Total numbers of orders processed = 541 million
  • Total on-disk storage for NCCI = 9.9 GB
  • Total time taken to run the fixed set of analytics queries = 02:19:53

What it shows that with compression delay, we speed up transactional workload approximately 15%, reduced storage footprint by 30% and improved the performance of analytic queries by 20%+. All this was done by just setting the compression delay without requiring any changes to the application. Note, the compression delay is just an option on the index. You can change it anytime and it does not require index rebuild.

Note, compression delay option is supported on all forms of columnstore indexes.

Thanks

Sunil