Compression estimates

,

The script executes the procedure 'sp_estimate_data_compression_savings' for each physical object in the database for which the page compression has not been implemented. It shows the result in the tabular form ordered by the size of the object in descending order.

Just execute the script against the user database on which you are planning to implement compression. Bare in mind that the compression is available only on certain editions of MS SQL Server - https://docs.microsoft.com/en-nz/sql/sql-server/editions-and-components-of-sql-server-2016.

I also found that on some databases the procedure 'sp_estimate_data_compression_savings' causes deadlocks and only way to fix that is set instance wide MAXDOP option to 1 in order to prevent parallel executions.

set nocount on;
--Get the list of the physical objects which are not already compressed in a temp table
SELECT [t].schema_id, [p].[object_id], [p].[index_id] index_no, [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression],
	(select SUM(s.[used_page_count]) * 8
	FROM sys.dm_db_partition_stats AS s
	where s.object_id = [p].[object_id] and s.index_id = p.index_id) AS IndexSizeKB,
	[u].user_updates + [u].system_updates as [Updates],
	[u].user_seeks + [u].system_seeks as [Seeks],
	[u].user_scans + [u].system_scans as [Scans]
into #objects_with_stats
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN [sys].[indexes] AS i ON i.object_id = p.object_id and i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS [u] 
	ON [u].[object_id] = [p].[object_id] and [u].[index_id] = [p].[index_id]
WHERE
	[p].[data_compression_desc] <> 'PAGE' and [p].[data_compression_desc] <> 'COLUMNSTORE'
	and i.is_disabled = 0
order by IndexSizeKB desc;

--Create a temp table for PAGE compression estimates
create table #estimates_tmp_p (
	[object_name] sysname, 
	[schema_name] sysname, 
	index_id int,
	partition_number int, 
	current_size bigint, 
	estimated_size bigint,
	sample_size bigint,
	sample_estimated bigint
);

--Create a temp table for ROW compression estimates
create table #estimates_tmp_r (
	[object_name] sysname, 
	[schema_name] sysname, 
	index_id int,
	partition_number int, 
	current_size bigint, 
	estimated_size bigint,
	sample_size bigint,
	sample_estimated bigint
);

--Use a cursor to iterate for each object
DECLARE @IndexCursor CURSOR;
DECLARE @sch sysname, @tbl sysname, @idx int, @prt int;

SET @IndexCursor = CURSOR FOR
    select SCHEMA_NAME([schema_id]), OBJECT_NAME([object_id]), index_no, Partition from #objects_with_stats;

OPEN @IndexCursor;
FETCH NEXT FROM @IndexCursor INTO @sch, @tbl, @idx, @prt;

WHILE @@FETCH_STATUS = 0
    BEGIN
		--Populate temp tables with the estimates for each object
		INSERT INTO #estimates_tmp_p ([object_name], [schema_name], index_id, partition_number, current_size, estimated_size, sample_size, sample_estimated)
		EXEC sp_estimate_data_compression_savings @sch, @tbl, @idx, @prt, 'PAGE';
		INSERT INTO #estimates_tmp_r ([object_name], [schema_name], index_id, partition_number, current_size, estimated_size, sample_size, sample_estimated)
		EXEC sp_estimate_data_compression_savings @sch, @tbl, @idx, @prt, 'ROW';
		--Take the next record
		FETCH NEXT FROM @IndexCursor INTO @sch, @tbl, @idx, @prt;
    END; 

CLOSE @IndexCursor;
DEALLOCATE @IndexCursor;

--Show the resultset ordered by the size of an index
select DB_NAME() [Database], SCHEMA_NAME(idx.[schema_id]) [schema], OBJECT_NAME(idx.[object_id]) [table],
	idx.index_no, idx.partition, idx.IndexSizeKB, idx.Updates, idx.Seeks, idx.Scans, rce.row_est_prc_save, pce.page_est_prc_save
from
	#objects_with_stats idx
inner join
	(select OBJECT_ID('['+[schema_name]+'].['+[object_name]+']') [object_id], SCHEMA_ID([schema_name]) [schema_id], index_id, partition_number, 
		case when current_size>0 
			then 100.0*(current_size - estimated_size)/current_size 
			else NULL 
		end page_est_prc_save
	from #estimates_tmp_p p) pce
on pce.[schema_id] = idx.[schema_id] and pce.[object_id] = idx.[object_id]
	and pce.index_id = idx.index_no and pce.partition_number = idx.partition
inner join
	(select OBJECT_ID('['+[schema_name]+'].['+[object_name]+']') [object_id], SCHEMA_ID([schema_name]) [schema_id], index_id, partition_number, 
		case when current_size>0 
			then 100.0*(current_size - estimated_size)/current_size 
			else NULL 
		end row_est_prc_save
	from #estimates_tmp_r r) rce
on pce.[schema_id] = rce.[schema_id] and pce.[object_id] = rce.[object_id]
	and pce.index_id = rce.index_id and pce.partition_number = rce.partition_number
order by idx.IndexSizeKB desc;

--Do cleanup
drop table #objects_with_stats
drop table #estimates_tmp_p
drop table #estimates_tmp_r

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)