SQL Server - Estimate Data Compression Savings for an Entire Database

Posted 04/13/2021 1:48 PM by Corey Klass

I've recently been researching SQL Server database compression in an effort to save disk space in my company's production and non-production environments. Much of our database contains text that would compress extremely well.

SQL Server offers the stored procedure sp_estimate_data_compression_savings to provide an estimate of what storage savings data compression would offer your tables and indexes. I'd like an at-a-glance view of all tables, but it unfortunately can only be run on a single table at a time and not an entire database.

Below you can find a SQL script that collects all of the user tables in a database, executes the data compression estimate stored procedure on them, and returns the results in a temp table.

    use MyDatabase

    if (object_id('tempdb.dbo.##Tables') is not null)
      drop table ##Tables

    create table ##Tables (
      object_name sysname,
      schema_name sysname,
      index_id int,
      partition_number int,
      size_with_current_compression_setting bigint,
      size_with_requested_compression_setting bigint,
      sample_size_with_current_compression_setting bigint,
      sample_size_with_requested_compression_setting bigint

    set nocount on

    -- Collect all tables
    declare @tableName sysname
    declare @errorText varchar(MAX)

    declare curTables cursor for
      select name
      from sys.tables
        type = 'U'

    open curTables

    fetch next from curTables into @tableName

    while @@fetch_status = 0 begin
      begin try
        insert into ##Tables
        exec sp_estimate_data_compression_savings 'dbo', @tableName, null, null, 'PAGE'
      end try

      begin catch
        set @errorText = @tableName + ' : ' + error_message()

        print @errorText

      end catch

      fetch next from curTables into @tableName

    close curTables
    deallocate curTables

    set nocount off

    select * from ##Tables