Enabling Data Compression – February 2014 Update

Blog reader cp spotted a pretty big bug in the last version of this routine. Namely, non-clustered indexes were being flagged as no longer present and were therefore not being compressed. This was because the existence check I used, supplying an ID to OBJECT_ID() and checking for a NULL value, will always return a null for non-clustered indexes. This is because OBJECT_ID can only find objects that are stored in the sys.objects system table and non-clustered indexes are not stored there. So I modified the routine to check for the existence of non-clustered indexes by using the sys.indexes table. Corrected code is below. Thanks again to cp for pointing this out in the comments here.

Note this script will generate an error if you have an index containing a timestamp column. The issue is a bug with Microsoft’s sp_estimate_data_compression_savings procedure, as detailed here. This has been fixed in SQL 2012, but the bug is still present in SQL 2008 and SQL 2008 R2.  I’ve decided not to modify the code to look for this case, as it seems to be fairly rare and it has been fixed in the latest version. Additionally, the index with the timestamp can still be compressed, it’s just the estimating procedure that fails. If you are running into this problem, I suggest either modifying the code below to exclude those indexes. If you don’t want to get into coding that (and I’ll admit that I don’t), I suggest manually compressing those indexes. Once they are compressed, this code will no longer pick them up and the errors will no longer be generated. Thanks once again to cp for finding this issue as well.

CREATE PROC [dbo].[up_CompressDatabase]
      (
       @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
      ,@MaxRunTimeInMinutes INT = 60
      ,@ExecuteCompressCommand BIT = 0  /* 1 to execute command */
      ,@DBToScan SYSNAME /* database to compress */
      )
AS
      SET nocount ON;
 
