SQL Server Page Compression Savings Calculator
Page compression can cut the size of large ERP history and transaction tables by a third to well over a half, which shrinks storage, backups and restore time. This calculator turns your database size and expected saving into GB, annual cost and rebuild hours.
Your numbers
Large history and transaction tables are good candidates. Skip tables with heavy updates or LOB data.
Take this from sp_estimate_data_compression_savings on a sample of tables.
Production, DR replica, test or QA refreshes.
Include SAN or cloud disk and snapshot cost.
Measured speed of an ALTER INDEX REBUILD WITH DATA_COMPRESSION on your hardware.
Your results
Compression also uses CPU on reads and writes and can slow write-heavy tables. Page compression is available on SQL Server editions that support it. Test on a restored copy first.
Annual storage saving versus compression ratio
Sweeps Expected compression saving from 10 to 80, holding your other inputs fixed.
Compression candidate scan
Optional: we can rank your biggest ERP tables by expected compression benefit, read-only, and send you the shortlist.
Optional. The tool and every export work without this. No spam.
How to use it
Start with used database size and estimate how much sits in large tables that are read far more than written, such as ledger history, inventory transactions, audit logs and EDI archives. Run sp_estimate_data_compression_savings on a few of them to get a realistic saving percentage, then enter it. Add how many copies of the database you store and your cost per GB. The rebuild rate comes from timing one test rebuild.
- Sample the biggest five tables, not the whole database
- Count DR and test copies in the cost
- Measure rebuild speed on a restored copy
Row compression versus page compression
Row compression stores fixed-length types in a variable-length format and typically saves less. Page compression adds prefix and dictionary compression on each page, which suits repetitive ERP data such as codes, dates and status flags. The cost is CPU when rows are read or modified. Tables that are mostly scanned and rarely updated gain the most. Tables with heavy random updates may be better left alone or given row compression only.
Worked example
A 1,500 GB database with 70 percent of its data in compressible tables has 1,050 GB in scope. At a 50 percent saving you free 525 GB per copy. With three copies at 10 cents per GB per month, that is about 1,890 dollars a year, plus smaller backups and faster restores. Rebuilding at 60 GB per hour takes around 17.5 hours, which is why you phase it across several maintenance windows.
Common mistakes
Do not apply compression to everything blindly. Check CPU headroom first, because compression moves cost from disk to processor. Rebuilds are fully logged in full recovery, so size the log and ship log backups often. Finally, new indexes do not inherit the table setting automatically, so include DATA_COMPRESSION in your index scripts.
Frequently Asked Questions
How much does page compression usually save?
ERP transaction and history tables commonly save 30 to 70 percent depending on how repetitive the data is. Tables with already compact or encrypted data, or large LOB columns, save far less. Always sample with sp_estimate_data_compression_savings before you decide.
Does compression slow my ERP down?
It adds some CPU on reads and writes, but often reduces I/O, which can make scans faster. Write-heavy tables with random updates can slow noticeably. Test with a realistic workload on a restored copy and watch CPU before and after.
Is backup compression the same thing?
No. Backup compression shrinks only the backup file. Data compression changes how data is stored in the database itself, reducing disk use, memory use and I/O. The two stack, but the benefit of backup compression is smaller on already compressed data.
Can I roll it back?
Yes. You can rebuild with DATA_COMPRESSION = NONE, but it needs another full rebuild window and log space. That is why testing on a copy first matters.
If you want a read-only scan that ranks your ERP tables by compression benefit and risk, we can run that on your own servers.
Related Tools
SQL Server Index Size Estimator Calculator
Estimate how many GB a new nonclustered index will add to your ERP database, and see how fill factor changes the size.
ERP OperationsERP Database Backup and Restore Time (RTO and RPO) Calculator
Estimate backup size, restore time (RTO) and worst-case data loss (RPO) for an ERP database from full, differential and log backups.
ERP OperationsLegacy Data Archival Cost Calculator
Compare annual cost to archive aging data to a cheaper storage tier against the cost of keeping it live inside your production ERP.
ERP OperationsERP Database Growth Forecaster
Forecast how large your ERP database will be in one to ten years, what storing it across environments will cost, and how much archiving could save.
More Developer Tools tools
See all- SQL Server Transaction Log Sizing Calculator
- ERP Incremental Extract Watermark SQL Generator
- SAP Sales Order ABAP SELECT Generator (VBAK, VBAP, VBFA)
- SAP Sales Order Create BAPI ABAP Generator (BAPI_SALESORDER_CREATEFROMDAT2)
- SAP ABAP Open Items SELECT Generator
- SAP ACDOCA SQL Query Generator
- SAP QM Inspection Lot ABAP SELECT Generator
- SAP Quality Notification Pareto SQL Query Generator
- SAP QM Usage Decision BAPI Skeleton