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

Comments

Popular posts from this blog

Embedding PowerBI with ASP.NET Core 2 and Angular(2,4,5)

Handling Exit Event of Console Application in C#

CSS text-overflow ellipsis in the beginning of the the text - Left Ellipsis using CSS in a Flex box