Ever want to do a bulk analysis of the possible numbers of using page or row compression on a SQL Server 2008(+) Enterprise database? Now you can – easily.

This script came from http://sqlfool.com/2011/06/estimate-compression-savings/, so thanks for the great script!

SET NOCOUNT ON; 

DECLARE @printOnly BIT = 0 — change to 1 if you don’t want to execute, just print commands
, @tableName VARCHAR(256)
, @schemaName VARCHAR(100)
, @sqlStatement NVARCHAR(1000)
, @tableCount INT
, @statusMsg VARCHAR(1000);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#tables%’)
DROP TABLE #tables;

CREATE TABLE #tables
(
database_name sysname
, schemaName sysname NULL
, tableName sysname NULL
, processed BIT
);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
DROP TABLE #compressionResults;

IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
BEGIN

CREATE TABLE #compressionResults
(
objectName VARCHAR(100)
, schemaName VARCHAR(50)
, index_id INT
, partition_number INT
, size_current_compression BIGINT
, size_requested_compression BIGINT
, sample_current_compression BIGINT
, sample_requested_compression BIGINT
);

END;

INSERT INTO #tables
SELECT DB_NAME()
, SCHEMA_NAME([schema_id])
, name
, 0 — unprocessed
FROM sys.tables;

SELECT @tableCount = COUNT(*) FROM #tables;

WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)
BEGIN

SELECT TOP 1 @tableName = tableName
, @schemaName = schemaName
FROM #tables WHERE processed = 0;

SELECT @statusMsg = ‘Working on ‘ + CAST(((@tableCount – COUNT(*)) + 1) AS VARCHAR(10))
+ ‘ of ‘ + CAST(@tableCount AS VARCHAR(10))
FROM #tables
WHERE processed = 0;

RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;

SET @sqlStatement = ‘EXECUTE sp_estimate_data_compression_savings ”’
+ @schemaName + ”’, ”’ + @tableName + ”’, NULL, NULL, ”PAGE”;’ — ROW, PAGE, or NONE

IF @printOnly = 1
BEGIN

SELECT @sqlStatement;

END
ELSE
BEGIN

INSERT INTO #compressionResults
EXECUTE SP_EXECUTESQL @sqlStatement;

END;

UPDATE #tables
SET processed = 1
WHERE tableName = @tableName
AND schemaName = @schemaName;

END;

SELECT *
FROM #compressionResults;

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close