Nieuwsbrief mei 2022

SQL Server bevat diverse mogelijkheden data te comprimeren. Vooral voor historische data kan dit een uitkomst zijn waardoor de groei van datafiles en backup-tijden beperkt blijven. Ik beschrijf hieronder kort de opties die je hebt. En ik verzorg weer langere SQL Server trainingen, in samenwerking met Eduvision.

Wil je mijn nieuwsbrief niet ontvangen, laat het me weten.

Met hartelijke groet,

Paul van Oordt

Datacompressie in SQL Server

SQL Server bevat diverse mogelijkheden data te comprimeren teneinde opslag, I/O en geheugengebruik te beperken.

Column-store indexes maken gebruik van een vergaande vorm van compressie waarin hele datasets tot een miljoen regels worden gecomprimeerd. Column-store indexes zijn echter bedoeld voor een specifieke werklast, namelijk rapportages op grote datasets, en zijn niet zomaar een alternatief voor gewone indexes. Een clustered column-store index met archival compression is wel de meest efficiënte manier om data te archiveren die zeer zelden wordt geraadpleegd.

Compressie van gewone indexes of heaps wordt vastgesteld per tabel- of index-partitie, waardoor je in staat bent bijvoorbeeld alleen de historische data in een tabel te comprimeren. De actuele data die nog regelmatig gemodificeerd wordt, kan dan ongecomprimeerd blijven. Daarvoor moet dus wel table partitioning geconfigureerd worden.

Compressie van de data in een partitie kan op row level en op page level. Hoeveel ruimte met compressie wordt bespaard, hangt af van de daadwerkelijke data. Row-level compressie comprimeert fixed-length kolommen op basis van de inhoud. Zo neemt een 0 in een integer veld nog maar 1 bit en de getallen 1 - 255 slechts 1 byte aan ruimte, in plaats van de standaard 4 bytes voor een int of 8 bytes voor een bigint. Ook fixed length character data (char en nchar), datetime en nog enkele datatypes leveren mogelijk besparing op. Page-level compression omvat row-level compression en gebruikt daarnaast algoritmes om meermaals voorkomende byte-sequenties op een pagina, ook binnen variable length kolommen, efficiënt op te slaan.

Het geschatte effect van compressie kan worden opgevraagd met een stored procedure bijvoorbeeld als volgt:

  exec sp_estimate_data_compression_savings 
     'Sales', 'Invoices', null, null, 'page';

Het vierde en vijfde argument van de procedure zijn hierbij respectievelijk index_id en partition_id. Voor deze tabel in de voorbeeld-database WideWorldImporters levert row compression 11% reductie op en page compression 29%. De gecomprimeerde data is dus 89% respectievelijk 71% van de ongecomprimeerde data.

En column-level compressie, bestaat dat ook nog? Jazeker. T-SQL bevat de compress en decompress functies waarmee character data gecomprimeerd kan worden. Ook hier hangt de winst af van de data. De reductie zal groter zijn naarmate de string langer is en meer regelmaat bevat. Een test op een tabel met SQL statements en queryplannen levert voor beide de aanzienlijke winst op van ruim 80%.

De select-performance lijdt niet al te sterk onder compressie. Het interne formaat van de dataopslag is anders, maar kan nog steeds efficiënt worden uitgelezen. Compressie heeft meer gevolgen voor de performance van data-modificatie. De update van een 0 naar een 1 in een integer-kolom, bijvoorbeeld, kost een extra byte, en die moet er maar net zijn. Grootschalige updates kunnen makkelijk leiden tot veel page splits en mede daarom veel meer tijd kosten. Het is dus logisch compressie vooral in te zetten voor data die niet of nauwelijks gewijzigd wordt, en wellicht slechts incidenteel wordt geraadpleegd.

Het is duidelijk dat in een concreet geval onderzocht zal moeten worden welke vorm van compressie de beste resultaten geeft voor de specifieke dataset en werklast. In veel gevallen betekent dat ook nadenken over partitioning. Table partitioning heeft andere belangrijke voordelen zoals het verwijderen van grote hoeveelheden data als supersnelle metadata-operatie, en het rebuilden van indexes of updaten van statistics per partitie. Ook partitioned views, in de tijd dat table partitioning nog een Enterprise Edition feature was soms 'poor mans partitioning' genoemd, is prima te combineren met compressie van enkele van de partities. In een partitioned view wordt data uit verschillende tabellen met union all gecombineerd in een view. Een voordeel is dat het in gebruik te nemen is met minimale downtijd.

Behalve de hier besproken compressie van data, is er ook backup-compressie die met een enkel vinkje aan te zetten is.

SQL Server trainingen in samenwerking met Eduvision

Zoals iedere docent weet, is een cursus niet alleen leerzaam voor de cursisten. De voorbereiding en de vele vragen geven ook de trainer steeds weer meer kennis en een dieper begrip.

In al mijn werk neemt kennisoverdracht een belangrijke plaats in. Ik ben immers maar kort bij een klant. Daarnaast verzorg ik wel eens een geïmproviseerde cursus van één of twee dagen, meestal voortkomend uit een onderzoek naar performance-problemen. "Eigenlijk zou je eens een dagje met onze ontwikkelaars in een zaaltje moeten gaan zitten..."

Onlangs heb ik, voor het eerst sinds mijn tijd als freelance trainer bij Compu'Train (1999 - 2009), weer eens een meer omvattende training verzorgd over vrijwel alle aspecten van SQL Server: programming, beheer, performance, availability, security en internals. Het is mij en de cursisten goed bevallen.

Zelf zie ik mij niet in de juiste positie om langere cursussen te organiseren, maar gelukkig zijn er bedrijven die daar heel goed in zijn. Ik werk op dit gebied samen met Eduvision in Apeldoorn. Ik verzorg geen Microsoft Official Courses, en ik ben geen Microsoft Certified Trainer (meer), maar ik kan heel flexibel invulling geven aan de leerwensen op basis van heel veel praktijkervaring.

Dank voor het lezen.

mei 2022; de nieuwsbrief verschijnt enkele keren per jaar; aanmelden / afmelden