SQL Server 2008 Enterprise Edition includes the ability to natively compress selected data on disk. This can provide significant space savings and improve performance of IO intensive workloads. When data compression is enabled, data pages are also compressed in the buffer pool (i.e., RAM). This provides the additional benefit of increasing the effective amount of application data that can be accessed from the data cache (faster) rather than disk (slower).
SQL Server 2008 supports both ROW and PAGE level compression.
ROW: Row level compression takes fixed length data type columns and stores them in a variable length format.
PAGE: Page level compression goes even deeper than row level compression and minimizes the data redundnacy of the rows in a given page.
You should run the sp_estimate_data_compression_savings system stored procedure or the Data Compression Wizard to evaluate how changing the compression state will affect a table or an index. Extra CPU overhead is required for the server to compress and decompress the data so it makes sense to get an estimate as to how much savings you will get for a given object.
Using the SQL Server 2008 AdventureWorks database we can look at and compare the two different types of compression (Row and Page) for an example object (Production.TransactionHistory)
EXEC sp_estimate_data_compression_savings ‘Production’, ‘TransactionHistory’, NULL, NULL, ‘ROW’;
EXEC sp_estimate_data_compression_savings ‘Production’, ‘TransactionHistory’, NULL, NULL, ‘PAGE’;
NOTE: sp_estimate_data_compression_savings takes a sample of the data from the table and compresses it in tempdb to determine the savings
If you decide to enabling compression on a table the syntax is as follows:
ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION = PAGE)
NOTE: This will NOT add data compression to the indexes on the table Production.TransactionHistory. A seperate ALTER INDEX statement is required for indexes if you decide to enable data compression.
Check out the Data Compression: Strategy, Capacity Planning and Best Practices whitepaper from the SQLCAT team for more information on Data Compression. This is a fantasic indepth look at Data Compression written by Sanjay Mishra that should be considered a “must read” when considering using Data Compression.
Benchmarking with Teamcenter and SQL Server has shown significant performance increases when data compression is used for the Teamcenter database.
Siemens recommends using data compression if your edition of SQL Server allows it.
Check out the Siemens Teamcenter and SQL Server Best Practices whitepaper for more information