Enabling Data Compression In SQL Server

Not a recommended way to compress your database
This is how the pioneers compressed their databases

SQL 2008 saw the introduction of data compression to SQL Server. (I’m referring specifically to data compression, not backup file compression.) This Enterprise-only feature allows SQL Server to store data on disk and in memory in a compressed format. SQL can perform three types of data compression – no compression, row compression, or page compression. I’m not going to go into the details of each method, but in a nutshell (and seriously over-simplified), row compression changes fixed-length fields into variable length fields and page compression includes row compression and also compresses data that is common across fields in all the rows on a page. The Books Online Entry can be found here.

How do you know if you can benefit from data compression? Microsoft provides a stored procedure that will give you an estimate of how much space you can save using the various compression methods: sp_estimate_data_compression_savings. Before you go enabling data compression, there are a few things to keep in mind.

  • This is an Enterprise Edition (or Developer Edition) only feature. If you enable data compression in a database, you will only be able to restore that database to a SQL Server running Enterprise or Developer edition.  Keep this in mind when designing your disaster recovery plan.
  • Likewise, if you have any servers that will be accessing the compressed databases as a linked server, they will also need to be Enterprise or Developer editions. *UPDATE* This may not be true. See my test in the comments below.
  • Compression comes with a price. SQL will store the data in memory and on disk in compresed form, so you gain performance by having fewer disk I/Os and being able to cache more data in memory. The flipside, however, is you’ll need some extra CPU cycles to work with that data.
  • SQL data compression is completely transparent to the user or application accessing the database.

If you’ve evaluated the pros and cons of data compression and decided it’s something you want to implement, how do you go about doing it? SQL Server does not compress an entire database. Rather, it compresses tables and indexes (and indexed views and some other things – see the BOL link above for more details.) Enabling compression on an object requires rebuilding the object, much like an index rebuild. The TSQL commands are ALTER TABLE or ALTER INDEX, depending on which you are compressing. You include the DATA_COMPRESSION clause in the command.

“But Shaun,” I hear you howling, “my database has hundreds of tables!!! Are you telling me I have to manually compress each one?” Unfortunately, yes. Furthermore, there is no way to tell SQL Server that all future objects should be compressed, so if your database has tables being created frequently, you’ll need to look for uncompressed objects on a regular basis and compress them.

Luckily, compression can be performed in an online operation, meaning the objects are still available to users while the compression is happening. Unfortunately, if your table is a heap, the online compression will be single threaded, meaning it will be S-L-O-W. Multi-threaded compression for heaps only happens when SQL can have exclusive access to the table.

But back to that one-object-at-a-time issue. As all DBAs know, anything that has to be done one object at a time can be scripted and, thus, somewhat automated. I’m about to give you a script that will do this for you.

This script will created a stored procedure called db_compression. It is based on a script first written by Paul Nielsen several years ago. I’ve modified it a bit. The procedure takes three input paramters: @minCompression, @MaxRunTimeInMinutes, and @ExecuteCompressCommand. @minCompression is the minimum compression ratio threshold you want to hit before an object is compressed, expressed as a decimal. For example, if you only want to compress objects where you can achieve 25% compression, use a value of .25. @MaxRunTimeInMinutes is the maximum time the procecure will spend compressing objects. Note that this does NOT include the time it takes to perform the initial scan of all objects and calculate their estimated savings from compression. It is also not a hard and fast limit. The script only checks this time before each compress command is started, so it’s possible a large table may make the procedure run longer than the limit entered here. Finally, the last option is a flag that tells the script if it should actually execute the compression commands or just print them out.

How it works

When first run, the script will create a list of all user tables and indexes in the database. (System tables cannot be compressed.) It writes this list to a table called dbEstimate. There is a flag for each record to indicate if the row has been processed or not and it also notes if the object currently has some sort of compression enabled. Once this table is populated, the script will execute the sp_estimate_data_compression_savings procedure against it. It will run this two times for each record – to get the estimated sizes for the two compression settings the object currently does not have. For example, if a table already has page compression enabled, the script will get estimated sizes for row compression and no compression. This portion of the script is not limited by the @MaxRunTimeInMinutes parameter.

The next portion of the script does the actual work. The dbEstimate table is looped through with a cursor. The cursor will only look at objects that contain data and where the compression ratio exceeds the value specified in the @minCompression parameter. If the @ExecuteCompressCommand parameter is 1, the script will execute a command to compress the object using whichever method gives the greatest compression ratio. If the @ExecuteCompressCommand is 0, it will not perform the compression and simply output the command instead. The output also includes a count of how many objects were compressed.

