Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

Estimating Data Compression ratios for all

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

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!

Related Tips: More | Become a paid author

Last Update: 9/22/2008

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Real solutions for real problems. Get in-depth knowledge of all SQL Server features. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think