The sp_estimate_data_compression_savings system stored procedure in SQL Server 2008 estimates how much space can be saved by compressing a single partition of an index on a table. Performing this estimation for many tables, indexes, and partitions manually is a tedious task.
The attached script is a wrapper for the sp_estimate_data_compression_savings procedure, that calls it on each partition in the database and in the end outputs a single result set. This multi-row result set is similar to the one-row set from sp_estimate_data_compression_savings. It contains three rows for every partition in the database, one row for each available compression option (NONE, ROW, PAGE). The three totals rows, presenting average compression estimates for the entire database, are at the top of the result set. As written, the script includes only the database level totals, however it is not hard to add other levels of aggregation (i.e., by schema, table, index, or a combination thereof), by modifying the GROUPING SETS clause in the last SELECT statement.
Please note that for large databases, the script can take a long time to complete. To gauge progress, the script outputs status messages referencing the object, index, and partition used as arguments for the current call to sp_estimate_data_compression_savings.
The sp_estimate_data_compression_savings stored procedure is available only in the Enterprise, Developer, or Datacenter editions of SQL Server 2008 and SQL Server 2008 R2.
EstimateCompressionSavings.sql
© 2019 Microsoft. All rights reserved.