(somewhat) Social
« SQL Saturday #43 | Main | BCP Using Powershell »
Monday
Jun072010

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 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.

 

 

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>