How good is Data Compression in SQL 2008

Data compression in SQL 2008 is best for data warehouses, where they tend to be very IO constrained and can take a wee hit on CPU and latency.

So how much space will you save with data compression? Well I just took a sample table from our SCOM data warehouse and applied compression. Results are below. The total size went from 1.7 Gb to about 740 MB. Not bad  for one table and its supporting NC indexes.



Total (Kb)

Data Space

Index Space






PAGE compression





ROW compression





In total the data warehouse went from 17GB to 10 GB when all the tables and indexes were compressed with page compression.

How long does it take to compress? Well on a two core laptop and a modest disk drive the 17GB database took 30 minutes.

Which Compression is better page or row ? It depends – while page compression seems to always give better compression, if your query only wants one row in a page the whole page needs to be uncompressed, so you’ll take a cpu and latency hit. If you are doing a large table scan then page compression should work great.

How can you compress and entire database at once ? AFAIK you have to compress each table and index separately. Here is a quick script to generate code to compress an entire database in one go. Just don’t try this on your 3TB data warehouse in one go…

select 'ALTER ' + case when si.type =1 then 'INDEX [' + + '] ON ' else 'TABLE ' END +  ' [' + + '].[' + + '] REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE); ' 
from sys.indexes  si
inner join sys.objects o on o.object_id =si.object_id
inner join sys.schemas s on s.schema_id=o.schema_id 
where si.type>0 and o.type='U'
order by,, si.index_id

Comments (1)
  1. Piotr Rodak says:

    Thanks Bob, that’s quite interesting info. You also answered question that just raised in my head – what are scenarios of using row vs page compression 🙂

Comments are closed.

Skip to main content