SQL Server: sp_estimate_data_compression_savings System Stored Procedure

sp_estimate_data_compression_savings system stored procedure is used to estimate the space that would be saved by implementing row or page compression for a table. DBA/Developer can also use the procedure to estimate the result of disabling compression or implementing compression for an index or a single partition of a partitioned table. The procedure accepts the following input parameters:

 @schema_name: Indicates the schema that contains the table.

This parameter defaults to the current user's schema if a schema is not specified.

@object_name: Indicates the table name.
@index_id: Indicates the ID number of an index.
@partition_number: Indicates the partition number.
@data_compression: Indicates the type of compression. Valid values are NONE, ROW, and PAGE.

The procedure returns a column indicating the current size, size_with_current_compression_setting, and a column indicating the estimated size if the specified type of compression were implemented, size_with_requested_compression_setting. In this scenario, DBA/Developer would be able to determine the estimated savings if DBA/Developer implemented page-level compression for a table. DBA/Developer should note that when evaluating whether to implement compression, DBA/Developer should also consider how the data is accessed. Compression generates overhead, especially for tables that are frequently accessed. Therefore, both the storage savings and the performance impact should be considered.

The sp_spaceused system stored procedure returns current space usage information, not estimated storage savings by implementing compression.