What happens if all the objects selected for compression do not get compressed before the allotted time is up? If you re-run the procedure, it will detect that the dbEstimate table already exists and, if there are still items than have not been processed, it will pick up compressing those, skipping the initial scan portion (which is often time consuming). If, however, all the items in the table have been processed, the script will wipe out the table and re-scan the entire database. This is useful if you have many tables and / or indexes being created.

If an object is already compressed, the script will not change the object from the existing compression method to the other method (i.e. from page to row or vice-versa), even if the other method may result in greater compression. (This might be something to look into for the next iteration of the script, but I’m not sure if this situation would occur often enough to make the effort worthwhile.)

The procedure is hard-coded to use the SORT_IN_TEMPDB option. It will also start compressing the smallest objects first. It does not compress using the ONLINE option, mainly due to the single-threaded limitation with heaps mentioned above. If you want this to run in online mode, add that option to lines 415 and 421 below. You can use this procedure to compress a large database over multiple days by running it for a couple hours overnight or during times of minimal usage.

Compressing the database will not automatically decrease the size of the data file on the disk. If you want to do that, you’ll need to shrink the database (and perform the appropriate defragmentation process afterwards).


CREATE PROC [dbo].[db_compression]
      (
       @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
      ,@MaxRunTimeInMinutes INT = 60
      ,@ExecuteCompressCommand BIT = 0	/* 1 to execute command */
      )
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 indexs 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

