The Case Of The Multi-Column Auto-Created Statistic

I’ve been spending some time lately working with statistics, specifically auto-created statistics. When auto-create statistics is enabled, SQL Server will create column statistics when it needs them. No surprises there. Conventional wisdom also states that SQL Server never creates multi-column statistics, only single column statistics. In fact, this MSDN page says this of the auto create statistics option:

“It applies strictly to single-column statistics for the full table.”

Seems pretty clear. SQL Server will not automatically create multi-column statistics. I’ve heard this over and over from many different sources.

So imagine my surprise when, while testing some code, I came upon entries in the sys.stats_column system table that looked like this:

MultiColAutoCreated

Wow.. That sure looks like three auto-created, multi-column statistics! We have three stats: stats_ids 3, 4, and 5. The sys.stats_column table contains one row for each column that is in a statistic, so multiple rows for a single statistic (i.e., a single stats_id value), indicate multiple columns in that stat. Indeed, the column_id values indicate the table columns contained the stat. So stats_id 3 contains columns VersionMajor and ApplicationID (column_ids 3 and 1), stats_id 4 contains columns VersionMinor and ApplicationID (column_ids 4 and 1), and stats_id 5 contains columns VersionRevision and ApplicationID (column_ids 5 and 1). And, clearly, the auto_created flag is true, so these three stats were auto-created. What’s going on?

I puzzled over this for quite some time got nowhere. I finally reached out to the SQL Server community for help. Erin Stellato (b | t) said she had never seen that before, but she did mention the naming of the statistics seemed strange. I noticed this as well. As Paul Randal has blogged, auto-created statistics are named as _WA_Sys_<hex value of column>_<hex value of table>. Typically, the names look something like _WA_Sys_0000000D_00000029.

But these stats don’t follow that format. Instead of the hex value for the column, they have the column name.

A couple hours later, Paul White (b | t) responded to my tweet:

PWTweet

Aha! It seemed highly likely that this was the cause of what I was seeing. Although I was working with a database on a SQL 2012 server, I was pretty sure that database was originally created on an older version of SQL. I wanted to confirm this however.

To verify what SQL version the database was created with, I used the DBCC DBINFO command. The value of the dbi_createVersion field indicates what SQL version was used to create the database.

dbccdbinfo

Thanks to Paul Randal’s TechNet post, we know that a value of 539 means the database was created with SQL 2000. Looks like Paul White might be on to something.

The next piece I wanted to verify was that auto create stats for SQL 2000 included the table’s clustering key. Unfortunately, deep dive details for auto-create stats for SQL 2000 are somewhat hard to come by these days and I was not able to find anything that definitely stated the clustering key was included. However, in all the auto-created stats in all the old databases I found on my servers, this was the case. I also verified that if the clustering key was more than one column, all the clustering columns were included in the auto created stats. I’m going to go ahead and assume this is the way it worked back in the day.

And finally, I wanted to find something confirming the naming convention. Again, details were hard to find, but I did find this ebook which states that the names of the auto-created statistics in SQL 2000 use the column name, not the hex value of the column.

This is enough evidence for me, so I’m going to call it: I’m going to go ahead and say that auto created column statistics can be created on multiple columns. Granted, you have to be running SQL 2000 and the additional columns are always the clustering key columns, so it’s very specific case and it no longer happen in modern versions of SQL Server.

Still, if you are ever asked if SQL Server will automatically create multi-column statistics, you can now give the classic SQL Server answer: It depends :-)

If SQL doesn’t behave this way anymore, why is this important? This issue caught my eye when I was writing some code to examine the statistics tables and when I joined to the sys.stats_columns table, I was getting back more rows than I expected. Because I was joining to other tables as well, this messed up my final result set. If you’ve got databases that have been migrated from older versions of SQL, you might run into this problem as well. Come back next week for the story on that…

 

P.S. Just for kicks, I deleted one of the old, two column stats and ran a query so that a new stat would be created. Would the new statistic follow the old or new SQL Server rule? Turns out, not surprisingly, the newly auto-created stat followed the new rules: it was single column, did not include the clustering key, and followed the new naming convention using the hex value for the column name. This was tested using SQL 2012.

I also wanted to see what would happen if the database was running in SQL 2000 compatibility mode. On a SQL 2005 server, I found a database that was created in SQL 2000 and had some of these strange auto-created stats. I changed the database to SQL 2000 compatibility mode, deleted one of the auto-created stats, and ran a query to force a new one to be created. The result? A single column stat that did not include the clustering key and that was named using the hex id of the column. In other words, the stat was made using the method we see in newer versions of SQL Server. So the database compatibility mode does not affect this. The only way multi-column auto created stats can be generated is if you are using the SQL Server 2000 (or earlier, most likely) database engine.

Share

Viewing Statistics in SSMS

I’m in the process of creating a post about statistics and came across something in SQL Server Management Studio that was driving me crazy. I thought I’d post it here so that others might not waste an hour or two like I did puzzling over this.

