T-SQL Script for Estimating Compression Savings

A couple of weeks ago, I was working on a Microsoft PDW proof-of-concept (POC) and had to measure compression ratios. In order to do this, I fired up SSMS and wrote a little script. The script will iterate through all tables in a database and run the sp_estimate_data_compression_savings stored procedure. This will only work in SQL Server 2008+ versions running Enterprise edition.

If you’re not familiar with this stored procedure, it basically will tell you what effect PAGE or ROW compression will have on your table/index/partition, etc. There are pro’s and con’s with compression. What I’ve tended to see is that compression has very positive results on space, IO, and query duration, with a negative impact on CPU and write speed. Like most things, it’s a trade-off and the results will vary by environment, so I recommend you do some testing before you apply compression to all tables. I tend to use compression mostly for my historical tables and partitions and leave my recent data uncompressed. And, back to the script, I use this stored procedure to estimate the impact of compression and to determine whether to use PAGE or ROW compression. PAGE is a higher level of compression, which means it’s also more expensive in terms of CPU, so if the difference between the two results is negligible, I’m more apt to just use ROW compression.

Now that my impromptu compression discussion is done, let’s get to the actual script. One final word of caution, however. This is an IO intensive process, so you may want to run it after peak business hours.

DECLARE @printOnly  BIT = 0 -- change to 1 if you don't want to execute, just print commands
    , @tableName    VARCHAR(256)
    , @schemaName   VARCHAR(100)
    , @sqlStatement NVARCHAR(1000)
    , @tableCount   INT
    , @statusMsg    VARCHAR(1000);
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#tables%')
    DROP TABLE #tables; 
      database_name     sysname
    , schemaName        sysname NULL
    , tableName         sysname NULL
    , processed         bit
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%')
    DROP TABLE #compressionResults;
IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%')
    CREATE TABLE #compressionResults
          objectName                    varchar(100)
        , schemaName                    varchar(50)
        , index_id                      int
        , partition_number              int
        , size_current_compression      bigint
        , size_requested_compression    bigint
        , sample_current_compression    bigint
        , sample_requested_compression  bigint
    , SCHEMA_NAME([schema_id])
    , name
    , 0 -- unprocessed
FROM sys.tables;
SELECT @tableCount = COUNT(*) FROM #tables;
WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)
    SELECT TOP 1 @tableName = tableName
        , @schemaName = schemaName
    FROM #tables WHERE processed = 0;
    SELECT @statusMsg = 'Working on ' + CAST(((@tableCount - COUNT(*)) + 1) AS VARCHAR(10)) 
        + ' of ' + CAST(@tableCount AS VARCHAR(10))
    FROM #tables
    WHERE processed = 0;
    RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;
    SET @sqlStatement = 'EXECUTE sp_estimate_data_compression_savings ''' 
                        + @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''PAGE'';' -- ROW, PAGE, or NONE
    IF @printOnly = 1
        SELECT @sqlStatement;
        INSERT INTO #compressionResults
        EXECUTE sp_executesql @sqlStatement;
    UPDATE #tables
    SET processed = 1
    WHERE tableName = @tableName
        AND schemaName = @schemaName;
FROM #compressionResults;

11 Responses to T-SQL Script for Estimating Compression Savings

  1. Artem Ervits says:

    Here’s a way to script it on a server in powershell. This script can easily be changed to run against all servers either from a servers.txt or from Central Management server call to msdb. Here’s another example at this link that I wrote to identify any tables that are not compressed http://artemervits.blogspot.com/2011/01/identify-uncompressed-tables-in-sql.html

    I’m still working on converting your index defrag script into Powershell ;).


    #$hostname = Get-Content “D:\NYP\CheckSQLServer\Staging\test.txt”
    echo “–Please Enter Server Name:”
    $hostname = Read-Host

    echo “–Please Enter Compression to estimate savings, valid input is ‘row’ or ‘page’ with no single quote”
    $compression = Read-Host

    Write-Host “–Servername: ” $hostname
    Write-Host “–Compression to calculate is: ” $compression

    [System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null
    $sqlserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $hostname

    foreach($db in $sqlserver.Databases)
    if($db.ID -gt 4 -and $db.Name -ne “ReportServer” -and $db.Name -ne “ReportServerTempDB”)
    foreach($table in $db.Tables)
    foreach($schema in $table.Schema)
    if($table.HasCompressedPartitions -ne $true)
    $use = “USE ” + $db.Name
    $go = “GO”
    $sql = “EXEC sp_estimate_data_compression_savings [” + $schema + “]” + “, ” + “[” + $table.Name + “]” + “, NULL, NULL, ” + $compression

    echo $use
    echo $go
    echo $sql
    echo $go


    Write-Host “—————————-“

  2. Artem Ervits says:

    Here’s the output from the script above:

    –Please Enter Server Name:
    –Servername: E3802XX6951
    –Compression to calculate is: PAGE
    USE AdventureWorks
    EXEC sp_estimate_data_compression_savings [dbo], [AWBuildVersion], NULL, NULL, PAGE
    USE AdventureWorks
    EXEC sp_estimate_data_compression_savings [dbo], [DatabaseLog], NULL, NULL, PAGE
    USE AdventureWorks
    EXEC sp_estimate_data_compression_savings [dbo], [ErrorLog], NULL, NULL, PAGE
    USE AdventureWorks
    EXEC sp_estimate_data_compression_savings [HumanResources], [Department], NULL, NULL, PAGE

    etc. I should’ve clarified that this version only scripts the T-SQL, at some point I will convert it into true powershell script so that it would calculate on the fly.

  3. Pingback: Determine compression on SQL Server database tables | Technobabble by Klee

  4. Pingback: Something for the Weekend – SQL Server Links 17/06/11

  5. Daniel says:

    Can you share what compression ratios you obtained for your POC ?

  6. Jorge says:

    Hi, I only get 7 tables that your script runs on… Any Ideia ?


  7. James C says:

    Thanks for the useful script!

  8. Mark Crooks says:

    Hey Michelle,
    I was just looking to find a script for this and I come by my old colleague.
    Thanx Michelle!

  9. Pingback: notes on SQL data compression | becknspace

  10. Bigpa says:

    Great script, thanks for all your various input(s)

Leave a Reply