*/

      DECLARE @CompressedCount INT;
      SET @CompressedCount = 0;

      DECLARE @StartTime DATETIME2;
      SET @StartTime = CURRENT_TIMESTAMP;

      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].[dbEstimate]')
                                AND type IN (N'U') )
         BEGIN
               CREATE TABLE dbEstimate
                      (
                       PK INT IDENTITY
                              NOT NULL
                              PRIMARY KEY
                      ,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
                      );
         END

  /*
 If all objects have been processed, rescan and start fresh. Useful for catching
  added objects since last scan and / or finding objects that were compressed
  initially with one method but now would do better with another method. But beware -
  this is I/O intensive and can take a while.
*/

      IF NOT EXISTS ( SELECT    1
                      FROM      dbEstimate
                      WHERE     AlreadyProcessed = 0 )
         BEGIN
               DELETE   FROM dbEstimate;

               INSERT   INTO dbEstimate
                        (schema_name
                        ,object_name
                        ,index_id
                        ,ixName
                        ,ixType
                        ,partition_number
                        ,data_compression_desc
                        ,AlreadyProcessed)
                        SELECT  S.name
                               ,o.name
                               ,I.index_id
                               ,I.name
                               ,I.type_desc
                               ,P.partition_number
                               ,P.data_compression_desc
                               ,0 AS AlreadyProcessed
                        FROM    sys.schemas AS S
                                JOIN sys.objects AS O ON S.schema_id = O.schema_id
                                JOIN sys.indexes AS I ON o.object_id = I.object_id
                                JOIN sys.partitions AS P ON I.object_id = P.object_id
                                                            AND I.index_id = p.index_id
                        WHERE   O.TYPE = 'U';

 -- Determine Compression Estimates
               DECLARE @PK INT
                      ,@Schema VARCHAR(150)
                      ,@object VARCHAR(150)
                      ,@DAD VARCHAR(25)
                      ,@partNO INT
                      ,@indexID INT
                      ,@ixName VARCHAR(250)
                      ,@SQL NVARCHAR(MAX)
                      ,@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
                       FROM     dbEstimate
                       WHERE    AlreadyProcessed = 0;

               OPEN cCompress;

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

               WHILE @@Fetch_Status = 0
                     BEGIN
							/* evaluate objects with no compression */
                           IF @DAD = 'none'
                              BEGIN
								/* estimate Page compression */
                                    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';

                                    UPDATE  dbEstimate
                                    SET     none_size = O.size_with_current_compression_setting
                                           ,page_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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
                                    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';

                                    UPDATE  dbEstimate
                                    SET     row_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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 */

							/* evaluate objects with row compression */
                           IF @DAD = 'row'
                              BEGIN
					           /* estimate Page compression */
                                    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';

                                    UPDATE  dbEstimate
                                    SET     row_size = O.size_with_current_compression_setting
                                           ,page_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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 None compression */
                                    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 = 'none';

                                    UPDATE  dbEstimate
                                    SET     none_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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 row compression */

							/* evalutate objects with page compression */
                           IF @DAD = 'page'
                              BEGIN
							/* estimate Row compression */
                                    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';

                                    UPDATE  dbEstimate
                                    SET     page_size = O.size_with_current_compression_setting
                                           ,row_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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 None compression */
                                    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 = 'none';

                                    UPDATE  dbEstimate
                                    SET     none_size = O.size_with_requested_compression_setting
                                    FROM    dbEstimate 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 page compression */

                           FETCH NEXT FROM cCompress INTO @Schema, @object,
                                 @indexID, @partNO, @DAD
                     END

               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    dbEstimate
      SET       AlreadyProcessed = 1
      WHERE     (1 - (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Row_Size <= Page_Size)                  AND None_Size > 0;

      UPDATE    dbEstimate
      SET       AlreadyProcessed = 1
      WHERE     (1 - (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Page_Size <= Row_Size)                 AND None_Size > 0;

  /* Do not set compression on empty objects */

      UPDATE    dbEstimate
      SET       AlreadyProcessed = 1
      WHERE     None_size = 0;

  /* Do not process objects that are already using an acceptable compression method */

      UPDATE    dbEstimate
      SET       AlreadyProcessed = 1
	  WHERE     (1 - (CAST(Page_Size AS FLOAT) / none_Size)) >= @minCompression
                AND (Page_Size <= Row_Size)                 AND None_Size > 0
		AND data_compression_desc = 'PAGE';

      UPDATE    dbEstimate
      SET       AlreadyProcessed = 1
      WHERE     (1 - (CAST(Row_Size AS FLOAT) / None_Size)) >= @minCompression
                AND (Row_Size <= Page_Size)                 AND None_Size > 0
		AND data_compression_desc = 'ROW';

	  /* Ignore tables that have not had their sizes calculated. This can occur if the procedure
			execution was cancelled before the initial scan completed. */

      UPDATE    dbEstimate
      SET       AlreadyProcessed = 1
      WHERE     None_Size IS NULL
                OR Row_Size IS NULL
                OR Page_Size IS NULL;

 -- set the compression
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    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      dbEstimate
              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 @Schema, @object, @partNO, @ixName, @ixType,
            @Recommended_Compression, @PK  -- prime the cursor;

      WHILE @@Fetch_Status = 0
            BEGIN

                  IF @ixType = 'Clustered'
                     OR @ixType = 'heap'
                     SET @SQL = 'ALTER TABLE ' + @Schema + '.' + @object
                         + ' Rebuild with (data_compression = '
                         + @Recommended_Compression + ', SORT_IN_TEMPDB=ON)';

                  ELSE
                     SET @SQL = 'ALTER INDEX ' + @ixName + ' on ' + @Schema
                         + '.' + @object
                         + ' Rebuild with (data_compression = '
                         + @Recommended_Compression + ',SORT_IN_TEMPDB=ON)';

                  IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                           IF @ExecuteCompressCommand = 1
                              BEGIN
                                    PRINT 'Compressing table/index: '
                                          + @Schema + '.' + @object;
                                    EXEC sp_executesql
                                        @SQL;

                                    UPDATE  dbEstimate
                                    SET     AlreadyProcessed = 1
                                    WHERE   PK = @PK;

                                    SET @CompressedCount = @CompressedCount + 1;
                              END
                           ELSE
                              BEGIN
                                    PRINT 'Command execution not enabled. Command is:'
                                          + @SQL;
                              END
                     END
                  ELSE
                     BEGIN
                           PRINT 'Max runtime reached. Exiting...';
                           BREAK
                     END

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

      CLOSE cCompress;
      DEALLOCATE cCompress;

      PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);

      DROP TABLE #ObjEst;

      RETURN

Update: I realized the above script calls a secondary stored procedure, db_compression_estimate, that was not provided. Here it is.

 

CREATE PROC [dbo].[db_compression_estimate]
AS 
       SET nocount ON 

-- estimates the row and page compression gain for every object and index in the database
-- Paul Nielsen
-- www.SQLServerBible.com 
-- March 13, 2008

-- to do:
-- [ ] transaction error from insert...Exec sp_estimate_data_compression_savings
-- [ ] filter objects to only those eligible for compression 

       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
              )
    
       CREATE TABLE #dbEstimate
              (
               PK INT IDENTITY
                      NOT NULL
                      PRIMARY KEY
              ,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
              )

       INSERT   INTO #dbEstimate
                (schema_name
                ,object_name
                ,index_id
                ,ixName
                ,ixType
                ,partition_number
                ,data_compression_desc)
                SELECT  S.name
                       ,o.name
                       ,I.index_id
                       ,I.name
                       ,I.type_desc
                       ,P.partition_number
                       ,P.data_compression_desc
                FROM    sys.schemas AS S
                        JOIN sys.objects AS O ON S.schema_id = O.schema_id
                        JOIN sys.indexes AS I ON o.object_id = I.object_id
                        JOIN sys.partitions AS P ON I.object_id = P.object_id
                                                    AND I.index_id = p.index_id
                WHERE   O.TYPE = 'U' 
              
 -- Determine Compression Estimates 
       DECLARE @PK INT
              ,@Schema VARCHAR(150)
              ,@object VARCHAR(150)
              ,@DAD VARCHAR(25)
              ,@partNO INT
              ,@indexID INT
              ,@SQL NVARCHAR(MAX)

       DECLARE cCompress CURSOR FAST_FORWARD
       FOR
               SELECT   schema_name
                       ,object_name
                       ,index_id
                       ,partition_number
                       ,data_compression_desc
               FROM     #dbEstimate
   
       OPEN cCompress
  
       FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD  -- prime the cursor

       WHILE @@Fetch_Status = 0 
             BEGIN
        
                   IF @DAD = 'none' 
                      BEGIN 
            -- estimate Page compression
                            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'
                
                            UPDATE  #dbEstimate
                            SET     none_size = O.size_with_current_compression_setting
                                   ,page_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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
                            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'
                
                            UPDATE  #dbEstimate
                            SET     row_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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 -- none compression estimate      
 
                   IF @DAD = 'row' 
                      BEGIN 
            -- estimate Page compression
                            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'
                
                            UPDATE  #dbEstimate
                            SET     row_size = O.size_with_current_compression_setting
                                   ,page_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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 None compression
                            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 = 'none'
                
                            UPDATE  #dbEstimate
                            SET     none_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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 -- row compression estimate     
      
                   IF @DAD = 'page' 
                      BEGIN 
            -- estimate Row compression
                            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'
                
                            UPDATE  #dbEstimate
                            SET     page_size = O.size_with_current_compression_setting
                                   ,row_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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 None compression
                            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 = 'none'
                
                            UPDATE  #dbEstimate
                            SET     none_size = O.size_with_requested_compression_setting
                            FROM    #dbEstimate 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 -- page compression estimate 
          
                   FETCH cCompress INTO @Schema, @object, @indexID, @partNO,
                         @DAD 
             END

       CLOSE cCompress
       DEALLOCATE cCompress
  
 -- report findings
       SELECT   schema_name + '.' + object_name AS [Object]
               ,index_id
               ,ixName
               ,ixType
               ,partition_number
               ,data_compression_desc AS Current_Compression
               ,CAST((1 - (CAST(Row_Size AS FLOAT) / none_Size)) * 100 AS INT) AS RowGain
               ,CAST((1 - (CAST(page_Size AS FLOAT) / none_Size)) * 100 AS INT) AS PageGain
               ,CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= .25
                          AND (Row_Size <= Page_Size) THEN 'Row'
                     WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= .25
                          AND (Page_Size <= row_Size) THEN 'Page'
                     ELSE 'None'
                END AS Recommended_Compression
       FROM     #dbEstimate
       WHERE    None_Size <> 0
       ORDER BY [Object]
   
       RETURN 
 