/*
    Original script by Paul Nielsen www.SQLServerBible.com March 13, 2008
    Modified by Shaun J. Stuart www.shaunjstuart.com February 27, 2013
 
  Sets compression for all objects and indexes in the database needing adjustment
  If estimated gain is equal to or greater than min compression parameter
    then enables row or page compression, whichever gives greater space savings
  If row and page have same gain then, enables row compression
  If estimated gain is less than min compression parameter, then compression is set to none
 
  - SJS 2/27/13
  - Added @MaxRunTimeInMinutes to limit run length (checked afer each command, so
        may run longer) Note: This timer only applies to the actual compression process.
        It does not limit the time it takes to perform a scan of all the tables / indexes
        and estimate compression savings.
  - Sorted compression cursor to compress smallest tables first
  - Added flag to execute compression command or not
  - Added sort in tempdb option (always)
  - Re-wrote slightly to persist initial scan results. Will only re-scan after all tables
        have been processed
 
    - SJS 7/17/13
    - Modified to only look at data that is not already compressed
    - Modified for that any items with null none_size, page_size, or row_size are no longer set as
        AlreadyProcessed and now have their sizes calculated (i.e. if process was cancelled before
        initial scan was completed)
    - Modified so that time limit now applies to compression estimate process as well as actual
        compression process
    - Only look at partitions that have at least one row
 
    - SJS 8/6/13
    - Changed name of dbEstimate table to dbCompressionEstimates for clarity
    - Added StatusText column to dbCompressionEstimates and added status messages
    - Modified to take database name as input parameter to allow use in utility database
        instead of the DB being compressed (Table dbCompressionEstimates, which stores sizes and
        compression estimates, is still created in the current database.)
    - Note: Compression estimates are only made for the database supplied as an input
        parameter. However, that database name is stored in the dbCompressionEstimates table and
        will be read from there and used when actually performing the compression. This allows you to
        create estimates only for multiple databases (using @ExecuteCompressCommand=0), then perform
        the compression across multiple databases later (with @ExecuteCompressCommand=1).
    - Removed all references to code that checked already compressed data since this routine now only
        looks at uncompressed data.
 
    - SJS 8/21/13
    - Put []s around table and index names in compression commands.
 
    - SJS 10/7/13
    - Added check to make sure table / index still exists before estimating or performing compression.
    - Fixed bug in cursor to determine compression estimates (left out db name in where clause)

    - SJS 1/31/14
    - Fixed bug where nonclustered indexes were always flagged as no longer present (Thanks to cp
		for pointing this out at
		http://shaunjstuart.com/archive/2013/10/enabling-data-compression-october-2013-update/comment-page-1/#comment)
 
*/
 
      IF ISNULL(@DBToScan, '') NOT IN (SELECT   [name]
                                       FROM     sys.databases)
         BEGIN
               SELECT   'Database ' + ISNULL(@DBToScan, 'NULL')
                        + ' not found on server.'
               RETURN
         END
 
      DECLARE @CompressedCount INT;
      SET @CompressedCount = 0;
 
      DECLARE @SQL NVARCHAR(MAX);
      DECLARE @ParmDefinition NVARCHAR(100);
      DECLARE @TestResult NVARCHAR(20);
      DECLARE @CheckString NVARCHAR(1000);
 
      DECLARE @StartTime DATETIME2;
      SET @StartTime = CURRENT_TIMESTAMP;
 
      DECLARE @CurrentDatabase SYSNAME;
      SET @CurrentDatabase = DB_NAME()
 
      IF OBJECT_ID('tempdb..##ObjEst', 'U') IS NOT NULL
         DROP TABLE ##ObjEst
 
      CREATE TABLE ##ObjEst
             (
              PK INT IDENTITY
                     NOT NULL
                     PRIMARY KEY
             ,object_name VARCHAR(250)
             ,schema_name VARCHAR(250)
             ,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_compresison_setting BIGINT
             );
 
      IF NOT EXISTS ( SELECT    1
                      FROM      sys.objects
                      WHERE     object_id = OBJECT_ID(N'[dbo].[dbCompressionEstimates]')
                                AND type IN (N'U') )
         BEGIN
               CREATE TABLE dbo.dbCompressionEstimates
                      (
                       PK INT IDENTITY
                              NOT NULL
                              PRIMARY KEY
                      ,DatabaseName SYSNAME
                      ,schema_name VARCHAR(250)
                      ,object_name VARCHAR(250)
                      ,index_id INT
                      ,ixName VARCHAR(255)
                      ,ixType VARCHAR(50)
                      ,partition_number INT
                      ,data_compression_desc VARCHAR(50)
                      ,None_Size INT
                      ,Row_Size INT
                      ,Page_Size INT
                      ,AlreadyProcessed BIT
                      ,StatusText VARCHAR(75)
                      );
         END
 
  /*
 If all objects have been processed, rescan and start fresh. Useful for catching
  added objects since last scan. But beware - this is I/O intensive and can take a while.
*/
 
      IF NOT EXISTS ( SELECT    1
                      FROM      dbo.dbCompressionEstimates
                      WHERE     AlreadyProcessed = 0
                                AND DatabaseName = @DBToScan )
         BEGIN
               DELETE   FROM dbo.dbCompressionEstimates
               WHERE    DatabaseName = @DBToScan;
 
               SET @SQL = 'USE [' + @DBToScan + '];
                        INSERT   INTO [' + @CurrentDatabase
                   + '].dbo.dbCompressionEstimates
                                (DatabaseName
                                ,schema_name
                                ,object_name
                                ,index_id
                                ,ixName
                                ,ixType
                                ,partition_number
                                ,data_compression_desc
                                ,AlreadyProcessed
                                ,StatusText)
                        SELECT  ''' + @DBToScan + '''
                                ,S.name
                               ,o.name
                               ,I.index_id
                               ,I.name
                               ,I.type_desc
                               ,P.partition_number
                               ,P.data_compression_desc
                               ,0 AS AlreadyProcessed
                               ,''Initial load'' AS StatusText
                        FROM    [' + @DBToScan + '].sys.schemas AS S
                                JOIN [' + @DBToScan
                   + '].sys.objects AS O ON S.schema_id = O.schema_id
                                JOIN [' + @DBToScan
                   + '].sys.indexes AS I ON o.object_id = I.object_id
                                JOIN [' + @DBToScan
                   + '].sys.partitions AS P ON I.object_id = P.object_id
                                                            AND I.index_id = p.index_id
                        WHERE   O.TYPE = ''U''
                                AND P.data_compression_desc = ''NONE''
                                AND P.rows > 0;'     -- only look at objects with data
               EXEC (@SQL)
         END
            -- Determine Compression Estimates
      DECLARE @PK INT
             ,@DatabaseName SYSNAME
             ,@Schema VARCHAR(150)
             ,@object VARCHAR(150)
             ,@DAD VARCHAR(25)
             ,@partNO VARCHAR(3)
             ,@indexID VARCHAR(3)
             ,@ixName VARCHAR(250)
             ,@ixType VARCHAR(50)
             ,@Recommended_Compression VARCHAR(10);
 
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    schema_name
                       ,object_name
                       ,index_id
                       ,partition_number
                       ,data_compression_desc
                       ,ixName
              FROM      dbo.dbCompressionEstimates
              WHERE     (None_size IS NULL
                         OR Row_Size IS NULL
                         OR Page_size IS NULL)
                        AND DatabaseName = @DBToScan;
 
      OPEN cCompress;
 
      FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD, @ixName;
 
      WHILE @@Fetch_Status = 0
            BEGIN
                            /* evaluate objects with no compression */
                  IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                            /* First, make sure the table / index still exists (in case this
                                process is run over multiple days */
 
                           IF @indexID = 0
                              BEGIN /* heaps */
                                    SET @CheckString = 'IF object_ID('''
                                        + @DBToScan + '.' + @Schema + '.'
                                        + @object
                                        + ''') IS NULL
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END
                           ELSE
                              BEGIN /* indexes */
                                    SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                                        + @DBToScan
                                        + '].[sys].[indexes] WHERE [name] ='''
                                        + @ixName + ''' AND OBJECT_ID('''
                                        + '[' + @DBToScan + ']' + '.['
                                        + @Schema + '].[' + @object + ']'''
                                        + ') = [object_id])
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END

                           SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                           EXECUTE sp_executesql
                            @CheckString
                           ,@ParmDefinition
                           ,@TestResultOUT = @TestResult OUTPUT;
                           IF @TestResult = 'Exists'
                              BEGIN
 
                                    IF @DAD = 'none'
                                       BEGIN
                                    /* estimate Page compression */
                                             SET @SQL = 'USE [' + @DBToScan
                                                 + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ' + @Schema
                                                 + '
                                                   ,@object_name = ' + @object
                                                 + '
                                                   ,@index_id = ' + @indexID
                                                 + '
                                                   ,@partition_number = '
                                                 + @partNO
                                                 + '
                                                   ,@data_compression = ''page'';'
                                             EXEC (@SQL)
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    none_size = O.size_with_current_compression_setting
                                                   ,page_size = O.size_with_requested_compression_setting
                                                   ,StatusText = 'Compression estimate 50% complete'
                                             FROM   dbo.dbCompressionEstimates D
                                                    JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
                                                              AND D.Object_name = O.object_name
                                                              AND D.index_id = O.index_id
                                                              AND D.partition_number = O.partition_number;
 
                                             DELETE ##ObjEst;
 
                                    -- estimate Row compression
                                             SET @SQL = 'USE [' + @DBToScan
                                                 + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ' + @Schema
                                                 + '
                                                   ,@object_name = ' + @object
                                                 + '
                                                   ,@index_id = ' + @indexID
                                                 + '
                                                   ,@partition_number = '
                                                 + @partNO
                                                 + '
                                                   ,@data_compression = ''ROW'';'
                                             EXEC (@SQL)
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    row_size = O.size_with_requested_compression_setting
                                                   ,StatusText = 'Compression estimate 100% complete'
                                             FROM   dbo.dbCompressionEstimates D
                                                    JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
                                                              AND D.Object_name = O.object_name
                                                              AND D.index_id = O.index_id
                                                              AND D.partition_number = O.partition_number;
 
                                             DELETE ##ObjEst;
                                       END /* end evaluating objects with no compression */
                              END
                           ELSE /* table or index no longer exists */
                              BEGIN
                                    UPDATE  dbo.dbCompressionEstimates
                                    SET     AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression estimate stage'
                                    WHERE   schema_name = @Schema
                                            AND object_name = @object
                                            AND index_id = @indexID
                                            AND partition_number = @partNO
                                            AND data_compression_desc = @DAD
                                            AND DatabaseName = @DBToScan;
                              END
 
                           FETCH NEXT FROM cCompress INTO @Schema, @object,
                                 @indexID, @partNO, @DAD, @ixName
                     END -- end time check block
                  ELSE
                     BEGIN
                           PRINT 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           RETURN
                     END
            END -- end while loop
 
      CLOSE cCompress
      DEALLOCATE cCompress
 
      PRINT 'Initial scan complete.'
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      PRINT 'Beginning compression.';
 
  /* Do not process objects that do not meet our compression criteria */
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Row_Size <= Page_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Page_Size <= Row_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
  /* Do not set compression on empty objects */
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'No data in table to compress'
      WHERE     None_size = 0
                AND AlreadyProcessed = 0;
 
 -- set the compression
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    DatabaseName
                       ,schema_name
                       ,object_name
                       ,partition_number
                       ,ixName
                       ,ixType
                       ,CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= @minCompression
                                  AND (Row_Size <= Page_Size) THEN 'Row'
                             WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= @minCompression
                                  AND (Page_Size <= row_Size) THEN 'Page'
                             ELSE 'None'
                        END AS Recommended_Compression
                       ,PK
              FROM      dbo.dbCompressionEstimates
              WHERE     None_Size <> 0
                        AND (CASE WHEN (1 - (CAST(Row_Size AS FLOAT)
                                             / none_Size)) >= @minCompression
                                       AND (Row_Size <= Page_Size) THEN 'Row'
                                  WHEN (1 - (CAST(page_Size AS FLOAT)
                                             / none_Size)) >= @minCompression
                                       AND (Page_Size <= row_Size) THEN 'Page'
                                  ELSE 'None'
                             END <> data_compression_desc)
                        AND AlreadyProcessed = 0
              ORDER BY  None_Size ASC;      /* start with smallest tables first */
 
      OPEN cCompress
 
      FETCH cCompress INTO @DatabaseName, @Schema, @object, @partNO, @ixName,
            @ixType, @Recommended_Compression, @PK  -- prime the cursor;
 
      WHILE @@Fetch_Status = 0
            BEGIN
 
                  IF @ixType = 'Clustered'
                     OR @ixType = 'heap'
                     BEGIN
                           SET @SQL = 'USE [' + @DatabaseName + '];
                                ALTER TABLE [' + @Schema + '].[' + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ', SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF object_ID('''
                               + @DatabaseName + '.' + @Schema + '.' + @object
                               + ''') IS NULL
                                BEGIN
                                    SELECT @TestResultOUT = ''Does Not Exist''
                                END
                                ELSE
                                BEGIN
                                    SELECT @TestResultOUT = ''Exists''
                                END';
                     END
 
                  ELSE  /* non-clustered index */
                     BEGIN
                           SET @SQL = 'USE [' + @DatabaseName + '];
                                ALTER INDEX [' + @ixName + '] on [' + @Schema
                               + '].[' + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ',SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                               + @DBToScan
                               + '].[sys].[indexes] WHERE [name] ='''
                               + @ixName + ''' AND OBJECT_ID(''' + '['
                               + @DBToScan + ']' + '.[' + @Schema + '].['
                               + @object + ']''' + ') = [object_id])
                            BEGIN
                                SELECT @TestResultOUT = ''Does Not Exist''
                            END
                            ELSE
                            BEGIN
                                SELECT @TestResultOUT = ''Exists''
                            END';
                     END

                  IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                           IF @ExecuteCompressCommand = 1
                              BEGIN
 
                            /* verify that table / index still exists before doing anything */
 
                                    SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                                    EXECUTE sp_executesql
                                        @CheckString
                                       ,@ParmDefinition
                                       ,@TestResultOUT = @TestResult OUTPUT;
                                    IF @TestResult = 'Exists'
                                       BEGIN
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    StatusText = 'Compressing data...'
                                             WHERE  PK = @PK;
 
                                             PRINT 'Compressing table/index: '
                                                   + @Schema + '.' + @object
                                                   + ' in database '
                                                   + @DatabaseName;
                                             EXEC sp_executesql
                                                @SQL;
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Compression complete'
                                             WHERE  PK = @PK;
 
                                             SET @CompressedCount = @CompressedCount
                                                 + 1;
                                       END
                                    ELSE
                                       BEGIN
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Object no longer exists at compression stage'
                                             WHERE  PK = @PK;
                                       END
                              END
                           ELSE
                              BEGIN
                                    PRINT 'Command execution not enabled. Command is:'
                                          + @SQL;
                              END
                     END
                  ELSE
                     BEGIN
                           PRINT 'Max runtime reached. Some compression performed. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           BREAK
                     END
 
                  FETCH cCompress INTO @DatabaseName, @Schema, @object,
                        @partNO, @ixName, @ixType, @Recommended_Compression,
                        @PK;
            END
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);
      DROP TABLE ##ObjEst
      RETURN

5 thoughts on “Enabling Data Compression – February 2014 Update

  1. Thanks for the script. But above script is failing when there is a space in a table name.
    For ex: We have a table name like “DB Config”. for this table above script is resulting with below error.

    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near ‘Config’

  2. Sorry for the late reply.. It’s probably due to me forgetting to properly enclose table names in brackets. I’ll look into this and post an updated version. Thanks.

  3. The script throws errors when run against a case-sensitive databases. I have modified the script to make it compatible:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_CompressDatabase]’) AND type in (N’P’, N’PC’))
    DROP PROCEDURE [dbo].[up_CompressDatabase]
    GO

    CREATE PROC [dbo].[up_CompressDatabase]
    (
    @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
    ,@MaxRunTimeInMinutes INT = 60
    ,@ExecuteCompressCommand BIT = 0 /* 1 to execute command */
    ,@DBToScan SYSNAME /* database to compress */
    )
    AS
    SET nocount ON;

    /*
    Original script by Paul Nielsen http://www.SQLServerBible.com March 13, 2008
    Modified by Shaun J. Stuart http://www.shaunjstuart.com February 27, 2013

    Sets compression for all objects and indexes in the database needing adjustment
    If estimated gain is equal to or greater than min compression parameter
    then enables row or page compression, whichever gives greater space savings
    If row and page have same gain then, enables row compression
    If estimated gain is less than min compression parameter, then compression is set to none

    – SJS 2/27/13
    – Added @MaxRunTimeInMinutes to limit run length (checked afer each command, so
    may run longer) Note: This timer only applies to the actual compression process.
    It does not limit the time it takes to perform a scan of all the tables / indexes
    and estimate compression savings.
    – Sorted compression cursor to compress smallest tables first
    – Added flag to execute compression command or not
    – Added sort in tempdb option (always)
    – Re-wrote slightly to persist initial scan results. Will only re-scan after all tables
    have been processed

    – SJS 7/17/13
    – Modified to only look at data that is not already compressed
    – Modified for that any items with null none_size, page_size, or row_size are no longer set as
    AlreadyProcessed and now have their sizes calculated (i.e. if process was cancelled before
    initial scan was completed)
    – Modified so that time limit now applies to compression estimate process as well as actual
    compression process
    – Only look at partitions that have at least one row

    – SJS 8/6/13
    – Changed name of dbEstimate table to dbCompressionEstimates for clarity
    – Added StatusText column to dbCompressionEstimates and added status messages
    – Modified to take database name as input parameter to allow use in utility database
    instead of the DB being compressed (Table dbCompressionEstimates, which stores sizes and
    compression estimates, is still created in the current database.)
    – Note: Compression estimates are only made for the database supplied as an input
    parameter. However, that database name is stored in the dbCompressionEstimates table and
    will be read from there and used when actually performing the compression. This allows you to
    create estimates only for multiple databases (using @ExecuteCompressCommand=0), then perform
    the compression across multiple databases later (with @ExecuteCompressCommand=1).
    – Removed all references to code that checked already compressed data since this routine now only
    looks at uncompressed data.

    – SJS 8/21/13
    – Put []s around table and index names in compression commands.

    – SJS 10/7/13
    – Added check to make sure table / index still exists before estimating or performing compression.
    – Fixed bug in cursor to determine compression estimates (left out db name in where clause)

    – SJS 1/31/14
    – Fixed bug where nonclustered indexes were always flagged as no longer present (Thanks to cp
    for pointing this out at

    http://shaunjstuart.com/archive/2013/10/enabling-data-compression-october-2013-update/comment-page-1/#comment)

    – SJS 4/14/14
    – Another fix to handle tables with spaces in their names

    */

    IF ISNULL(@DBToScan, ”) NOT IN (SELECT [name]
    FROM sys.databases)
    BEGIN
    SELECT ‘Database ‘ + ISNULL(@DBToScan, ‘NULL’)
    + ‘ not found on server.’
    RETURN
    END

    DECLARE @CompressedCount INT;
    SET @CompressedCount = 0;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @ParmDefinition NVARCHAR(100);
    DECLARE @TestResult NVARCHAR(20);
    DECLARE @CheckString NVARCHAR(1000);

    DECLARE @StartTime DATETIME2;
    SET @StartTime = CURRENT_TIMESTAMP;

    DECLARE @CurrentDatabase SYSNAME;
    SET @CurrentDatabase = DB_NAME()

    IF OBJECT_ID(‘tempdb..##ObjEst’, ‘U’) IS NOT NULL
    DROP TABLE ##ObjEst

    CREATE TABLE ##ObjEst
    (
    PK INT IDENTITY
    NOT NULL
    PRIMARY KEY
    ,object_name VARCHAR(250)
    ,schema_name VARCHAR(250)
    ,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_compresison_setting BIGINT
    );

    IF NOT EXISTS ( SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[dbCompressionEstimates]’)
    AND type IN (N’U’) )
    BEGIN
    CREATE TABLE dbo.dbCompressionEstimates
    (
    PK INT IDENTITY
    NOT NULL
    PRIMARY KEY
    ,DatabaseName SYSNAME
    ,schema_name VARCHAR(250)
    ,object_name VARCHAR(250)
    ,index_id INT
    ,ixName VARCHAR(255)
    ,ixType VARCHAR(50)
    ,partition_number INT
    ,data_compression_desc VARCHAR(50)
    ,None_Size INT
    ,Row_Size INT
    ,Page_Size INT
    ,AlreadyProcessed BIT
    ,StatusText VARCHAR(75)
    );
    END

    /*
    If all objects have been processed, rescan and start fresh. Useful for catching
    added objects since last scan. But beware – this is I/O intensive and can take a while.
    */

    IF NOT EXISTS ( SELECT 1
    FROM dbo.dbCompressionEstimates
    WHERE AlreadyProcessed = 0
    AND DatabaseName = @DBToScan )
    BEGIN
    DELETE FROM dbo.dbCompressionEstimates
    WHERE DatabaseName = @DBToScan;

    SET @SQL = ‘USE [‘ + @DBToScan + ‘];
    INSERT INTO [‘ + @CurrentDatabase
    + ‘].dbo.dbCompressionEstimates
    (DatabaseName
    ,schema_name
    ,object_name
    ,index_id
    ,ixName
    ,ixType
    ,partition_number
    ,data_compression_desc
    ,AlreadyProcessed
    ,StatusText)
    SELECT ”’ + @DBToScan + ”’
    ,S.name
    ,O.name
    ,I.index_id
    ,I.name
    ,I.type_desc
    ,P.partition_number
    ,P.data_compression_desc
    ,0 AS AlreadyProcessed
    ,”Initial load” AS StatusText
    FROM [‘ + @DBToScan + ‘].sys.schemas AS S
    JOIN [‘ + @DBToScan
    + ‘].sys.objects AS O ON S.schema_id = O.schema_id
    JOIN [‘ + @DBToScan
    + ‘].sys.indexes AS I ON O.object_id = I.object_id
    JOIN [‘ + @DBToScan
    + ‘].sys.partitions AS P ON I.object_id = P.object_id
    AND I.index_id = P.index_id
    WHERE O.TYPE = ”U”
    AND P.data_compression_desc = ”NONE”
    AND P.rows > 0;’ — only look at objects with data
    EXEC (@SQL)
    END
    — Determine Compression Estimates
    DECLARE @PK INT
    ,@DatabaseName SYSNAME
    ,@Schema VARCHAR(150)
    ,@object VARCHAR(150)
    ,@DAD VARCHAR(25)
    ,@partNO VARCHAR(3)
    ,@indexID VARCHAR(3)
    ,@ixName VARCHAR(250)
    ,@ixType VARCHAR(50)
    ,@Recommended_Compression VARCHAR(10);

    DECLARE cCompress CURSOR FAST_FORWARD
    FOR
    SELECT schema_name
    ,object_name
    ,index_id
    ,partition_number
    ,data_compression_desc
    ,ixName
    FROM dbo.dbCompressionEstimates
    WHERE (None_size IS NULL
    OR Row_Size IS NULL
    OR Page_size IS NULL)
    AND DatabaseName = @DBToScan;

    OPEN cCompress;

    FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD, @ixName;

    WHILE @@Fetch_Status = 0
    BEGIN
    /* evaluate objects with no compression */
    IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
    BEGIN
    /* First, make sure the table / index still exists (in case this
    process is run over multiple days */

    IF @indexID = 0
    BEGIN /* heaps */
    SET @CheckString = 'IF object_ID('''
    + @DBToScan + '.' + @Schema + '.'
    + @object
    + ''') IS NULL
    BEGIN
    SELECT @TestResultOUT = ''Does Not Exist''
    END
    ELSE
    BEGIN
    SELECT @TestResultOUT = ''Exists''
    END';
    END
    ELSE
    BEGIN /* indexes */
    SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
    + @DBToScan
    + '].[sys].[indexes] WHERE [name] ='''
    + @ixName + ''' AND OBJECT_ID('''
    + '[' + @DBToScan + ']' + '.['
    + @Schema + '].[' + @object + ']'''
    + ') = [object_id])
    BEGIN
    SELECT @TestResultOUT = ''Does Not Exist''
    END
    ELSE
    BEGIN
    SELECT @TestResultOUT = ''Exists''
    END';
    END

    SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';

    EXECUTE sp_executesql
    @CheckString
    ,@ParmDefinition
    ,@TestResultOUT = @TestResult OUTPUT;
    IF @TestResult = 'Exists'
    BEGIN

    IF @DAD = 'none'
    BEGIN
    /* estimate Page compression */
    SET @SQL = 'USE [' + @DBToScan
    + '];
    INSERT ##ObjEst
    (object_name
    ,schema_name
    ,index_id
    ,partition_number
    ,size_with_current_compression_setting
    ,size_with_requested_compression_setting
    ,sample_size_with_current_compression_setting
    ,sample_size_with_requested_compresison_setting)
    EXEC sp_estimate_data_compression_savings
    @schema_name = [' + @Schema
    + ']
    ,@object_name = [' + @object
    + ']
    ,@index_id = ' + @indexID
    + '
    ,@partition_number = '
    + @partNO
    + '
    ,@data_compression = ''PAGE'';'

    EXEC (@SQL)

    UPDATE dbo.dbCompressionEstimates
    SET none_size = O.size_with_current_compression_setting
    ,page_size = O.size_with_requested_compression_setting
    ,StatusText = 'Compression estimate 50% complete'
    FROM dbo.dbCompressionEstimates D
    JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
    AND D.Object_name = O.object_name
    AND D.index_id = O.index_id
    AND D.partition_number = O.partition_number;

    DELETE ##ObjEst;

    — estimate Row compression
    SET @SQL = 'USE [' + @DBToScan
    + '];
    INSERT ##ObjEst
    (object_name
    ,schema_name
    ,index_id
    ,partition_number
    ,size_with_current_compression_setting
    ,size_with_requested_compression_setting
    ,sample_size_with_current_compression_setting
    ,sample_size_with_requested_compresison_setting)
    EXEC sp_estimate_data_compression_savings
    @schema_name = [' + @Schema
    + ']
    ,@object_name = [' + @object
    + ']
    ,@index_id = ' + @indexID
    + '
    ,@partition_number = '
    + @partNO
    + '
    ,@data_compression = ''ROW'';'
    EXEC (@SQL)

    UPDATE dbo.dbCompressionEstimates
    SET row_size = O.size_with_requested_compression_setting
    ,StatusText = 'Compression estimate 100% complete'
    FROM dbo.dbCompressionEstimates D
    JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
    AND D.Object_name = O.object_name
    AND D.index_id = O.index_id
    AND D.partition_number = O.partition_number;

    DELETE ##ObjEst;
    END /* end evaluating objects with no compression */
    END
    ELSE /* table or index no longer exists */
    BEGIN
    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = 'Object no longer exists at compression estimate stage'
    WHERE schema_name = @Schema
    AND object_name = @object
    AND index_id = @indexID
    AND partition_number = @partNO
    AND data_compression_desc = @DAD
    AND DatabaseName = @DBToScan;
    END

    FETCH NEXT FROM cCompress INTO @Schema, @object,
    @indexID, @partNO, @DAD, @ixName
    END — end time check block
    ELSE
    BEGIN
    PRINT 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting…';
    CLOSE cCompress
    DEALLOCATE cCompress
    DROP TABLE ##ObjEst
    RETURN
    END
    END — end while loop

    CLOSE cCompress
    DEALLOCATE cCompress

    PRINT 'Initial scan complete.'
    –END
    /* End evaluating compression savings. Now do the actual compressing. */

    PRINT 'Beginning compression.';

    /* Do not process objects that do not meet our compression criteria */

    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = 'Best compression method less than minCompression threshold'
    WHERE (1 – (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression
    AND (Row_Size 0
    AND AlreadyProcessed = 0;

    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = ‘Best compression method less than minCompression threshold’
    WHERE (1 – (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression
    AND (Page_Size 0
    AND AlreadyProcessed = 0;

    /* Do not set compression on empty objects */

    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = ‘No data in table to compress’
    WHERE None_size = 0
    AND AlreadyProcessed = 0;

    — set the compression
    DECLARE cCompress CURSOR FAST_FORWARD
    FOR
    SELECT DatabaseName
    ,schema_name
    ,object_name
    ,partition_number
    ,ixName
    ,ixType
    ,CASE WHEN (1 – (CAST(Row_Size AS FLOAT) / none_Size)) >= @minCompression
    AND (Row_Size = @minCompression
    AND (Page_Size <= row_Size) THEN 'Page'
    ELSE 'None'
    END AS Recommended_Compression
    ,PK
    FROM dbo.dbCompressionEstimates
    WHERE None_Size 0
    AND (CASE WHEN (1 – (CAST(Row_Size AS FLOAT)
    / none_Size)) >= @minCompression
    AND (Row_Size = @minCompression
    AND (Page_Size <= row_Size) THEN 'Page'
    ELSE 'None'
    END data_compression_desc)
    AND AlreadyProcessed = 0
    ORDER BY None_Size ASC; /* start with smallest tables first */

    OPEN cCompress

    FETCH cCompress INTO @DatabaseName, @Schema, @object, @partNO, @ixName,
    @ixType, @Recommended_Compression, @PK — prime the cursor;

    WHILE @@Fetch_Status = 0
    BEGIN

    IF @ixType = ‘Clustered’
    OR @ixType = ‘heap’
    BEGIN
    SET @SQL = ‘USE [‘ + @DatabaseName + ‘];
    ALTER TABLE [‘ + @Schema + ‘].[‘ + @object
    + ‘] Rebuild with (data_compression = ‘
    + @Recommended_Compression
    + ‘, SORT_IN_TEMPDB=ON)’;

    SET @CheckString = ‘IF object_ID(”’
    + @DatabaseName + ‘.’ + @Schema + ‘.’ + @object
    + ”’) IS NULL
    BEGIN
    SELECT @TestResultOUT = ”Does Not Exist”
    END
    ELSE
    BEGIN
    SELECT @TestResultOUT = ”Exists”
    END’;
    END

    ELSE /* non-clustered index */
    BEGIN
    SET @SQL = ‘USE [‘ + @DatabaseName + ‘];
    ALTER INDEX [‘ + @ixName + ‘] on [‘ + @Schema
    + ‘].[‘ + @object
    + ‘] Rebuild with (data_compression = ‘
    + @Recommended_Compression
    + ‘,SORT_IN_TEMPDB=ON)’;

    SET @CheckString = ‘IF NOT EXISTS (SELECT 1 FROM [‘
    + @DBToScan
    + ‘].[sys].[indexes] WHERE [name] =”’
    + @ixName + ”’ AND OBJECT_ID(”’ + ‘[‘
    + @DBToScan + ‘]’ + ‘.[‘ + @Schema + ‘].[‘
    + @object + ‘]”’ + ‘) = [object_id])
    BEGIN
    SELECT @TestResultOUT = ”Does Not Exist”
    END
    ELSE
    BEGIN
    SELECT @TestResultOUT = ”Exists”
    END’;
    END

    IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
    BEGIN
    IF @ExecuteCompressCommand = 1
    BEGIN

    /* verify that table / index still exists before doing anything */

    SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
    EXECUTE sp_executesql
    @CheckString
    ,@ParmDefinition
    ,@TestResultOUT = @TestResult OUTPUT;
    IF @TestResult = 'Exists'
    BEGIN

    UPDATE dbo.dbCompressionEstimates
    SET StatusText = 'Compressing data…'
    WHERE PK = @PK;

    PRINT 'Compressing table/index: '
    + @Schema + '.' + @object
    + ' in database '
    + @DatabaseName;
    EXEC sp_executesql
    @SQL;

    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = 'Compression complete'
    WHERE PK = @PK;

    SET @CompressedCount = @CompressedCount
    + 1;
    END
    ELSE
    BEGIN
    UPDATE dbo.dbCompressionEstimates
    SET AlreadyProcessed = 1
    ,StatusText = 'Object no longer exists at compression stage'
    WHERE PK = @PK;
    END
    END
    ELSE
    BEGIN
    PRINT 'Command execution not enabled. Command is:'
    + @SQL;
    END
    END
    ELSE
    BEGIN
    PRINT 'Max runtime reached. Some compression performed. Exiting…';
    CLOSE cCompress
    DEALLOCATE cCompress
    DROP TABLE ##ObjEst
    BREAK
    END

    FETCH cCompress INTO @DatabaseName, @Schema, @object,
    @partNO, @ixName, @ixType, @Recommended_Compression,
    @PK;
    END

    CLOSE cCompress;
    DEALLOCATE cCompress;

    PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);
    DROP TABLE ##ObjEst
    RETURN

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.