Detect Low PAGE Compression Success Rates in all Databases

Thanks to Paul Randal and his recently published blog post: “The Curious Case of… tracking page compression success rates“, I finally discovered what was the purpose of the column page_compression_success_count from the system function sys.dm_db_index_operational_stats, and how to use it.

In short: This column could 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).

This was actually the missing piece that I needed to accompany my “Ultimate Compression Savings Estimation Script“.

The script provided by Paul was very bare-bones and simple. Just a simple query to get the page compression success rate per table.

But I wanted more than that.

So, here is my own expansion to Paul’s script, which 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.

See the gist on github.

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. 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.