It’s been about a year and a half since I last posted an update to my script that scans databases for objects to compress and, optionally, compresses them. This time around, I’ve just incorporated a bug fix and added a check to make sure the script is running on an edition of SQL Server that supports compression – namely Enterprise or Developer. That check is really useful because if you attempt to compress an object in a different version of SQL Server, you get an error message that doesn’t really tell you the underlying problem. Well, actually, it does but it’s just very easy to overlook:
I can’t tell you how many times I’ve seen that error message and struggled for quite some time trying to figure out what the problem was. All I needed to do was scroll the window to the right:
Ah! So that’s the problem! But I always seemed to forget to scroll over, so I added a check into the script that will now print out a message if the edition you are running on doesn’t support compression. (Really, Microsoft. Would a carriage return be too much to ask? And note that the error is not completely accurate – Developer edition also supports compression.)
The other minor bug fix was to make sure to put brackets around the schema and table names when running the estimates for PAGE and ROW compression.
For details on how this works and how to use it, see this post.
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
*/
DECLARE @EditionCheck VARCHAR(50);
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
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;
One thought on “Enabling Data Compression – October 2015 Update”