Looping through each table, to see what type of data compression to use.
drop table if exists #tmpObjectList,#tmpFinalResults;
go
declare @i_i int,
@i_maxID int,
@nvc_SQL nvarchar(2000) ;
declare @tmpResults table
(
idResults int identity(1,1) not null primary key clustered,
objName varchar(128) not null,
schName varchar(128) not null,
index_id int not null,
partition_number int not null,
current_compression_size bigint null, /kb/
requested_compression_size bigint null, /kb/
current_compression_size_sample bigint null, /kb/
requested_compression_size_sample bigint null/kb/
);
create table #tmpFinalResults
(
idFinalResults int identity(1,1) not null primary key clustered,
objName varchar(128) not null,
schName varchar(128) not null,
index_id int not null,
partition_number int not null,
current_compression_size bigint null, /kb/
requested_compression_size bigint null, /kb/
CompressionType varchar(50) not null
);
create table #tmpObjectList
(
idObject int identity(1,1) not null primary key clustered,
objName varchar(128) not null,
schName varchar(128) not null
);
INSERT INTO #tmpObjectList(objName,schName)
SELECT o.name, OBJECT_SCHEMA_NAME(o.object_id) as schName
FROM sys.objects as o
INNER JOIN sys.indexes AS i ON i.object_id = o.object_id
INNER JOIN sys.dm_db_partition_stats as ps ON ps.object_id = i.object_id and ps.index_id = i.index_id
INNER JOIN sys.partitions as p ON ps.object_id = p.object_id and ps.partition_id = p.partition_id and ps.index_id = p.index_id
WHERE
o.is_ms_shipped =0
and o.type =’U’
GROUP BY
o.name,OBJECT_SCHEMA_NAME(o.object_id)
HAVING
CONVERT(decimal(18,2),SUM(reserved_page_count) * 8/1024.0) > 10.0 /* sa fie mai mare ca 10 MB/ /
SELECT o.name, OBJECT_SCHEMA_NAME(o.object_id) as schName
FROM sys.objects as o
WHERE
o.is_ms_shipped =0
and o.type =’U’
*/
/preluam limitele min , max/
SELECT @i_maxID = MAX(idObject) ,
@i_i = MIN(idObject)
FROM #tmpObjectList;
WHILE (@i_i<= @i_maxID)
/WHILE (@i_i<= 2)/
BEGIN
/* row compression*/
SELECT @nvc_SQL =N’EXEC sp_estimate_data_compression_savings ‘ + tol.schName + N’, ‘ + tol.objName + N’, NULL, NULL, ”ROW”’
FROM #tmpObjectList as tol
WHERE idObject = @i_i;
/*print @nvc_SQL;*/
DELETE FROM @tmpResults;
INSERT INTO @tmpResults(objName,schName,index_id,partition_number,
current_compression_size ,requested_compression_size,
current_compression_size_sample ,requested_compression_size_sample)
EXEC(@nvc_SQL);
INSERT INTO #tmpFinalResults(objName,schName,index_id,partition_number,current_compression_size ,requested_compression_size,CompressionType)
SELECT t.objName,t.schName,t.index_id,t.partition_number,t.current_compression_size,t.requested_compression_size,'ROW'
FROM @tmpResults as t
/*page compression*/
SELECT @nvc_SQL =N'EXEC sp_estimate_data_compression_savings ' + tol.schName + N', ' + tol.objName + N', NULL, NULL, ''PAGE'''
FROM #tmpObjectList as tol
WHERE idObject = @i_i;
/*print @nvc_SQL;*/
DELETE FROM @tmpResults;
INSERT INTO @tmpResults(objName,schName,index_id,partition_number,
current_compression_size ,requested_compression_size,
current_compression_size_sample ,requested_compression_size_sample)
EXEC(@nvc_SQL);
INSERT INTO #tmpFinalResults(objName,schName,index_id,partition_number,current_compression_size ,requested_compression_size,CompressionType)
SELECT t.objName,t.schName,t.index_id,t.partition_number,t.current_compression_size,t.requested_compression_size,'PAGE'
FROM @tmpResults as t
SET @i_i = @i_i +1;
SET @nvc_SQL =N'';
END;
/*
SELECT * FROM #tmpFinalResults;
*/
SELECT ISNULL(r.objName,p.objName) as tblName,ISNULL(r.schName,p.schName) as tblSchName,ISNULL(r.index_id,p.index_id)as index_ID,
r.current_compression_size , p.current_compression_size,
r.requested_compression_size as row_compression_size,p.requested_compression_size as page_compression_size,
CAST(r.requested_compression_size*1.0 / (r.current_compression_size ) *100.0 as tinyint) as ROW_Procent,
CAST(p.requested_compression_size*1.0 / (p.current_compression_size ) *100.0 as tinyint) as PAGE_Procent
FROM
(
SELECT r.objName,r.schName,r.index_id,r.current_compression_size,r.requested_compression_size
FROM #tmpFinalResults as r
WHERE r.CompressionType =’ROW’
) as r
FULL JOIN
(
SELECT p.objName,p.schName,p.index_id,p.current_compression_size,p.requested_compression_size
FROM #tmpFinalResults as p
WHERE p.CompressionType =’PAGE’
) as p
ON r.objName = p.objName AND r.schName = p.schName AND r.index_id = p.index_id
Sql Server 