Site icon Eitan Blumin's blog

The Ultimate Compression Savings Estimation Script for an Entire Database

Eitan Blumin

Data compression in SQL Server can definitely go a long way in saving storage space, as well as improve performance. It can especially be significant in very large databases. But in certain scenarios it can actually cause more harm than good, and it’s important to identify these scenarios.

I wrote this script which performs compression savings estimation checks for an ENTIRE database. Yeah there are plenty such scripts out there already, but there’s something in particular that they all seem to be missing, and that is the ability to actually generate the relevant remediation commands for you. After all, ain’t nobody got time for writing all those “ALTER TABLE/INDEX” commands themselves!

This is why I created the T-SQL script below. Check out the “messages” tab once the script completes execution, to find the resulting remediation commands. In addition, you will also get a query result with full details.

The script also performs various checks to determine whether PAGE or ROW compression type would be more beneficial (based on stuff such as range scans vs. updates percentage, difference in compression savings, etc.). It has several parameters at the top to control various thresholds and behaviors, so that you could fine tune it for your specific database.

The default values for all parameters should fit most use cases, but you can still customize them if you have to. I’ve added comments next to each parameter with more details.

Speaking of which, I added a bunch of comments all over the script, and tried my best to make it as concise and reader-friendly as possible. But, if something is still isn’t clear in the script, please let me know (either at the comments section here or at the GitHub Gist), and I’ll do my best to clarify.

A few important notes first

The script performs the check for an entire database based on what’s specified in the @DatabaseName parameter. So don’t forget to change the @DatabaseName parameter to the one you actually want to check (or leave it as NULL to check the database you’re currently connected to).

BE MINDFUL IN PRODUCTION ENVIRONMENTS !

Running this script may take a very long time on databases with large tables, and significant IO and CPU stress may be noticeable. Ironically though, that is usually where you’ll have the biggest benefit to compression.

Schema-level locks may be held for a while per each table, and will possibly block other sessions performing DDL operations (ALTER, DROP, TRUNCATE etc.).

One extra important thing to remember is that this script uses the built-in data compression savings estimation procedure (sp_estimate_data_compression_savings) per each table and compression type (PAGE and ROW). This procedure copies 5% of your table to TempDB and compresses it there. This means that if you have an extremely huge table, it could cause TempDB to significantly inflate in size, which might fill out the storage disk it’s on, or reach its max size (both of which will make your SQL Server unresponsive!).

This is why I’ve added a few cautionary checks and additional threshold parameters, in order to avoid such scenarios: Specifically, @FeasibilityCheckOnly, @MaxSizeMBForActualCheck and @TempDBSpaceUsageThresholdPercent. The parameter @FeasibilityCheckOnly in particular is used to determine whether you want to run sp_estimate_data_compression_savings, or you just want to do a “basic” check (which shouldn’t cause any overheads or major issues) to find possible candidates for compression.

In general, if you want to run this script in a production environment, be sure to do it during low-traffic hours, and make sure you have enough free disk space on the TempDB drive.

The Script:

Click here to get it from my GitHub Gists.

The script is also mirror-published at Madeira’s Toolbox GitHub repository here.

License and Disclaimer

The script is completely free and is provided as-is, without any warrantee. Use at your own risk, and as stated above, be extra careful when running it in production environments.

Possible side-effects may include (especially if executed within very large databases):

Please handle with care! 😉

Tracking Low Compression Success Rates

The column page_compression_success_count from the system function sys.dm_db_index_operational_stats can be used to track whether a table properly benefits from PAGE compression, or would rather be “downgraded” to ROW compression to save on unnecessary CPU load.

This is based on how many PAGE compressions were completed successfully on top of ROW compressions, resulting in 20% or more additionally saved space (you can find more details in Paul’s blog post).

My version of the script adds the following:

The script is available in our Madeira Toolbox repository

So, now we should have the whole package to properly manage data compression in our SQL Server estate:

  1. Run the Ultimate Compression Savings Estimation script.
  2. Review and apply its recommended remediations.
  3. Run the new script to track its success, and rebuild as necessary with ROW compression instead of PAGE compression.

Something interesting I noticed about the page_compression_success_count column, is that it’s populated not only when data is inserted/updated in the table, but also when a PAGE-compressed index is built or re-built on the table.

This means that you should have the information you need immediately after creating or rebuilding an index with PAGE compression, and using the script I provided (or Paul’s query) you’ll be able to determine whether PAGE compression was indeed beneficial, or it would be better to rebuild it using ROW compression instead.

Additional Resources

If this topic interests you, you should check out the following resources which may give you additional perspective on compression savings estimation in SQL server. I used these resources myself while building this script:

Enjoy!

Cover Image by Gerd Altmann from Pixabay

Exit mobile version