info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Microsoft SQL Server - Database Compression Estimator (PAGE and ROW)
by BF (Principal Consultant; Architecture; Engineering)
2019-08-26
T-SQL Solution:
Set nocount on;
Truncate Table dbo.Compression_Space_Report;
Declare @SQL Nvarchar(Max), @SCHEMA Nvarchar(10), @CT Nvarchar(10), @TBL Nvarchar(50), @CNT int, @msg varchar(100);
Set @SCHEMA = 'TESTDTA';
Create Table #TBLS (ID int Identity(1,1), [Name] varchar(50) COLLATE Latin1_General_CI_AS_WS)
Insert into #TBLS ([Name]) Select 'TBL01'
--Insert into #TBLS ([Name]) Select 'TBL02'
--Insert into #TBLS ([Name]) Select 'TBL03'
--Insert into #TBLS ([Name]) Select 'TBL04'
--Insert into #TBLS ([Name]) Select 'TBL05';
Create Table #TMP
([object_name] varchar(50)
,[schema_name] varchar(30)
,[index_id] int
,[partition_number] int
,[size_with_current_compression_setting(KB)] int
,[size_with_requested_compression_setting(KB)] int
,[sample_size_with_current_compression_setting(KB)] int
,[sample_size_with_requested_compression_setting(KB)] int
);
Set @Cnt = (Select count(*) from #TBLS);
Set @TBL = (Select [Name] from #TBLS where ID = @CNT);
While (@CNT > 0)
Begin
Set @SQL = '';
Set @CT = 'PAGE'; --PAGE in sp_estimate_data_compression_savings
RAISERROR ('Begin Compression Estimation...', 10, 1) WITH NOWAIT;
Set @msg = @TBL + ': ' + @CT;
RAISERROR (@msg, 10, 1) WITH NOWAIT
Set @SQL =
'Set nocount on
Insert into #TMP
EXEC sp_estimate_data_compression_savings ' + '''' + @SCHEMA + '''' + ', ' + '''' + @TBL + '''' + ', NULL, NULL, ''PAGE''
Insert into dbo.Compression_Space_Report
(
[object_name], [schema_name], [index_id], [partition_number],
[size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)],
[sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)], [compression_type]
)
Select
[object_name], [schema_name], [index_id], [partition_number],
[size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)],
[sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)], ' + '''' + @CT + '''' +
' from #TMP
Truncate Table #TMP
'
--Print @SQL
Exec sp_executesql @SQL
Set @SQL = '';
Set @CT = 'ROW'; --ROW in sp_estimate_data_compression_savings
Set @msg = @TBL + ': ' + @CT;
RAISERROR (@msg, 10, 1) WITH NOWAIT
Set @SQL =
'Set nocount on
Insert into #TMP
EXEC sp_estimate_data_compression_savings ' + '''' + @SCHEMA + '''' + ', ' + '''' + @TBL + '''' + ', NULL, NULL, ''ROW''
Insert into dbo.Compression_Space_Report
(
[object_name], [schema_name], [index_id], [partition_number],
[size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)],
[sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)], [compression_type]
)
Select
[object_name], [schema_name], [index_id], [partition_number],
[size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)],
[sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)], ' + '''' + @CT + '''' +
' from #TMP
Truncate Table #TMP
'
--Print @SQL
Exec sp_executesql @SQL
--Delete from #TBLS where ID = @CNT
Set @Cnt = @Cnt - 1
Set @TBL = (Select [Name] from #TBLS where ID = @CNT);
RAISERROR ('End Compression Estimation...', 10, 1) WITH NOWAIT
RAISERROR (' ', 10, 1) WITH NOWAIT
End;
Select
GetDate() as 'Collection Date',
[Object_Name], [Schema_Name] as 'Scheme', [Partition_Number] as 'P.No', t3.name as 'Index Name', t1.Index_ID as 'I.ID', t3.Type_Desc as 'Index Type',
t3.[Rows],
[Compression_Type],
Convert(decimal(18,1),(Convert(decimal(18,1),([Size_with_current_compression_setting(KB)] - [Size_with_requested_compression_setting(KB)])) / [Size_with_current_compression_setting(KB)]) * 100) as 'Compression Ratio (%)',
[Size_with_current_compression_setting(KB)]/1000 as 'Current (MB)',
[Size_with_requested_compression_setting(KB)]/1000 as 'Compression(MB)',
[Size_with_current_compression_setting(KB)] as 'Current (KB)',
[Size_with_requested_compression_setting(KB)] as 'Compression (KB)',
[Sample_size_with_current_compression_setting(KB)],
[Sample_size_with_requested_compression_setting(KB)]
from
dbo.Compression_Space_Report t1
inner join #TBLS t2 on t1.[object_name] = t2.Name
inner join
(SELECT DISTINCT
i.name,
i.index_id,
i.type_desc,
p.object_id,
p.rows
FROM sys.tables t
LEFT JOIN sys.indexes i
ON t.object_id = i.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p
ON i.index_id = p.index_id
AND t.object_id = p.object_id
WHERE t.type = 'U') as t3 on t3.index_id = t1.index_id AND t1.object_name = Object_Name(t3.object_id)
order by t2.ID ASC, t1.Index_id ASC, [Compression_Type] ASC;
DROP TABLE #TMP;
DROP TABLE #TBLS;
/*
Create Table dbo.Compression_Space_Report
([object_name] varchar(50) COLLATE Latin1_General_CI_AS_WS
,[schema_name] varchar(30)
,[index_id] int
,[partition_number] int
,[size_with_current_compression_setting(KB)] int
,[size_with_requested_compression_setting(KB)] int
,[sample_size_with_current_compression_setting(KB)] int
,[sample_size_with_requested_compression_setting(KB)] int
,[compression_type] varchar(10) NULL
);
*/
GO
Output:
Compression Analyzer - Processing - Part #1:
Begin Compression Estimation...
TBL01: PAGE
TBL01: ROW
End Compression Estimation...
Begin Compression Estimation...
TBL02: PAGE
TBL02: ROW
End Compression Estimation...
Begin Compression Estimation...
TBL03: PAGE
TBL03: ROW
End Compression Estimation...
Begin Compression Estimation...
TBL04: PAGE
TBL04: ROW
End Compression Estimation...
Begin Compression Estimation...
TBL05: PAGE
TBL05: ROW
End Compression Estimation...
Compression Analyzer - Report - Part #2:
|
|
|
|
|
|
|
|
|
|