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):
- 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.
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:
- 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)
Enjoy!
Cover Image by Gerd Altmann from Pixabay
Pingback: Troubleshooting Long-Running SHRINK Operations – Eitan Blumin's Blog
Pingback: The new “metadata-only column changes” feature in SQL Server 2016 is useless! – Eitan Blumin's Blog
Pingback: I learned to love WHILE (true) and you should too – Eitan Blumin's Blog
Have you considered adding Columnstore compression?
Yes, actually. I did indeed. However, considerations for columnstore indexing are exceedingly more complex than what can be inferred from the SQL server metadata.
There are other similar solutions out there specifically dedicated for columnstore index considerations.
Here is an example:
https://columnscore.com/
size_MB compressible_data scan_percent update_percent compression_type
62822 99 % 0 % 0 % PAGE
62822 99 % 0 % 0 % ROW
10585 100 % 0 % 0 % PAGE
Hi Eitan,
What can I understand from this report? and why what does “0%” means?
(“scan_percent” / “update_percent”)
Hi Yossi!
From your report you can understand that 99% of the data in the first two tables is compressible, and 100% of the data in the third table is compressible. This depends on column data types.
Based on the size_MB, I’m guessing that the first two rows are actually one and the same table, only with a different compression_type.
The “0%” refers to scan_percent and update_percent. It calculates the ratio of access/usage type of the table. In other words: Out of the total number of accesses to this table, 0% of them are scans and updates.
In terms of compression, the following is generally true:
Tables with a high percentage of scans will benefit greatly from compression.
Tables with a high percentage of updates will SUFFER from compression in terms of performance.
This is because compression is most beneficial for scan operations, but it’s the most costly in terms of performance for update operations.
When both access types are “0%”, this makes things a bit ambiguous. It could be because no such access type was captured in the metadata currently existing in cache. It may or may not represent the general behavior in the server, as it depends on how long ago these statistics were collected and how long is the SQL Server uptime.
Or it could be because most of the access into the table is “seek”. That kind of operation can still benefit from compression, just not as much as scan operations.
Or it could also be because the table is only inserted into, but not queried from. This kind of use case can benefit from compression in terms of storage space, but the insertions will most likely have somewhat worse performance.
I hope that cleared things up!
Pingback: Detect Low Compression Rates in all Databases – Eitan Blumin's Blog