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…
, 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’)
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.