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.
Click here to get it from my GitHub Gists.
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):
- Extra IO workload
- Extra CPU workload
- Extra schema-level locks
- Extra disk-space used by TempDB
- Rapid loss of disk free space
- Possible schema-level deadlocks (in some SQL versions. It may be solved using MAXDOP 1 at instance-level)
- Extra time spent in front of your computer while sweating profusely
- … or extra requirement of coffee intake
- … or booze
- … or a sudden inexplicable need to seek new job openings
- … or all of the above!
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:
- Outputs a remediation command to REBUILD each relevant index with ROW compression.
- The script has parameters for:
- Minimum number of compression attempts
- Maximum success rate percentage
- Index rebuild options
- The script outputs additional details such as:
- Whether the index/table in question is partitioned or not.
- Range-scan percentage
- Leaf-level updates percentage
- It runs the check for ALL accessible and writeable databases.
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:
- Run the Ultimate Compression Savings Estimation script.
- Review and apply its recommended remediations.
- 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.
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:
- What_to_Compress_v2 by SQLBalls at SQLServerCentral.com
- Estimate compression for all tables and indexes with both Row and Page by DawnLove at SQLServerCentral.com
- Compression Estimates by osragde at SQLServerCentral.com
- Evaluation Compression Gains by Microsoft Tiger Team
- Data compression in SQL Server (Microsoft Docs)