### SQL Server Data Compression Prediction

Following script will generate prediction for data compression in 3 different levels (Database level, Table level, Index level).

DECLARE @estimation table(ObjectName nvarchar(100),SchemaName nvarchar(10),IndexId int, PartitionId int, CurrentSize bigint, NewSize bigint, SampleCurrentSize bigint, SampleNewSize bigint, CompressionMode nvarchar(10) null);

DECLARE @TableName nvarchar(100);

DECLARE TABLE_CURSOR cursor for

SELECT name FROM sys.objects where type = 'U'

OPEN TABLE_CURSOR

FETCH NEXT FROM TABLE_CURSOR into @TableName;

WHILE(@@FETCH_STATUS = 0)

BEGIN

INSERT INTO @estimation (ObjectName,SchemaName,IndexId,PartitionId,CurrentSize,NewSize,SampleCurrentSize,SampleNewSize)

EXEC sp_estimate_data_compression_savings 'dbo',@TableName,NULL,NULL,ROW;

UPDATE @estimation SET CompressionMode = 'ROW' WHERE CompressionMode IS NULL;

INSERT into @estimation (ObjectName,SchemaName,IndexId,PartitionId,CurrentSize,NewSize,SampleCurrentSize,SampleNewSize)

EXEC sp_estimate_data_compression_savings 'dbo',@TableName,NULL,NULL,PAGE;

UPDATE @estimation SET CompressionMode = 'PAGE' WHERE CompressionMode IS NULL;

FETCH NEXT FROM TABLE_CURSOR into @TableName;

END

close TABLE_CURSOR;

deallocate TABLE_CURSOR;

SELECT

