Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This script performs compression savings estimation check across the ENTIRE database within which it is executed.
-------------------------------------------------------
-------- Compression Savings Estimation Check ---------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-------------------------------------------------------
-- Change Log:
-- -----------
-- 2019-12-09 - added ONLINE rebuild option
-- 2019-12-24 - flipped to traditional ratio calculation; added READ UNCOMMITTED isolation level; added minimum difference thresholds for PAGE vs. ROW considerations
-------------------------------------------------------
-- Description:
-- ------------
-- This script performs compression savings estimation check for both PAGE and ROW
-- compression accross the ENTIRE DATABASE WITHIN WHICH IT IS EXECUTED.
-- For each index which passes the check, a corresponding ALTER INDEX command
-- is printed for you to use in order to apply the compression.
-- The script also compares the results of the PAGE and ROW estimations and automatically
-- selects the one with the better savings as the command to print, based on the provided thresholds.
-------------------------------------------------------
--
-- IMPORTANT !!!
-- -------------
--
-- 1. Don't forget to change the database context to the one you want to check.
--
-- 2. BE MINDFUL IN PRODUCTION ENVIRONMENTS !
--
-- - Running this script may take a very long time on big databases with many tables, and significant IO + CPU stress may be noticeable.
--
-- - Schema-level locks may be held for a while per each table, and will possibly block other sessions performing DDL operations.
--
-------------------------------------------------------
-- Parameters:
-- -----------
DECLARE
@CompressionRatioThreshold FLOAT = 65 -- Number between 0 and 100 representing the minimum compressed data ratio, relative to current size, for which a check will pass
,@CompressionSizeSavedKBThreshold FLOAT = 102400 -- Minimum estimated saved space in KB resulting from compression (affects both PAGE and ROW compressions)
,@MinimumRatioDifferenceForPage FLOAT = 10 -- Minimum difference in percentage between ROW and PAGE compression types, in order to deem PAGE compression preferable
,@MinimumSavingsKBDifferenceForPage FLOAT = 102400 -- Minimum difference in saved space in KB between ROW and PAGE compression types, in order to deem PAGE compression preferable
,@MinimumRows INT = 10000 -- Minimum number of rows in order to perform check
,@CheckPerPartition BIT = 0 -- If 1, will perform separate estimation check per partition
,@OnlineRebuild BIT = 1 -- If 1, will generate REBUILD commands with the ONLINE option turned on
,@SortInTempDB BIT = 1 -- If 1, will generate REBUILD commands with the SORT_IN_TEMPDB option turned on
-------------------------------------------------------
-- DON'T CHANGE ANYTHING BELOW THIS LINE --
-------------------------------------------------------
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Init local variables
DECLARE @Schema SYSNAME, @Table SYSNAME, @ObjectId INT, @IndexId INT, @Partition INT, @CompressionType VARCHAR(4)
DECLARE @Results AS TABLE (object_name SYSNAME, schema_name SYSNAME, index_id INT, partition_number INT, size_w_current_compression_KB FLOAT, size_w_requested_compression_KB FLOAT, sample_size_current_KB INT, sample_size_requested_KB INT)
DECLARE @RebuildOptions NVARCHAR(MAX) = N''
IF @OnlineRebuild = 1 SET @RebuildOptions = @RebuildOptions + N', ONLINE = ON'
IF @SortInTempDB = 1 SET @RebuildOptions = @RebuildOptions + N', SORT_IN_TEMPDB = ON'
-- Init temp table to hold final results
IF OBJECT_ID('tempdb..#ResultsAll') IS NOT NULL DROP TABLE #ResultsAll;
CREATE TABLE #ResultsAll (table_name NVARCHAR(1000), index_name NVARCHAR(500), partition_number INT NULL, compression_type VARCHAR(4), compression_ratio FLOAT, compression_size_saving_KB FLOAT, is_compression_recommended BIT)
-- Init cursor to traverse all un-compressed tables in the database
DECLARE TablesToCheck CURSOR
LOCAL FORWARD_ONLY FAST_FORWARD
FOR
SELECT
OBJECT_SCHEMA_NAME(p.object_id)
, OBJECT_NAME(p.object_id)
, p.object_id
, p.index_id
, CASE WHEN @CheckPerPartition = 1 THEN p.partition_number ELSE NULL END
, ct.CompressionType
FROM
sys.partitions AS p WITH(nolock)
CROSS JOIN
(VALUES('PAGE'),('ROW')) AS ct(CompressionType) -- check both ROW and PAGE compression for each
WHERE
data_compression = 0
AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY
p.object_id
, p.index_id
, CASE WHEN @CheckPerPartition = 1 THEN p.partition_number ELSE NULL END
, ct.CompressionType
HAVING
SUM(rows) >= @MinimumRows
ORDER BY
SUM(rows) DESC
OPEN TablesToCheck
FETCH NEXT FROM TablesToCheck INTO @Schema, @Table, @ObjectId, @IndexId, @Partition, @CompressionType
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR(N'--[%s].[%s] index %d partition %d compression type %s...',0,1,@Schema,@Table,@IndexId,@Partition,@CompressionType) WITH NOWAIT;
-- Calculate compression savings estimation
INSERT INTO @Results
EXEC sys.sp_estimate_data_compression_savings
@schema_name = @Schema,
@object_name = @Table,
@index_id = @IndexId,
@partition_number = @Partition,
@data_compression = @CompressionType;
-- Save to main results table
INSERT INTO #ResultsAll
SELECT
table_name = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
, index_name = QUOTENAME(ix.name)
, partition_number = @Partition
, compression_type = @CompressionType
, compression_ratio = 100 - (SUM(r.size_w_requested_compression_KB) * 100 / SUM(r.size_w_current_compression_KB))
, compression_size_saving_KB = SUM(r.size_w_current_compression_KB) - SUM(r.size_w_requested_compression_KB)
, is_compression_recommended = CASE WHEN
100 - (SUM(r.size_w_requested_compression_KB) * 100.0 / SUM(r.size_w_current_compression_KB)) >= @CompressionRatioThreshold
AND
SUM(r.size_w_current_compression_KB) - SUM(r.size_w_requested_compression_KB) >= @CompressionSizeSavedKBThreshold
THEN 1 ELSE 0 END
FROM
@Results AS r
INNER JOIN
sys.indexes AS ix
ON
ix.object_id = @ObjectId
AND ix.index_id = @IndexId
GROUP BY
ix.name
DELETE @Results;
FETCH NEXT FROM TablesToCheck INTO @Schema, @Table, @ObjectId, @IndexId, @Partition, @CompressionType
END
CLOSE TablesToCheck
DEALLOCATE TablesToCheck
-- Return results to client
SELECT
table_name
,index_name
,partition_number = ISNULL(CONVERT(VARCHAR,partition_number),'ALL')
,compression_type
,compression_ratio = ROUND(compression_ratio,3)
,compression_size_saving_KB
,is_compression_recommended = CASE WHEN is_compression_recommended = 1 THEN 'Yes' ELSE 'No' END
,remediation_command =
CASE WHEN is_compression_recommended = 0 THEN N'-- ' ELSE N'' END
+ N'ALTER ' + ISNULL(N'INDEX ' + index_name + N' ON ', N'TABLE ') + table_name + N' REBUILD PARTITION = ' + ISNULL(CONVERT(NVARCHAR(MAX),partition_number), N'ALL')
+ N' WITH (DATA_COMPRESSION = ' + compression_type + @RebuildOptions + N');'
FROM
#ResultsAll
ORDER BY
compression_size_saving_KB DESC
, compression_ratio DESC
-- Begin generating remediation script that takes into consideration all checks
-- including ROW vs. PAGE considerations
DECLARE @CMD NVARCHAR(MAX);
PRINT N'-----------------------------------------------------------------------'
PRINT N'---------- Recommendation Script Begins Below -------------------------'
PRINT N'-----------------------------------------------------------------------'
DECLARE Rebuilds CURSOR
LOCAL FORWARD_ONLY FAST_FORWARD
FOR
SELECT RemediationCmd
FROM
(
SELECT
RemediationCmd = N'ALTER ' + ISNULL(N'INDEX ' + index_name + N' ON ', N'TABLE ') + table_name + N'
REBUILD PARTITION = ' + ISNULL(CONVERT(nvarchar,partition_number), N'ALL') + N' WITH (DATA_COMPRESSION = ' + compression_type + @RebuildOptions + N');'
, table_name
, compression_size_saving_KB
, compression_ratio
, SavingsRating = ROW_NUMBER() OVER (
PARTITION BY
table_name
, index_name
ORDER BY
compression_ratio + (CASE WHEN compression_type = 'ROW' THEN @MinimumRatioDifferenceForPage ELSE 0 END) DESC,
compression_size_saving_KB + (CASE WHEN compression_type = 'ROW' THEN @MinimumSavingsKBDifferenceForPage ELSE 0 END) DESC
)
FROM
#ResultsAll
WHERE
is_compression_recommended = 1
) AS q
WHERE
SavingsRating = 1
ORDER BY
table_name ASC
, compression_size_saving_KB DESC
, compression_ratio DESC
OPEN Rebuilds
FETCH NEXT FROM Rebuilds INTO @CMD
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @CMD
PRINT N'GO'
FETCH NEXT FROM Rebuilds INTO @CMD
END
CLOSE Rebuilds
DEALLOCATE Rebuilds
PRINT N'-----------------------------------------------------------------------'
PRINT N'----------- Recommendation Script Ends Here --------------------------'
PRINT N'-----------------------------------------------------------------------'
@EitanBlumin

This comment has been minimized.

Copy link
Owner Author

EitanBlumin commented Jan 10, 2020

As pointed out in this article: https://www.sqlservercentral.com/blogs/introducing-what_to_compress-v2
I should add a cautionary parameter for max table size, in order to avoid abusing TempDB storage space.

@EitanBlumin

This comment has been minimized.

Copy link
Owner Author

EitanBlumin commented Jan 12, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.