Data Compression: Strategy, Capacity Planning and Best Practices
SQL Server Technical Article
Writer: Sanjay Mishra
Contributors: Marcel van der Holst, Peter Carlin, Sunil Agarwal
Technical Reviewer: Stuart Ozer, Lindsey Allen, Juergen Thomas, Thomas Kejser, Burzin Patel, Prem Mehra, Joseph Sack, Jimmy May, Cameron Gardiner, Mike Ruthruff, Glenn Berry (SQL Server MVP), Paul S Randal (SQLskills.com), David P Smith (ServiceU Corporation)
Published: May 2009
The data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.
This white paper provides guidance on the following:
- How to decide which tables and indexes to compress
- How to estimate the resources required to compress a table
- How to reclaim space released by data compression
- The performance impacts of data compression on typical workloads
Read Whitepaper: Data Compression Strategy Capacity Planning and Best Practices
Abstract courtesy : Microsoft
Estimate Data Compression Savings First
Before I compress any table, I measure. SQL Server ships with a system procedure that estimates the savings for row or page compression, so you can see the benefit before you spend CPU on it. Run it on your largest tables first. Also note that since SQL Server 2016 SP1, data compression is available in Standard Edition too, not only in Enterprise Edition.
EXEC sp_estimate_data_compression_savings 'dbo', 'YourTable', NULL, NULL, 'PAGE';Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
