Update on data compression performance/space-savings and links to published white papers
It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Server 2008 with customers. Many customers have been able to reduce the size of their database significantly leading to reduction in the cost of hardware and storage management. The table below shows the actual space savings that some of the customers have achieved with ROW and PAGE compression in their production environment. Each row in the table below represents a different customer. Couple of key observations; First, the space savings can be huge. For example, one customer has been able to achieve 81% space savings with PAGE compression. Second, not surprisingly, the PAGE compression provides better compression at the cost of additional CPU cycles. As I had emphasized in my earlier blogs, the space savings achieved will depend on the schema and the data distribution.
Data Compression Space Savings |
Notes |
70% |
PAGE. Data Warehouse application. |
40% |
PAGE. OLTP Web application. |
62% |
PAGE. DW application. |
38%, 21% |
PAGE, ROW. |
80%, 50% |
PAGE, ROW. |
52% |
PAGE. |
50%, 15% |
PAGE, ROW. |
81% |
PAGE. ERP application. |
35% |
PAGE. |
Now, this may all sound good, but one question that keep coming back to me is 'Well space savings is alright, but how does data compression impact the performance of the workload?'. I agree we can all create best/worst cases to make our point but the real answer is know how data compression has impacted the real production applications. The table below shows the performance impact of data compression (the green color shows the improvement in the performance while the red color shows the performance degradation). Each row in the table below represents a different customer.
Performance impact |
Notes |
5% |
PAGE compression. OLTP Web application. Large volume of transactions. |
40%-60% |
PAGE compression. Large sequential range queries. A DW Application (IO intensive) |
1% |
PAGE compression. 500 users, 1500 Transactions / sec. OLTP with some reporting queries. |
11% |
PAGE compression. A lot of insert, update and delete activity which leads to increase in CPU usage. A better choice would have been to go with ROW compression. |
2% - 3% |
PAGE compression. OLTP Application. |
3% |
PAGE compression. ERP application – small transactions. |
I am hoping that this has provided some excitement to customers on the sideline. I would like to encourage trying out the feature and seeing for yourself if this is right for your environment. I will also encourage you to read some of the white papers listed below
https://www.unisys.com/eprise/main/admin/corporate/doc/41371394.pdf
https://h71028.www7.hp.com/ERC/downloads/4AA1-8766ENW.pdf?jumpid=reg_R1002_USEN
https://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=4000002956