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 SQLServerCentral.com 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…

SELECT
      Database_Name
    , OBJECT_NAME
    , 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
FROM
    estimated_data_compression_savings
WHERE
    database_name in ( ‘AdventureWorks2008’, ‘AdventureWorksDW2008’, ‘teststatsdb’)
GROUP BY
      Database_Name
    , object_name
WITH ROLLUP
HAVING sum(current_size_kb)/1024 > 0
ORDER BY
      Database_Name
    , 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.