Database Compression Evaluator

Should you compress this database? This script walks every user table, runs sp_estimate_data_compression_savings against it, and prints an ALTER TABLE ... REBUILD statement for any table that exceeds both your size threshold (default 1 GB uncompressed) and your compression ratio threshold (default 40%). The header comment explains the trade-off: page compression suits I/O-bound systems, none suits CPU-bound ones, and row is in between.

SET NOCOUNT ON;
GO

/*
Whether you should use row, page or no compression depends on how IO and processor bound your system is.
If you are extremely IO bound, you will probably find that maximum compression (page) will suit you.
If you are extremely processor bound, you will probably find that no compression will suit you. Row is somewhere inbetween.

ROW: replace fixed-length column data types with variable-length (so char into varchar)
PAGE: does ROW first then look for similar patterns to replace (prefix/dictionary), similar to Zip/text
*/

----------------------------------------------------BEGIN Configuration----------------------------------------
DECLARE @UncompressedSizeThreshold bigint			SET @UncompressedSizeThreshold = ((1024 * 1024)) * 1 --GB
DECLARE @CompressionRatioThreshold decimal(16, 2)	SET @CompressionRatioThreshold = 40
DECLARE @CompressionMethod VarChar(20)				SET @CompressionMethod         = 'ROW' --(PAGE oe ROW)
----------------------------------------------------END Configuration------------------------------------------

DECLARE @Schema sysname
DECLARE @Object sysname
DECLARE @UncompressedSize bigint
DECLARE @CompressedSize bigint
DECLARE @CompressionRatio decimal(16, 2)

DECLARE @Sizes TABLE
(
	[object_name] sysname,
	[schema_name] sysname,
	index_id int,
	partition_number int,
	[size_with_current_compression_setting (KB)] bigint,
	[size_with_requested_compression_setting (KB)] bigint,
	[sample_size_with_current_compression_setting (KB)] bigint,
	[sample_size_with_requested_compression_setting (KB)] bigint
)

DECLARE Tables CURSOR FAST_FORWARD FOR
	SELECT
		schema_name(schema_id),
		name
	FROM
		sys.objects
	WHERE
		type = 'U'
		and is_ms_shipped = 0
OPEN Tables

FETCH FROM Tables INTO @Schema, @Object

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	DELETE FROM @Sizes
	
	INSERT INTO @Sizes
	EXEC sp_estimate_data_compression_savings
		@schema_name = @Schema,
		@object_name = @Object,
		@index_id = null,
		@partition_number = null,
		@data_compression =  @CompressionMethod

	SELECT
		@UncompressedSize = sum([size_with_current_compression_setting (KB)]),
		@CompressedSize = sum([size_with_requested_compression_setting (KB)]),
		@CompressionRatio = (
					100 - cast((sum(([size_with_requested_compression_setting (KB)] + 0.0))
					/ 	(
							CASE WHEN sum([size_with_current_compression_setting (KB)] + 0.0) = 0
								THEN NULL
								ELSE sum([size_with_current_compression_setting (KB)] + 0.0)
							END
						)
					) * 100.0 as Decimal(16, 2))
					)
	FROM
		@Sizes
	GROUP BY
		[object_name],
		[schema_name]

	IF @UncompressedSize >= @UncompressedSizeThreshold AND @CompressionRatio >= @CompressionRatioThreshold
	BEGIN--IF
		PRINT '--[' + @Schema + '].[' + @Object + ']'
			+ ', Size: ' + Cast(@UncompressedSize as VarChar)
			+ ', Compressed Size: ' + Cast(@CompressedSize as VarChar)
			+ ', Ratio: ' + Cast(@CompressionRatio as VarChar) + '%'

		PRINT 'ALTER TABLE ' + '[' + @Schema + '].[' + @Object + ']' + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
	END--IF

	FETCH FROM Tables INTO @Schema, @Object
END--WHILE

CLOSE Tables DEALLOCATE Tables