When you create in index in SQL Server, SQL automatically creates a statistic for the columns in the index. In the screenshot below, you can see my table named SJSTest, to which I have added a clustered index called, somewhat unoriginally, ClusteredIndex. When you expand the Statistics folder, you can see SQL has automatically created a statistic with the same name as the index.

Screen1

The clustered index is composed of Col2, Col1, and Col3:

Screen2

We expect SQL Server to create a statistic with those columns and in that order. But if we look at the properties of the statistic we see this:

Screen3

What’s going on? Why are the columns in the statistic not in the same order as the columns in the index? Well, it turns out, they are. If we look on the Details page, we see the density vector is, in fact, created as Col2, Col1, Col3, which is the order of the columns in the index:

Screen4

At the time, I happened to be flipping back and forth between the property pages of indexes and statistics a lot and this really confused me. In the property page for an index, the columns are listed in the order the are in the index, but on the property page for statistics, the columns are displayed in the order they exist in the table.

I’m using SSMS version 13.0.15600.2. It would be nice if they made in the statistics property page behave the same as the index property page in a future version.

I have created a Connect item for this. If you agree, please vote for it.

Share

SQL 2016 May Need .Net Framework 3.5 After All

UPDATE: This has been fixed with SQL 2016 CU2 and SQL 2016 SP1 CU2.

UPDATE TO THE UPDATE: Today, July 3, 2018, I installed a brand new copy of SQL 2016 and patched it to SQL 2016 SP2 CU1. The .Net 3.5 framework was still not installed and I still had the same problems with database mail mentioned below until I manually installed it.

I’ve seen a lot of blog posts written about how SQL Server 2016 no longer requires .Net Framework 3.5 to be installed. This seems to be true to some extent; the SQL 2016 install program will no longer abort if it is not installed, like it did in previous versions.

And the SQL installer does not install the .Net Framework 3.5 on its own either. I installed SQL 2016 on three different machines and none of them had the .Net Framework installed before or after the SQL 2016 install completed.

However, I did run into one issue later on regarding database mail. I was able to configure it correctly and was even able to send a test email through SSMS without any errors. Unfortunately, the email was never delivered. Additionally, the database mail logs did not show database mail starting or attempting to send the message. I checked with my Exchange administrator and he said he never saw the SQL Server connecting to the mail server.

A check of the database mail table in msdb (sysmail_allitems) showed my messages sitting there with a status of Unsent.

After troubleshooting for a couple of hours and getting nowhere, we installed in the .Net Framework 3.5 and suddenly database mail started working.

This can be dangerous if you are relying on database mail to send alerts. I received no warnings or error messages when sending an email. All indications were that the mail was sent successfully. I only knew there was an issue because my standard build script sends an email and I knew to expect one when building a new server and I never got one.

I’ve not had much time yet to dig deeper into this and, as I mentioned, I’ve only tried this on 3 SQL Servers, but it has happened consistently on all three and the solution was the same on all three: installed .Net Framework 3.5. The root cause may turn out to be something else, but at least for right now, it seems SQL Server 2016 still needs .Net Framework 3.5 if you want database mail to function.

(And to clarify, I ran into this on three brand new installations. I have not done an in-place upgrade, so this might not occur in that situation.)

Share

Price Reductions On All My Online Courses!

I’m pleased to announce that prices have been permanently reduced on ALL my online SQL Server courses! Additionally, use the links below to save an additional 15% off the newly reduced prices!

 

Microsoft SQL Server 101 – This course is designed for new DBAs and involuntary DBAs or anyone who needs to learn the basics of SQL Server. The course can be found here at Udemy.com and includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is over 4 hours long and can be taken at your own pace.

Understanding Data Compression in SQL Server – This course delves into how SQL Server’s data compression feature works and how and when to implement it. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is approximately 1.5 hours long and can be taken at your own pace.

SQL Server Maintenance Plans – This course will teach you all about SQL Server’s maintenance plans and what they can (and can’t) do. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is almost 2 hours long and can be taken at your own pace.

Understanding Statistics in SQL Server – This course will teach you all about statistics in SQL Server – what they look like, how they are used, and how to maintain them. I also go over common problems caused by out of data statistics and how to find and fix them. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is almost 2 hours long and can be taken at your own pace.

Sample videos from my courses can be found on my YouTube channel here.

 

Share

Enabling Data Compression – January 2016 Update

Reader Dick H. posted a comment on my last version of this script stating that he got an error when this was run against tables containing sparse columns. Data compression does not support tables with sparse columns, so they should be excluded from this process. I’ve modified this script to correct this. I don’t have any tables with sparse columns in my environment, so thanks to Dick for pointing this out!

For instructions on using this script, look here.

 

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

    - SJS 1/4/16
    - Excluded tables with sparse columns (they do not support compression)

*/

      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 O.object_id NOT IN (SELECT object_id 
									FROM    [' + @DBToScan + '].sys.columns	WHERE is_sparse = 1)
                                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