Data Compression, data pages, XML & SQLCLR

Chiunque si occupi di database sa, è evidente, che la loro dimensione aumenta nel tempo.

Aumentano i dati, quindi gli indici, quindi lo storage necessario, quindi le dimensioni dei backup, quindi i tempi di manutenzione, quindi …

A partire dalla versione 2008, SQL Server offre la possibilità di gestire i dati (e gli indici) in maniera compressa.

 

Dopo un’attenta valutazione, la possibilità di avere tabelle compresse consente di avere strutture dati più piccole e, di conseguenza, una minore attività di I/O.

 

La compressione può essere definita a livello di riga o a livello di pagina.

• Per il livello ROW soltanto alcuni tipi di dato possono essere compressi. Qui la lista completa.
• Per il livello PAGE dipende dal contenuto, parliamo a livello di binario, della data page che contiene i dati.

Cosa significa?

Significa che dipende dalla struttura interna che viene utilizzata per memorizzare il dato che andiamo a gestire.

Queste sono le strutture (allocation unit) presenti in SQL Server:

• IN_ROW_DATA
• ROW_OVERFLOW_DATA
• LOB_DATA

La compressione avviene solo sui dati memorizzati all’interno di strutture IN_ROW_DATA.

 

Ho modo di vedere “al volo” le strutture che vengono utilizzate per le mie tabelle e, quindi, valutare se poter prendere in considerazione la compressione dei dati a livello pagina?

Si, ad esempio con questa SELECT:

 SELECT
    OBJECT_NAME( P.object_id )AS       [Name] , 
    I.name AS                          [Index] , 
    PS.in_row_data_page_count AS       [In Row] , 
    PS.row_overflow_used_page_count AS [Row Overflow] , 
    PS.lob_reserved_page_count AS      [LOB Data]
FROM sys.dm_db_partition_stats AS PS
JOIN sys.partitions AS P ON 
       PS.partition_id = P.partition_id
JOIN sys.indexes AS I ON 
       P.index_id = I.index_id AND 
       P.object_id = I.object_id
WHERE 
    OBJECTPROPERTY( P.object_id , 'IsUserTable' ) = 1
ORDER BY
           PS.in_row_data_page_count DESC;

 

Questo un esempio di risultato:

 

Più alto è il valore “In Row” più è facile che la tabella sia un’ottima candidata alla compressione.

 

Che cosa succede utilizzando un tipo XML (quindi un campo LOB, Large OBject)?

Questo tipo, così come i tipi che utilizzano la clausola “MAX”, vengono memorizzati all’interno di strutture IN_ROW_DATA finché il loro contenuto lo consente, altrimenti in strutture LOB_DATA che NON possono essere compresse.

 

Proviamo ad immaginare questo scenario:

• Ho la necessità di memorizzare e storicizzare dati XML, di notevoli dimensioni, all’interno di una mia tabella.
• Poiché l’XML contiene dei commenti, decido di memorizzarlo all’interno di una colonna VARCHAR(MAX), in modo da memorizzarlo perfettamente identico a come arriva e non in modalità “equivalente” come farebbe il tipo XML.
• L’XML deve poter poi essere letto ogni tanto, ad esempio per poter fare dei controlli periodici o cose simili.

 

Posso pensare di memorizzarlo compresso per ridurre lo spazio che altrimenti sarebbe utilizzato?

Si, ad esempio tramite il SQLCLR.

 

Potrei costruire due funzioni:

• una per comprimere il dato XML per poterlo scrivere come binario
• una per de-comprimere il binario e riportare “in chiaro” il dato XML

 

Ad esempio, la funzione per comprimere:

 using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlBytes uf_Compress(SqlBytes blob)
    {
        if (blob.IsNull)
            return blob;

        byte[] blobData = blob.Buffer;

        MemoryStream compressedData = new MemoryStream();
        DeflateStream compressor = new DeflateStream(compressedData,
                                           CompressionMode.Compress, true);

        compressor.Write(blobData, 0, blobData.Length);

        compressor.Flush();
        compressor.Close();
        compressor = null;

        return new SqlBytes(compressedData);
    }
}

 

La funzione per de-comprimere:

 using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlBytes uf_Decompress(SqlBytes compressedBlob)
    {
        if (compressedBlob.IsNull)
            return compressedBlob;

        DeflateStream decompressor = new DeflateStream(compressedBlob.Stream,
                                           CompressionMode.Decompress, true);

        int bytesRead = 1;
        int chunkSize = 10000;
        byte[] chunk = new byte[chunkSize];

        MemoryStream decompressedData = new MemoryStream();

        try
        {
            while ((bytesRead = decompressor.Read(chunk, 0, chunkSize)) > 0)
            {
                decompressedData.Write(chunk, 0, bytesRead);
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            decompressor.Close();
            decompressor = null;
        }
        return new SqlBytes(decompressedData);
    }
}

Un esempio per scrivere i dati:

 

Un esempio per leggerli:

 

Costruendo due tabelle, una con una colonna XML e l’altra con una colonna VARBINARY(MAX) , e popolandole con diverse migliaia di righe di test, potremmo arrivare a questi risultati:

 

I risultati sono molto interessanti ed incoraggianti, con un livello di compressione > 84% .

 

Come al solito, i commenti sono aperti.