cast((SUM(ROW.CurrentSize)/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((SUM(ROW.NewSize) /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((SUM(PAGE.NewSize)/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE SUM(ROW.CurrentSize) WHEN 0 THEN '0%' ELSE cast(cast(((SUM(ROW.CurrentSize)-SUM(ROW.NewSize)) * 100.00 /SUM(ROW.CurrentSize) ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case SUM(PAGE.CurrentSize) when 0 then '0%' else cast(cast(((SUM(PAGE.CurrentSize)-SUM(PAGE.NewSize)) * 100.00 /SUM(PAGE.CurrentSize)) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'ROW' group by ObjectName)ROW

INNER JOIN (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'PAGE' group by ObjectName)PAGE ON

ROW.ObjectName = PAGE.ObjectName

SELECT ROW.ObjectName,cast((ROW.CurrentSize/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((ROW.NewSize /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((PAGE.NewSize/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE ROW.CurrentSize WHEN 0 THEN '0%' ELSE cast(cast(((ROW.CurrentSize-ROW.NewSize) * 100.00 /ROW.CurrentSize ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case PAGE.CurrentSize when 0 then '0%' else cast(cast(((PAGE.CurrentSize-PAGE.NewSize) * 100.00 /PAGE.CurrentSize) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'ROW' group by ObjectName)ROW

INNER JOIN (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'PAGE' group by ObjectName)PAGE ON

ROW.ObjectName = PAGE.ObjectName

SELECT ROW.ObjectName,ROW.IndexId,IND.name as IndexName, ROW.PartitionId,cast((ROW.CurrentSize/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((ROW.NewSize /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((PAGE.NewSize/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE ROW.CurrentSize WHEN 0 THEN '0%' ELSE cast(cast(((ROW.CurrentSize-ROW.NewSize) * 100.00 /ROW.CurrentSize ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case PAGE.CurrentSize when 0 then '0%' else cast(cast(((PAGE.CurrentSize-PAGE.NewSize) * 100.00 /PAGE.CurrentSize) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT * FROM @estimation where CompressionMode = 'ROW')ROW

INNER JOIN (SELECT * FROM @estimation where CompressionMode = 'PAGE')PAGE ON

ROW.ObjectName = PAGE.ObjectName AND

ROW.IndexId = PAGE.IndexId AND

ROW.PartitionId = PAGE.PartitionId

LEFT OUTER JOIN sys.indexes IND on

object_id( ROW.ObjectName ) = IND.object_id AND

ROW.IndexId = IND.index_id

DECLARE @estimation table(ObjectName nvarchar(100),SchemaName nvarchar(10),IndexId int, PartitionId int, CurrentSize bigint, NewSize bigint, SampleCurrentSize bigint, SampleNewSize bigint, CompressionMode nvarchar(10) null);

DECLARE @TableName nvarchar(100);

DECLARE TABLE_CURSOR cursor for

SELECT name FROM sys.objects where type = 'U'

OPEN TABLE_CURSOR

FETCH NEXT FROM TABLE_CURSOR into @TableName;

WHILE(@@FETCH_STATUS = 0)

BEGIN

INSERT INTO @estimation (ObjectName,SchemaName,IndexId,PartitionId,CurrentSize,NewSize,SampleCurrentSize,SampleNewSize)

EXEC sp_estimate_data_compression_savings 'dbo',@TableName,NULL,NULL,ROW;

UPDATE @estimation SET CompressionMode = 'ROW' WHERE CompressionMode IS NULL;

INSERT into @estimation (ObjectName,SchemaName,IndexId,PartitionId,CurrentSize,NewSize,SampleCurrentSize,SampleNewSize)

EXEC sp_estimate_data_compression_savings 'dbo',@TableName,NULL,NULL,PAGE;

UPDATE @estimation SET CompressionMode = 'PAGE' WHERE CompressionMode IS NULL;

FETCH NEXT FROM TABLE_CURSOR into @TableName;

END

close TABLE_CURSOR;

deallocate TABLE_CURSOR;

SELECT

cast((SUM(ROW.CurrentSize)/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((SUM(ROW.NewSize) /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((SUM(PAGE.NewSize)/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE SUM(ROW.CurrentSize) WHEN 0 THEN '0%' ELSE cast(cast(((SUM(ROW.CurrentSize)-SUM(ROW.NewSize)) * 100.00 /SUM(ROW.CurrentSize) ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case SUM(PAGE.CurrentSize) when 0 then '0%' else cast(cast(((SUM(PAGE.CurrentSize)-SUM(PAGE.NewSize)) * 100.00 /SUM(PAGE.CurrentSize)) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'ROW' group by ObjectName)ROW

INNER JOIN (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'PAGE' group by ObjectName)PAGE ON

ROW.ObjectName = PAGE.ObjectName

SELECT ROW.ObjectName,cast((ROW.CurrentSize/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((ROW.NewSize /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((PAGE.NewSize/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE ROW.CurrentSize WHEN 0 THEN '0%' ELSE cast(cast(((ROW.CurrentSize-ROW.NewSize) * 100.00 /ROW.CurrentSize ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case PAGE.CurrentSize when 0 then '0%' else cast(cast(((PAGE.CurrentSize-PAGE.NewSize) * 100.00 /PAGE.CurrentSize) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'ROW' group by ObjectName)ROW

INNER JOIN (SELECT ObjectName,SUM(CurrentSize) as CurrentSize,sum(NewSize) as NewSize FROM @estimation where CompressionMode = 'PAGE' group by ObjectName)PAGE ON

ROW.ObjectName = PAGE.ObjectName

SELECT ROW.ObjectName,ROW.IndexId,IND.name as IndexName, ROW.PartitionId,cast((ROW.CurrentSize/1024.00) AS NUMERIC(10,2)) as CurrentSizeInMB,

cast((ROW.NewSize /1024.00) AS NUMERIC(10,2)) as ROWCompressedSizeInMB,

cast((PAGE.NewSize/1024.00) AS NUMERIC(10,2)) as PAGECompressedSizeInMB,

CASE ROW.CurrentSize WHEN 0 THEN '0%' ELSE cast(cast(((ROW.CurrentSize-ROW.NewSize) * 100.00 /ROW.CurrentSize ) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as ROWCompression,

case PAGE.CurrentSize when 0 then '0%' else cast(cast(((PAGE.CurrentSize-PAGE.NewSize) * 100.00 /PAGE.CurrentSize) AS NUMERIC(10,2)) AS NVARCHAR(50)) + '%' END as PAGECompression

FROM (SELECT * FROM @estimation where CompressionMode = 'ROW')ROW

INNER JOIN (SELECT * FROM @estimation where CompressionMode = 'PAGE')PAGE ON

ROW.ObjectName = PAGE.ObjectName AND

ROW.IndexId = PAGE.IndexId AND

ROW.PartitionId = PAGE.PartitionId

LEFT OUTER JOIN sys.indexes IND on

object_id( ROW.ObjectName ) = IND.object_id AND

ROW.IndexId = IND.index_id

## Comments

## Post a Comment