Estimating Data Compression ratios for all

By: Chad Boyd   |   Updated: 2008-09-22   |   Comments   |   Related: > Compression

One of my favorite features with SQL 2008 has been Data and Backup compression (which I discuss in more detail technically here) - this is not only because of the actual functionality it brings to the table, but also because of all the technical intricacies that it involves and the impact it can have on many other fun topics (fragmentation, storage, internals, etc.). Of course, the functionality is pretty cool too...

One customer of mine was asking how they can get an idea of the level of compression the different flavors of data compression would have on all the different structures within their database - of course, most folks realize the system procedure sp_estimate_data_compression_savings that exists to provide just that - but, this customer wanted to be able to see this type of information for all structures within their database (partitions, indexes, heaps, etc.) and see where they would get the biggest bang for their buck so-to-speak.

So, I went to work putting together a fairly simple procedure that would basically run through a database and execute that for each partition for each type of compression that each given partition wasn't currently set in (i.e., if the partition is NONE compressed, we want to see estimations for ROW and PAGE compression; however, if the partition is already ROW compressed, show estimations for NONE and PAGE compression (or un-compression in the case of NONE)). We also wanted to be able to filter on specific objects and/or thresholds for the minimum size of partition to bother checking.

What came out was sp_estimate_data_compression_savings_all, and I figured we may as well be nice and share with everyone. There's no rocket science here or anything, but a pretty cool procedure nonetheless. Of course, we wouldn't recommend you run this on large production systems during peak hours or anything like that, but it is perfectly well suited for scanning on non-production systems to figure out where to concentrate your time in further investigation.

And, as a final side note, it also includes some of the simple TSQL enhancements that only work with SQL 2008 (compound assignment, inline initialization, etc.) that I usually exclude from my system procedures for backward-compatibility, but since this applies to only 2008 anyhow, I could use them - makes for much cleaner, more easy to write code, that's for sure...

Enjoy!

About the author

Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips