Šajā rakstā SQL vaicājums, kas ļauj saprast cik lieli ieguvumi varētu būt no datu kompresēšanas.
Procedūra sp_estimate_data_compression_savings atgriež prognozētos kompresijas rezultātus par konkrētu tabulu, indeksu, indeksa daļu. Vēlējos atrast jau gatavu SQL vaicājumu, kas attēlotu šos rezultātus par visām datu bāzes tabulām. Tuvākais ko atradu ir šis (sqlfool.com), ko arī izmantoju par sava varianta pamatu.
Izveidotā vaicājuma plusi:
- Var iegūt prognozi, cik potenciāli vietas var iegūt kompresējot noteiktus objektus;
- Blakus salīdzināmi datu rindas un datu lapas kompresēšanas varianti (PAGE/ROW);
- Pieejami daudz informācijas, kas var noderēt apsverot kompresēšanas iespējas (pietrūkst Select/Insert/Uupdate/Delete attiecība).
- Process ir resursu prasīgs (dēļ sp_estimate_data_compression_savings darbības principiem);
Piebilde par vaicājumu: "After_CompressionROW" un "After_CompressionPage" ir procentuāli cik vietas tiks aizņemts salīdzinoši ar esošo stāvokli. Piemēram- 10 nozīmē to, ka tabula aizņems 10% no tā, ko tā aizņem šobrīd).
Vaicājums (Rezultātus vislabāk analizēt EXCEL):
declare @TableName sysname;
declare @SchemaName sysname;
declare @object_id int;
declare @txt nvarchar(200);
declare @resultsid int;
CREATE TABLE #results
(
resultsid int primary key identity,
ObjectName sysname,
SchemaName sysname,
index_id int,
partition_number int,
size_current_compression bigint,
size_requested_compression bigint,
sample_current_compression bigint,
sample_requested_compression bigint,
object_id int,
CompressionType varchar(20)
);
-- tiek atrasti visi objekti (tabulas), kuras potenciāli var kompresēt.
Select name TableName, schema_name(schema_id) SchemaName, object_id
Into #ttable
From sys.objects
Where is_ms_shipped = 0 And Type = 'U' -- USER_TABLE, indeksētus skatus neapskatu šeit.
Order By schema_name(schema_id), name
-- sākas lēnais process, kas kas kursorā apstrādās visas tabulas.
-- procesa laikā tiek veikta 10% no datiem kompresija- tātad, resursietilpīgs process.
-- produkcijas vidē tas noteikti jāņem vērā (un tur, visdrīzāk labāk
-- pārbaudīt tikai to, ko vajag)
Set NoCount On;
declare cur cursor for
Select TableName, SchemaName, object_id From #ttable
open cur
fetch next from cur into @TableName, @SchemaName, @object_id
while @@fetch_status = 0
begin
Set @txt = 'working on ' + @SchemaName + '.' + @TableName
RaisError(@txt, 0, 0) With NoWait; -- informatīvs paziņojums par statusu
Insert Into #results(ObjectName, SchemaName, index_id, partition_number, size_current_compression, size_requested_compression,
sample_current_compression, sample_requested_compression)
Exec sp_estimate_data_compression_savings @SchemaName, @TableName, NULL, NULL, 'PAGE';
Set @resultsid = scope_identity();
Update #results
Set object_id = @object_id,
CompressionType = 'PAGE'
Where ObjectName = @TableName and SchemaName = @SchemaName And object_id is null;
Insert Into #results(ObjectName, SchemaName, index_id, partition_number, size_current_compression, size_requested_compression,
sample_current_compression, sample_requested_compression)
Exec sp_estimate_data_compression_savings @SchemaName, @TableName, NULL, NULL, 'ROW';
Set @resultsid = scope_identity();
Update #results
Set object_id = @object_id,
CompressionType = 'ROW'
Where ObjectName = @TableName and SchemaName = @SchemaName And object_id is null;
fetch next from cur into @TableName, @SchemaName, @object_id
end
close cur;
Deallocate cur;
;
With cte as
(
Select r.SchemaName + '.' + r.ObjectName ObjectName, i.Name IndexName, i.type_desc IndexType, p.rows rwCount, p.data_compression_desc CurrentCompressionType, CompressionType,
r.partition_number, size_current_compression, size_requested_compression,
sample_current_compression, sample_requested_compression,
case when size_current_compression = 0 then 0 else size_requested_compression * 100 / size_current_compression end estimatedCompression,
case when sample_current_compression = 0 then 0 else sample_requested_compression * 100 / sample_current_compression end sampleCompresion,
ps.in_row_used_page_count AS IN_ROW_DATA,
ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA,
ps.lob_used_page_count AS LOB_DATA,
ps.in_row_used_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count total_page_count,
r.object_id, r.index_id,
Cast(
Case When ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count > 0
Then ps.in_row_used_page_count * 100 / (ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count)
Else 0 End As Int) PercentInRow,
Cast(
Case When ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count > 0
Then ps.row_overflow_used_page_count * 100 / (ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count)
Else 0 End As Int) PercentRowOverflow,
Cast(
Case When ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count > 0
Then ps.lob_used_page_count * 100 / (ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_used_page_count)
Else 0 End As Int) PercenLob
From #results r
Left Join sys.indexes i
On r.object_id = i.object_id And r.index_id = i.index_id
Left Join sys.partitions p
On r.object_id = p.object_id And r.index_id = p.index_id And r.partition_number = p.partition_number
Left Join sys.dm_db_partition_stats ps
On r.object_id = ps.object_id And r.index_id = ps.index_id And r.partition_number = ps.partition_number
--Where Rows > 1000 -- nav jēga skatīt tabulas, kur maz datu.. 1000 rindas ar nav daudz..
)
Select
c1.ObjectName, c1.IndexName, c1.partition_number, c1.IndexType, c1.rwCount, c1.total_page_count,
c1.PercentInRow [% InRow],
c1.PercentRowOverflow [% RowOverflow],
c1.PercenLob [% LOB],
c1.estimatedCompression After_CompressionROW,
c2.estimatedCompression After_CompressionPAGE,
Cast(c1.total_page_count * 8 / 1024.0 as decimal(18,2)) MBSpaceUsed,
-- Datu veidi (Rindu kompresēšana attiecas tikai uz IN_ROW_DATA):
--c1.IN_ROW_DATA,
--c1.ROW_OVERFLOW_DATA,
--c1.LOB_DATA,
-- Prognozējamais tabulas izmērs pēc kompresijas:
--Cast(c1.total_page_count * 8 / 1024.0 * c1.estimatedCompression / 100 as decimal(18,2)) MBAfter_CompresionROW,
--Cast(c1.total_page_count * 8 / 1024.0 * c2.estimatedCompression / 100 as decimal(18,2)) MBAfter_CompresionPAGE,
-- Prognozējamais vietas ietaupījums:
Cast(c1.total_page_count * 8 / 1024.0 - c1.total_page_count * 8 / 1024.0 * c1.estimatedCompression / 100 as decimal(18,2)) MBSaveAfter_CompresionRow,
Cast(c1.total_page_count * 8 / 1024.0 - c1.total_page_count * 8 / 1024.0 * c2.estimatedCompression / 100 as decimal(18,2)) MBSaveAfter_CompresionPAGE
From cte c1 Inner Join cte c2
On c1.object_id = c2.object_id
And c1.index_id = c2.index_id
And c1.partition_number = c2.partition_number
And c1.CompressionType = 'ROW' And c2.CompressionType = 'PAGE'
Order By c1.ObjectName, c1.IndexName, c1.partition_number
Nav komentāru:
Ierakstīt komentāru