One of the more interesting and valuable features in SQL Server 2008 R2 Enterprise Edition (and Datacenter Edition) is data compression. Data compression can help relieve both memory and I/O pressure at the cost of a little bit of CPU pressure. Since most modern database servers have significant extra CPU capacity, using data compression is quite often a huge net win.

Data compression was first added in SQL Server 2008 Enterprise Edition, but it was enhanced in SQL server 2008 R2 to add unicode data compression. SQL Server 2008 and 2008 R2 have two types of data compression. The first type is PAGE data compression, while the second type is ROW data compression. PAGE data compression is a superset of ROW data compression.

The ideal use case for data compression is a large, static table that shows a significant space savings from data compression. For example, a large, read-only table in a data warehouse would be the very best example.  A small table that has lots of updates would not be a good candidate for data compression. Larger tables that have lots of inserts and deletes, but few updates are also good candidates for data compression.

The queries below are designed to help you identify tables and indexes that might be good candidates for using data compression.

-- SQL Server 2008 and R2 Data Compression Queries
-- Glenn Berry 
-- May 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], 
p.index_id, COUNT(*)/128 AS [Buffer size(MB)],  COUNT(*) AS [BufferCount], 
p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC;
-- Get Table names, row counts, and compression status for clustered index or heap
SELECT OBJECT_NAME(object_id) AS [ObjectName], 
SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]
FROM sys.partitions 
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(object_id) NOT LIKE 'queue_%' 
AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%' 
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC;

-- Estimate data compression difference on clustered index if compression type is NONE
EXEC sp_estimate_data_compression_savings 'SchemaName', 'TableName', 1, NULL, 'NONE';
-- Estimate data compression difference on clustered index if compression type is ROW
EXEC sp_estimate_data_compression_savings 'SchemaName', 'TableName', 1, NULL, 'ROW';
-- Estimate data compression difference on clustered index if compression type is PAGE
EXEC sp_estimate_data_compression_savings 'SchemaName', 'TableName', 1, NULL, 'PAGE';