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;