trešdiena, 2012. gada 25. jūlijs

Datu kompresēšana: Pārskats par visām DB tabulām

Sākot ar SQL Server 2008, Enterprice/Developer redakcijās ir iespējama datu kompresēšana. Datus var kompresēt datu rindas un datu lapas līmeņos. Datu rindas kompresēšana ir ātrāka un mazāk resursu prasīga, datu lapu kompresēšana ir lēnāka un resursu prasīgāka.

Š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).
Mīnusi:

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:

Komentāra publicēšana