-- =============================================
-- SQL Server Table and Index Compression Analysis Script
-- =============================================
-- This script identifies uncompressed tables and indexes,
-- then estimates space savings from PAGE compression
-- =============================================
SET NOCOUNT ON;
-- =============================================
-- Configuration: Minimum size threshold
-- =============================================
DECLARE @MinSizeMB DECIMAL(10,2) = 1.0; -- Only analyze objects larger than this size in MB
-- Clean up temp tables if they exist
IF OBJECT_ID('tempdb..#UncompressedObjects') IS NOT NULL
DROP TABLE #UncompressedObjects;
IF OBJECT_ID('tempdb..#CompressionEstimates') IS NOT NULL
DROP TABLE #CompressionEstimates;
-- =============================================
-- Step 1: Identify uncompressed tables and indexes
-- =============================================
CREATE TABLE #UncompressedObjects (
ID INT IDENTITY(1,1) PRIMARY KEY,
SchemaName SYSNAME NOT NULL,
TableName SYSNAME NOT NULL,
IndexID INT NOT NULL,
IndexName SYSNAME NULL,
IndexType VARCHAR(50) NOT NULL,
PartitionNumber INT NOT NULL,
CurrentCompression VARCHAR(20) NOT NULL,
TotalSpaceKB BIGINT NOT NULL,
TotalSpaceMB DECIMAL(18,2) NOT NULL
);
-- Get all uncompressed tables and their indexes
INSERT INTO #UncompressedObjects (
SchemaName,
TableName,
IndexID,
IndexName,
IndexType,
PartitionNumber,
CurrentCompression,
TotalSpaceKB,
TotalSpaceMB
)
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.index_id AS IndexID,
ISNULL(i.name, 'HEAP') AS IndexName,
i.type_desc AS IndexType,
p.partition_number AS PartitionNumber,
p.data_compression_desc AS CurrentCompression,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS DECIMAL(18,2)) AS TotalSpaceMB
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.is_ms_shipped = 0 -- Exclude system tables
AND t.object_id > 255 -- Exclude system objects
AND t.name NOT LIKE 'dt%' -- Exclude diagnostic tables
AND p.data_compression_desc = 'NONE' -- Only uncompressed objects
AND p.rows > 0 -- Only objects with data
GROUP BY
s.name,
t.name,
i.index_id,
i.name,
i.type_desc,
p.partition_number,
p.data_compression_desc
HAVING
SUM(a.total_pages) * 8 > (@MinSizeMB * 1024) -- Only objects larger than threshold
ORDER BY
TotalSpaceKB DESC;
-- Show what we found
DECLARE @ObjectCount INT = (SELECT COUNT(*) FROM #UncompressedObjects);
PRINT '=============================================';
PRINT 'Found ' + CAST(@ObjectCount AS VARCHAR(10)) + ' uncompressed objects';
PRINT '=============================================';
PRINT '';
-- =============================================
-- Step 2: Estimate compression savings
-- =============================================
CREATE TABLE #CompressionEstimates (
object_name SYSNAME NOT NULL,
schema_name SYSNAME NOT NULL,
index_id INT NOT NULL,
partition_number INT NOT NULL,
size_with_current_compression_KB BIGINT NOT NULL,
size_with_requested_compression_KB BIGINT NOT NULL,
sample_size_with_current_compression_KB BIGINT NOT NULL,
sample_size_with_requested_compression_KB BIGINT NOT NULL
);
DECLARE @ID INT;
DECLARE @SchemaName SYSNAME;
DECLARE @TableName SYSNAME;
DECLARE @IndexID INT;
DECLARE @PartitionNumber INT;
DECLARE @Counter INT = 0;
DECLARE @Total INT = (SELECT COUNT(*) FROM #UncompressedObjects);
-- Process each uncompressed object
DECLARE object_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT ID, SchemaName, TableName, IndexID, PartitionNumber
FROM #UncompressedObjects
ORDER BY TotalSpaceKB DESC;
OPEN object_cursor;
FETCH NEXT FROM object_cursor INTO @ID, @SchemaName, @TableName, @IndexID, @PartitionNumber;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter = @Counter + 1;
-- Progress indicator
IF @Counter % 10 = 0
PRINT 'Processing ' + CAST(@Counter AS VARCHAR(10)) + ' of ' + CAST(@Total AS VARCHAR(10)) + '...';
-- Estimate compression for this specific index/partition
BEGIN TRY
INSERT INTO #CompressionEstimates
EXEC sp_estimate_data_compression_savings
@schema_name = @SchemaName,
@object_name = @TableName,
@index_id = @IndexID,
@partition_number = @PartitionNumber,
@data_compression = 'PAGE';
END TRY
BEGIN CATCH
PRINT 'ERROR processing ' + @SchemaName + '.' + @TableName + ' (Index: ' + CAST(@IndexID AS VARCHAR(10)) + ')';
PRINT ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM object_cursor INTO @ID, @SchemaName, @TableName, @IndexID, @PartitionNumber;
END
CLOSE object_cursor;
DEALLOCATE object_cursor;
PRINT '';
PRINT 'Processing complete!';
PRINT '';
-- =============================================
-- Step 3: Display Results
-- =============================================
-- Detail view: All indexes and partitions
PRINT '=============================================';
PRINT 'DETAILED VIEW: Individual Indexes and Partitions';
PRINT '=============================================';
SELECT
uo.SchemaName,
uo.TableName,
uo.IndexID,
uo.IndexName,
uo.IndexType,
uo.PartitionNumber,
uo.CurrentCompression,
CAST(ROUND(ce.size_with_current_compression_KB / 1024.0, 2) AS DECIMAL(18,2)) AS CurrentSizeMB,
CAST(ROUND(ce.size_with_requested_compression_KB / 1024.0, 2) AS DECIMAL(18,2)) AS CompressedSizeMB,
CAST(ROUND((ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS SavingsMB,
CAST(ROUND(
(CASE
WHEN ce.size_with_current_compression_KB > 0
THEN (ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) * 100.0 / ce.size_with_current_compression_KB
ELSE 0
END), 2) AS DECIMAL(5,2)) AS SavingsPercent
FROM
#UncompressedObjects uo
INNER JOIN
#CompressionEstimates ce
ON uo.SchemaName = ce.schema_name
AND uo.TableName = ce.object_name
AND uo.IndexID = ce.index_id
AND uo.PartitionNumber = ce.partition_number
ORDER BY
SavingsMB DESC;
PRINT '';
PRINT '=============================================';
PRINT 'SUMMARY VIEW: Aggregated by Table';
PRINT '=============================================';
-- Summary view: Aggregated by table
SELECT
uo.SchemaName,
uo.TableName,
COUNT(DISTINCT uo.IndexID) AS IndexCount,
CASE
WHEN MAX(CASE WHEN uo.IndexID = 1 THEN 1 ELSE 0 END) = 1 THEN 'CLUSTERED'
WHEN MAX(CASE WHEN uo.IndexID = 0 THEN 1 ELSE 0 END) = 1 THEN 'HEAP'
ELSE ''
END AS HeapOrClustered,
SUM(CASE WHEN uo.IndexID > 1 THEN 1 ELSE 0 END) AS NonClusteredCount,
CAST(ROUND(SUM(ce.size_with_current_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS CurrentSizeMB,
CAST(ROUND(SUM(ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS CompressedSizeMB,
CAST(ROUND(SUM(ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS TotalSavingsMB,
CAST(ROUND(
(CASE
WHEN SUM(ce.size_with_current_compression_KB) > 0
THEN SUM(ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) * 100.0 / SUM(ce.size_with_current_compression_KB)
ELSE 0
END), 2) AS DECIMAL(5,2)) AS SavingsPercent
FROM
#UncompressedObjects uo
INNER JOIN
#CompressionEstimates ce
ON uo.SchemaName = ce.schema_name
AND uo.TableName = ce.object_name
AND uo.IndexID = ce.index_id
AND uo.PartitionNumber = ce.partition_number
GROUP BY
uo.SchemaName,
uo.TableName
ORDER BY
TotalSavingsMB DESC;
PRINT '';
PRINT '=============================================';
PRINT 'OVERALL SUMMARY';
PRINT '=============================================';
-- Overall summary
SELECT
COUNT(DISTINCT uo.SchemaName + '.' + uo.TableName) AS TotalTables,
COUNT(DISTINCT CASE WHEN uo.IndexID IN (0,1) THEN uo.SchemaName + '.' + uo.TableName + '.' + CAST(uo.IndexID AS VARCHAR(10)) END) AS TablesWithClusteredOrHeap,
COUNT(DISTINCT CASE WHEN uo.IndexID > 1 THEN uo.SchemaName + '.' + uo.TableName END) AS TablesWithNonClusteredIndexes,
COUNT(*) AS TotalIndexPartitions,
CAST(ROUND(SUM(ce.size_with_current_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS TotalCurrentSizeMB,
CAST(ROUND(SUM(ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS TotalCompressedSizeMB,
CAST(ROUND(SUM(ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS TotalPotentialSavingsMB,
CAST(ROUND(
(CASE
WHEN SUM(ce.size_with_current_compression_KB) > 0
THEN SUM(ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) * 100.0 / SUM(ce.size_with_current_compression_KB)
ELSE 0
END), 2) AS DECIMAL(5,2)) AS OverallSavingsPercent
FROM
#UncompressedObjects uo
INNER JOIN
#CompressionEstimates ce
ON uo.SchemaName = ce.schema_name
AND uo.TableName = ce.object_name
AND uo.IndexID = ce.index_id
AND uo.PartitionNumber = ce.partition_number;
-- =============================================
-- Optional: Generate compression commands
-- =============================================
PRINT '';
PRINT '=============================================';
PRINT 'COMPRESSION COMMANDS (Top 20 by savings)';
PRINT '=============================================';
PRINT 'Run these commands to implement PAGE compression:';
PRINT '';
SELECT TOP 20
'ALTER ' +
CASE
WHEN uo.IndexID IN (0,1) THEN 'TABLE'
ELSE 'INDEX'
END + ' ' +
uo.SchemaName + '.' + uo.TableName +
CASE
WHEN uo.IndexID > 1 THEN '.' + uo.IndexName
ELSE ''
END +
' REBUILD PARTITION = ' + CAST(uo.PartitionNumber AS VARCHAR(10)) +
' WITH (DATA_COMPRESSION = PAGE' +
CASE
WHEN uo.IndexID > 1 THEN ', ONLINE = ON'
ELSE ''
END + ');' AS CompressionCommand,
CAST(ROUND((ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) / 1024.0, 2) AS DECIMAL(18,2)) AS SavingsMB
FROM
#UncompressedObjects uo
INNER JOIN
#CompressionEstimates ce
ON uo.SchemaName = ce.schema_name
AND uo.TableName = ce.object_name
AND uo.IndexID = ce.index_id
AND uo.PartitionNumber = ce.partition_number
WHERE
(ce.size_with_current_compression_KB - ce.size_with_requested_compression_KB) > 0
ORDER BY
SavingsMB DESC;
-- Cleanup
DROP TABLE #UncompressedObjects;
DROP TABLE #CompressionEstimates;
PRINT '';
PRINT 'Script execution completed successfully!';
Comments
Post a Comment