Share

4 thoughts on “Enabling Data Compression In SQL Server

  1. Hi Shaun,

    Good article. I have been trying to track down the effects of data compression when sending data across a linked server connection.

    You say, “Likewise, if you have any servers that will be accessing the compressed databases as a linked server, they will also need to be Enterprise or Developer editions.”

    Assuming I have two Enterprise Servers, one in Australia and One in the UK, and enable data compression on my table, would the data compression also lead to reduced traffic across the Linked server Connection?

    Thanks

    P.S. I have been trying unsuccessfully to find an answer online and have posted on the MS SQL Server forums here:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d6e3fc9-b3b3-42e8-b8eb-ed7f42c48eab/data-compression-over-linked-server-connections-sql-server-2012-enterprise-developer-editions

    1. I believe it will give reduced network traffic, but I have nothing to back up that claim. Perhaps a test is in order?

      *EDIT* I’m changing my answer here. I think the data will be sent uncompressed over the network. I ran the following test: I have database with a compressed table on an Enterprise 2012 SQL Server. On a 2008 R2 Standard SQL Server, I created a linked server entry to the Enterprise server. On the Standard server, I was able to do a SELECT * from the compressed table on the Enterprise server via the linked server entry. This would imply that the data is sent uncompressed over the network because Standard edition does not support compressed data.

  2. Thanks Shaun!

    Your out-of-the-box do-it-all (and even without looking) scripts were very welcome for reducing the footprint on my developer notebook. For me, space savings clearly outweigh performance concerns.

    Regards,
    Reiner

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.