Shaun J Stuart

Just another SQL Server weblog

With the release of SP2 for SQL 2012, SQL Server Management Studio got a bug fix that results in a nice quality of life improvement. Since the initial release of SQL 2012, plain text in SSMS always showed up in teal, no matter what you set it to via the Tools --> Options --> Environment --> Fonts And Colors setting. This bug was filed in a Microsoft Connect item over two years ago. With SP2, it has finally been fixed. This isn't a big deal, but it does make things easier on my tired old eyes.

SSMS 2012 prior to SP2

SSMS 2012 prior to SP2

SSMS 2012 post SP2

SSMS 2012 post SP2

Share

I'm offering a summer school discount of 50% on my Udemy online SQL Server courses!

SQL101

Microsoft SQL Server 101

SQLCompression

​Understanding Data​ Compression In SQL Server

sql server maintenance plans

​SQL Server Maintenance Plans​

 

Share

DBAs gained the ability to disable indexes in SQL Server 2005. This is a handy little option when you are tuning indexes or consolidating indexes. When you disable an index, you tell SQL Server to basically pretend the index doesn't exist. Data changes made to the underlying tables are not reflected in the disabled index. As a result, you can sometimes improve performance by disabling unused indexes.

Disabling an index is not the same as dropping an index. When you drop an index, the index and its definition are deleted from the database. When you disable an index, the index definition is retained in the system catalog and you can still see the index in Management Studio. This may lead you to believe the index data remains as well and that it is just no longer updated with data changes. In actuality, when you disable an index, although the index definition remains in the database, the actual index data is deleted from the database. Indeed, the BOL entry for the ALTER INDEX statement, which is what you use to disable an index, states: "Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data." This is actually handy if you are unsure if any code is referencing the index. Just dropping the index would cause such references to fail. Disabling the index will also cause them to fail, but because the index definition is retained, it's an easy matter to recreate the index.

Let's verify this by running a test. First, we'll create a test database and make a table with a clustered index.

USE [master]
GO

CREATE DATABASE [Demo]
GO
ALTER DATABASE [Demo] SET RECOVERY SIMPLE
GO

USE Demo

CREATE TABLE [dbo].[BigTable](
[PK] [int] IDENTITY(1,1) NOT NULL,
[ColA] [varchar](100) NULL,
[ColB] [varchar](100) NULL,
[ColC] [varchar](100) NULL,
[ColD] [varchar](100) NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[BigTable]
(
[PK] ASC
) ON [PRIMARY]
GO

 

 

Now, let's add 10,000 rows of data:

INSERT INTO BigTable
(ColA,
ColB,
ColC,
ColD)
VALUES
(newid(),
newid(),
newid(),
newid())
GO 10000

 

 

Let's look at how much space this table takes up.

Now, we'll add a large index - an index on ColA and including ColB, ColC, and ColD. (Note this is not a good indexing strategy - adding every column in a table to an index. I'm doing it here simply to make a large index.)

CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[BigTable]
(
[ColA] ASC
)
INCLUDE (     [ColB],
[ColC],
[ColD])

GO

 

 

Once again, let's check the size of the table and indexes:

We can see that, compared to the sizes before we added the non-clustered index, our index_size increased by 1704 KB.

Now, let's disable the index and see what happens to our sizes:

ALTER INDEX [NonClusteredIndex] ON [dbo].[BigTable] DISABLE
GO

 

As you can see, the index_size dropped back down to what it was before we added the non-clustered index. When we disabled the index, SQL deleted the underlying index data. (If you use the sp_spaceused command without the table name, you will see results for the entire database and you will can verify that the data has indeed been removed from the database.)

When you think about what is going on, you can understand why there is no ENABLE INDEX command. To re-enable a disabled index, you issue an index rebuild command. There is no index data present to re-enable - SQL must recreate it.

The exception to this behavior is when you disable a clustered index. Because the clustered index actually is the table data, SQL cannot delete it. Instead, when you disable the clustered index on the table, the table becomes unavailable.

ALTER INDEX [ClusteredIndex] ON [dbo].[BigTable] DISABLE
GO

You can see that, even though the clustered index has been disabled, the data is still present:

 

 

However, attempting to access the table will generate an error:

So why is this information useful?

I ran into a problem the other day with one of my production databases. This database had been a constant size for months. Suddenly, one day I got a warning that the drive the database was on was running out of disk space. I did some investigating and narrowed down the cause of the file growth to a particular table in the database. The table was a log table and records were being written to it and deleted every day.

My first thought was perhaps we had an increase in activity and more records were being inserted. Luckily, the records included a datestamp, so I was able to look at the number of rows being inserted per day. That number was relatively constant over the past couple months, so that was not the cause of the growth.

Two of the columns in the table were XML data types, so my next thought was perhaps the XML data that was being logged had grown. I checked with our developers and they did not make any changes to the XML data being logged. I also used the DATALENGTH function and verified the average data size of the two fields had remained  the same over the past several months.

So what was going on? Why was my table growing? I needed to get an answer quickly. This table had 26 million rows in it and it was suddenly growing by about 300 MB per day where it had been a constant size previously.

I started running sp_spaceused on a regular basis and writing the output to a table. From here, I was able to see the index space used was very close to the data space used and was growing at about the same rate as the data. Hmmm.

That led me to take a closer look at the indexes. I found a newly added index! This index was on two columns, but had all the other columns of the table included in the index as INCLUDED columns. So this index basically doubled the size of the table. I had found my culprit! It just so happened that while I was digging into this, a developer was online rebuilding this index, so I was able to find out the person responsible for the index. After a brief visit, I found out he was attempting to resolve some performance issues. I gave some advice (including to test on a development system, not production), and I disabled the index. (I didn't just drop the index because we were worried other processes might be referencing it and we wanted to make sure nothing broke before we got rid of it altogether.) Once the index was disabled, the index data was removed from the database, and my file growth stopped.

My next step was to investigate how a developer got DDL access to a production database.

 

Share

I'm pleased to announce my new online course is now up and available: SQL Server Maintenance Plans. This course is almost 2 hours long and covers every aspect of SQL Server's maintenance plans. You'll learn what each and every task does and you'll see examples of how to create plans using both the Maintenance Plan Wizard and the Maintenance Plan Designer. The course also briefly covers setting up SQL Agent, Database Mail, and alerts.

sql server maintenance plans

From now until the end of April, the first 100 people to sign up using this link (or coupon code PLAN50) will get a 50% discount. Starting May 1, all readers of this blog can use the coupon code BLOG15OFF for a 15% discount.

Don't forget - my other courses are still available:

Microsoft SQL Server 101. Use this link for a 15% discount.

Understanding Data Compression in SQL Server. Use this link for a 15% discount.

Share

Thanks for commenter Srini for catching another problem in how tables with spaces in their names were being handled. This version fixes that.

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

                           SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                           
						   EXECUTE sp_executesql
                            @CheckString
                           ,@ParmDefinition
                           ,@TestResultOUT = @TestResult OUTPUT;
                           IF @TestResult = 'Exists'
                              BEGIN
 
                                    IF @DAD = 'none'
                                       BEGIN
                                    /* estimate Page compression */
                                             SET @SQL = 'USE [' + @DBToScan
                                                 + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = [' + @Schema
                                                 + ']
                                                   ,@object_name = [' + @object
                                                 + ']
                                                   ,@index_id = ' + @indexID
                                                 + '
                                                   ,@partition_number = '
                                                 + @partNO
                                                 + '
                                                   ,@data_compression = ''PAGE'';'

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

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