Enabling Data Compression – November 2015 Update

It never fails. You work on something, be it a painting, a story, or even a SQL script, and tweak it until you’ve got it just right and, finally, after all your hard work, you publish it. Then you immediately find something you want to change.

Guess what? The very day my updated data compression script was published here, I found something I wanted to change. I mean, the thing hadn’t even been out for 2 hours yet, for cryin’ out loud!

But I discovered a new command that I just had to implement.

I had just completed enabling compression in a database and had shrunk it to reclaim some of the space. After that, of course, I had to defragment it, since the shrink process completely fragments the database. I was using Ola’s index maintenance script and noticed it was printing out messages as it ran. My scripts, which use either PRINT statements or SELECT statements to print out status messages, usually don’t display anything to the SSMS results window until the whole script finished. I decided to investigate how Ola was managing this feat.

Turns out, it’s not really a big secret. You simply use the RAISERROR WITH NOWAIT command and specify a severity level of 10 or less. The low severity means SQL doesn’t abort with an error message and keeps running the script. The NOWAIT instructs SQL to output the message to the SSMS Message pane immediately. Very handy for outputting progress updates for long running scripts.

Of course, this was a new shiny and I had to use it. I’ve updated my compression script to use this method to output messages. In addition to converting the existing messages, I added some additional progress messages as well. Full details on how to use the script can be found here.

This may not be a new concept to you, but it was to me. It’s always fun to learn something new!

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)

    - SJS 4/14/14
    - Another fix to handle tables with spaces in their names
 
    - SJS 8/12/15
    - Put []s around table and schema names in estimate page and row compression commands
    - Added edition check

    - SJS 10/13/15
    - Changed PRINT statements to RAISERROR WITH NOWAITs to output info immediately during execution
    - Fixed bug where error was generated when max runtime exceeded


*/

      DECLARE @EditionCheck VARCHAR(50);
      DECLARE @ProgressMessage VARCHAR(2044);

      SELECT    @EditionCheck = CAST(SERVERPROPERTY('Edition') AS VARCHAR(50));
      IF LEFT(@EditionCheck, 10) <> 'Enterprise'
         AND LEFT(@EditionCheck, 9) <> 'Developer'
         BEGIN
               PRINT 'Database compression is only supported on Enterprise and Developer editions '
                     + 'of SQL Server. This server is running '
                     + @EditionCheck + '.';
               RETURN;
         END;



      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
               SET @ProgressMessage = 'No unprocessed items found. Starting new scan.';
               RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
               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
      SET @ProgressMessage = 'Beginning compression estimate phase.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
      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'';';

                                            SET @ProgressMessage = 'Estimating PAGE compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            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'';';

                                            SET @ProgressMessage = 'Estimating ROW compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            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
                                    SET @ProgressMessage = 'Encountered table or index that no longer exists at compression estimate stage. Skipping.';
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                    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
                           SET @ProgressMessage = 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
            END; -- end while loop
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Compression estimate scan complete.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      SET @ProgressMessage = 'Beginning compression.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
 
  /* Do not process objects that do not meet our compression criteria */
 
      SET @ProgressMessage = 'Skipping empty objects and objects that do not meet '
          + 'the minimum compression threshold.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      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;
 
                                            SET @ProgressMessage = 'Compressing table/index: '
                                            + '[' + @ixName
                                            + '] on ['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '] in database '
                                            + @DatabaseName;

                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            EXEC sp_executesql
                                            @SQL;
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Compression complete'
                                            WHERE
                                            PK = @PK;
 
                                            SET @CompressedCount = @CompressedCount
                                            + 1;
                                       END;
                                    ELSE
                                       BEGIN
                                            SET @ProgressMessage = 'Encountered table or index that no longer exists at compression stage. Skipping.';
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression stage'
                                            WHERE
                                            PK = @PK;
                                       END;
                              END;
                           ELSE
                              BEGIN

                                    SET @ProgressMessage = 'Command execution not enabled. Command is:'
                                        + @SQL;
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                              END;
                     END;
                  ELSE
                     BEGIN
                           SET @ProgressMessage = 'Max runtime reached. Some compression performed. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
 
                  FETCH cCompress INTO @DatabaseName,
                        @Schema, @object, @partNO, @ixName,
                        @ixType, @Recommended_Compression,
                        @PK;
            END;
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Objects compressed: '
          + CONVERT(VARCHAR(10), @CompressedCount);
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      DROP TABLE ##ObjEst;
      RETURN;

Share

2 thoughts on “Enabling Data Compression – November 2015 Update

  1. Dear Shuan,

    I am using your very nice and good script for data compression: I love it!!
    Thanks a million for this.

    It is working very nicely, but the script or the stored procedure of Microsoft does not support/work with Sparse column.

    Is this something that could be checked in the next version of the script?

    See message below:

    Estimating ROW compression for [ADAMDB].[dbo].[tblRECORDINDEXEDFIELDS], index id 6, partition number 1
    Msg 15662, Level 16, State 1, Procedure sp_estimate_data_compression_savings, Line 86
    Compressing tables with sparse columns or column sets is not supported by the stored procedure sp_estimate_data_compression_savings.

    Blessings,

    Dick H.

  2. Ah..Nice find. I don’t have any databases with sparse columns, so I never ran into this issue. I’ll add it to the list of fixes to put in the next version. Thanks!

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.