Estimating Data Compression

It’s T-SQL Tuesday #007 time and we have Jorge Segarra (blog | twitter) the one and only SQLChicken running things.

I’m hoping he’s going to have so many posts to go through that he won’t notice that I am going to cheat a little.

When I first got the chance to play with SQL 2008 my attention was instantly captured by the opportunity to perform data compression. With an environment growing out of control and a SAN having reached it’s physical limits I really needed something to give me a little breathing room. Data compression provided me that option and I worked towards seeing exactly how much space it could save me. A consequence of this work was that last year I wrote an article for about Data Compression in SQL Server 2008 in which I gave an overview of compression and how it works. As a part of the article I also provided a script which could be used to estimate space savings on every table in a database (extending sp_estimate_data_compression_savings which works on an individual table only).

One thing missing from that article and the attached proc was a final rollup script that would take the data you’d collected and provide it to you in a somewhat more useful format. So in the interests of being helpful here one is…


    , sum(current_size_kb)/1024 as existing_server_size_MB
    , sum(estimated_size_page_kb)/1024 as estimated_server_size_page_compressed_MB
    , sum(estimated_page_savings_kb)/1024 as estimated_server_size_page_savings_MB
    database_name in ( ‘AdventureWorks2008’, ‘AdventureWorksDW2008’, ‘teststatsdb’)
    , object_name
HAVING sum(current_size_kb)/1024 > 0
    , estimated_server_size_page_savings_MB desc


Note: the WITH ROLLUP allows us to quickly get a summary of the total space savings we could get at the server and database level without having to perform other subqueries

So there you go, a quick and easy view of where you might be able to save yourself some disk space on your SQL Servers. Remember though, it’s only a guide and you need to thoroughly test performance before implementing any kind of compression.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s