Microsoft SQL Server 2008 et la compression de données

Introduction

Microsoft SQL Server implémente depuis la version 2008 une fonctionalité permettant de compresser les pages de données.

L’objectif annoncé est double, d’abord de permettre de réduire de manière significative la taille d’une base, ensuite de réaliser des gains de performance en réduisant le nombre de d’E/S nécessaires à la manipulation de données.

La promesse de cette fonction est séduisante à priori car elle remplace des I/Os physiques - très lents - par des cycles CPU - extrêmement rapides.

La présente note va montrer que le premier objectif est largement atteint, mais que le second est moins systématique qu’il n’y paraît.

Présentation

La stratégie de compression repose sur une extension à tous les types (ou presque) d’une nouveauté apparue en SQL 2005, le type vardecimal, stockant les valeurs numériques de manière optimisée. Conséquence fâcheuse pour cette nouveauté, elle devient désormais obsolète (deprecated) dans la prochaine version de SQL Server (Denali).

La compression, comme l’encryption, est absolument transparente pour les applications clientes.

La compression est réalisée au sein de chaque page et s’applique aux tables, indexes et partitions de manière indépendante. De fait, toutes les combinaisons sont possibles. On peut compresser une partition unitairement et pas les autres. Pour une même table, des types de compression différents par index peuvent être définis.

Deux stratégies différentes de compression influant sur le degré de transformation de la page sont disponibles : la compression de lignes et la compression de pages.

Compression de lignes (ROW)

Description de la compression de lignes (ROW)

Dans la compression de lignes, les types fixes sont remplacés par des types équivalents variables. Le surcoût par colonne compressée est de 4 bits, indiquant entre autre la taille de la donnée.

Concrètement, une colonne de type CHAR(10) NOT NULL contenant la valeur 'ABCDEF' occupera 10 octets classiquement, et uniquement 6 (+ 4 bits) en mode compressé.

Mieux, ce transtypage s’applique également aux valeurs numériques. Avec la compression de lignes, les valeurs numériques sont optimisées en réduisant le nombre d’octets nécessaires au stockage, sans perte de précision. Ainsi, une colonne de type INT (valeurs de -2^31 à +2^31 -1) occupant donc 4 octets habituellement quelle que soit la valeur saisie n’en consommera qu’un seul (plus les 4 bits) pour les valeurs comprises entre 0 et 255, comme pour le type tinyint.

Enfin, les valeurs NULL et 0 sont également traitées de manière optimisée, seuls les 4 bits additionnels sont nécessaires à leur stockage.

Le phénomène de gain d’espace dans la compression de lignes est illustré ci-dessous :

Le schéma montre bien l’effet des conversions : malgré un surcoût structurel de la solution (4 bits supplémentaires par colonne compressée), de l’espace supplémentaire est rendu disponible, espace qui sera utilisé pour stocker d’autres lignes.

Notons la compression en 1 seul octet au lieu de 4 pour les valeurs numériques 123 et 45 codées désormais comme un type tinyint (rouge-orangé dans le schéma).

Compression de lignes - Exemple

Soit une table contenant trois colonnes de taille fixe dans laquelle sont insérées des données ne remplissant pas la totalité du champ.

create table test ( a char(10) not null, b INTEGER NOT NULL , c char(5) not null) 
go
insert test ( a, b , c ) select  top 10000 'ABCDEF' , 123 , 'xyz' from syscolumns a join syscolumns b on 1=1
insert test ( a, b , c ) select  top 10000 'ijk' , 456 , 'ijk' from syscolumns a join syscolumns b on 1=1
go
          (10000 row(s) affected)
(10000 row(s) affected)

La deuxième insertion (ijk) est nécessaire pour démontrer la compression de type PAGE présentée ultérieurement dans cet article.

execute sp_spaceused test
          name        rows    reserved       data    index_size  unused
-------------------------------------------------------------
test       20000      584 KB     560 KB          8 KB   16 KB

La taille de la table est de 560 Kb, soit 70 pages (560/8), chaque colonne utilisant l’espace alloué de taille fixe correspondant au typage défini.

La table est maintenant compressée en mode ROW :

alter table test rebuild with ( data_compression = ROW )
go

Command(s) completed successfully.

execute sp_spaceused test

name        rows    reserved       data    index_size  unused
-------------------------------------------------------------
test       20000      336 KB     312 KB         86 KB   16 KB

La taille de la table est désormais de 312 Kb, soit 39 pages (312/8) pour un gain de plus de 55% environ.

Compression de pages (PAGE)

La stratégie de compression de pages est une version améliorée de la compression de lignes (ROW) par l’ajout dans la page d’une structure référençant les valeurs répétitives. Les valeurs répétitives sont stockées en entête de page et seule la référence à cette structure est matérialisée dans les lignes.

Cette structure de référence peut contenir :

• des valeurs exactes : compression de pages avec dictionnaire (Dictionary compression).
• des préfixes récurrents : compression de pages avec préfixes (Prefix compression).

La stratégie appliquée n’est pas paramétrable, le moteur décide de la méthode en fonction de seuils internes. Si aucune compression par page n’est possible, elle n’est pas réalisée, la compression de lignes (ROW) est appliquée néanmoins.

La stratégie de compression de pages (préfixes ou dictionnaire) est également réévaluée lors d’opérations d’écritures comme une coupure de pages (split de pages) par exemple.

Les compressions de pages par préfixes et par dictionnaire sont complémentaires et peuvent donc être implémentées au sein d’une même page.

Compression de pages par préfixes (Prefix compression)

Dans les faits, cette structure est une ligne supplémentaire en début de page référençant la valeur de la colonne contenant le tronc commun le plus grand dans la page. Cette ligne est nommée *Anchor Record*. Dès lors, chaque colonne contient une donnée relative à cette ligne sous le format <T><DATA>, où <T> correspond à la taille de l’ancre (anchor) à prendre en compte et <DATA> à la valeur à ajouter.

En orangé sur l’illustration ci-dessus, figure la nouvelle ligne d’ancres (anchor record). La valeur 123 par rapport à sa compression optimisée en mode ROW est désormais un préfixe.

Sur la ligne 2, première colonne, la valeur 6SSE est interprétée en prenant les 6 premiers caractères de l’ancre COMPRE auxquels on ajoute SSE, ce qui donne la valeur souhaitée.

La valeur null (représentée ici par <>) signifie 'valeur complète de l’ancre'. Elle est différenciée de la valeur NULL par la structure de 4 bits évoquée précédemment.

Information paradoxale à noter : une colonne (PRESSE dans l’exemple) dont le contenu n’a aucun point commun avec l’ancre consomme plus d’espace après compression.

Dernier élément, la deuxième colonne a deux valeurs d’ancre possibles : PRESSE et TOTAUX. Une seule valeur étant permise pour l’ancre, c’est la valeur qui permet le plus fort taux de compression dans la page qui est choisie.

Compression de pages avec dictionnaire (Dictionary compression)

Un dictionnaire est créé si et seulement si une valeur strictement identique est présente dans AU MOINS 2 colonnes différentes.

Le dictionnaire est un tableau à une dimension, intégré à la ligne d’ancres (anchor record). La première entrée a pour référence 0, la deuxième 1, etc.

Dans le mode dictionnaire, chaque colonne possède toujours sa propre valeur de référence dans la ligne d’ancres, mais on observe une nouvelle entrée qui est le tableau des valeurs répétées. Dans cette stratégie, la colonne contient la position de la valeur dans ce tableau de valeurs répétées et non sa valeur (en rouge dans l’illustration).

Ainsi, la chaîne PRESSE, répétée et présente dans 2 colonnes différentes, est intégrée dans le dictionnaire et représentée par l’identifiant [0] (première entrée : [0], deuxième entrée : [1], etc.).

Compression de pages - Exemple

Poursuivons l’exemple défini précédemment dans lequel la compression en mode ROW avait divisé la taille de l’objet par plus de deux.

La compression en mode PAGE est à présent appliquée.

alter table test rebuild with ( data_compression = PAGE )
go

Command(s) completed successfully.

execute sp_spaceused test

name        rows    reserved       data    index_size  unused
-------------------------------------------------------------
test       20000      272 KB      232 KB         8 KB   32 KB

La taille des données de la table est désormais de 232 KB, soit 34 pages (232/8) et un gain de 25 % supplémentaires environ par rapport au mode ROW.

Rappel : deux insertions différentes ont été réalisées en préparation de cette table test. La première insertion génère du contenu différent dans chaque colonne, la seconde insertion génère des valeurs répétées dans 2 colonnes différentes induisant la création d’un dictionnaire.

Performances - Remarques générales (typage, surcoût…)

Première remarque : la stratégie employée ne compresse efficacement que les modèles suboptimaux. Cela signifie que meilleur est le typage du modèle, plus faible est le taux de compression.

Dans l’exemple illustré ci dessous, 50 000 lignes contenant la chaîne 'xxx' on été insérées dans des tables dont seul le typage de la donnée varie. Le gain obtenu avec la compression est relativement faible lorsque les types sont soit déjà variables (varchar), soit correctement définis (char(5)).

Deuxième remarque : le surcoût d’un accès à une table compressée est comparable au coût d’une jointure avec typage hétérogène (cast), +10% en plus.

Troisième remarque : bien que potentiellement plus efficace en terme d’espace gagné, l’accès à un objet compressé par PAGE est plus coûteux qu’une simple compression par ROW. Le choix d’une stratégie - par objet - doit donc être réfléchi.

Mauvaise nouvelle : les pages compressées montées en cache restent compressées. C’est une mauvaise nouvelle car même si cela permet de stocker plus d’informations en mémoire, cela implique cependant un conversion systématique de ces données manipulées. Surconsommation CPU donc et dégradations de performance pour les données 'chaudes', même en lecture. Ces conversions systématiques des données compressées en cache nuisent à la montée en charge de l’instance. Conséquence : il est nécessaire de modifier la structure de la table en la partitionnant, de façon à ne compresser que les données - et les indexes - les moins sollicités.

Bancs d’essais - benchmarks

L’impact de la compression est tellement dépendant du modèle de données, de son typage, du volume de la table, de la puissance des machines, du compromis espace/cpu voulu, qu’il est hasardeux de fournir des mesures de performance.

L’exercice est quand même réalisé ici, exercice permettant à la fois de parcourir quelques syntaxes mais aussi de montrer des effets possibles.

Contexte des benchmarks

Un jeu d’enregistrements totalement artificiel de 200 Mo environ est créé. Ce jeu mélange plusieurs types différents : types fixes et variables, chaînes, valeurs numériques.

Plusieurs scénarios sont testés : compression par ligne, par page, partitionnement, requête avec scan complet, sur un ensemble moyen et sur petits volumes. Les résultats présentés sont les moyennes de 3 tests réalisés avec données en cache, les résultats sont mesurés avec les commandes de base statistics io, time.

Les différents cas testés sont référencés comme suit dans les tableaux et/ou graphiques qui suivent :

-- -------------------------------------------------------------
-- table de réference

select top 50 name into #names from sysobjects
SELECT top 500 ID=IDENTITY (int, 1, 1) into #ids from sysobjects a, sysobjects b,sysobjects c
select top 10 status=ID into #status from #ids 

-- structure initiale
select  'name'   = convert(varchar(50), name) ,
        'dt'     = dateadd(day,- id,'20110101'),
        'status' = status,
        'value'  = ABS(CAST(NEWID() AS binary(6)) % 1000) ,
        'ownr'   = convert(char(20), replicate('x',status)),
        'comment'= convert(varchar(100),name + ' ' + CONVERT(char(50),id) + convert(varchar(2),status))
into    data_ref
from    #names
join    #ids on 1=1
join    #status on 1=1
where   dateadd(day,- id,'20110101') between '20100101' and '20101231'

-- Pour la compression page : au moins 2 colonnes avec la même valeur
update data_ref set comment = name where ownr in ( 'xx','xxx' ) 

-- Ajout de l'historique 
insert data_ref select name, DATEADD(year,-1,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-2,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-3,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-4,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-5,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-6,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-7,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-8,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-9,dt),status,value,ownr,comment
 from data_ref where dt between '20100101' and '20101231'
go
create clustered index clu_dt on data_ref(dt)
go


-- -------------------------------------------------------------
-- Partitionnement

CREATE PARTITION FUNCTION [part_fct_dt](datetime)
   AS RANGE LEFT FOR VALUES ('20020101','20030101','20040101','20050101','20060101',
                             '20070101','20080101','20090101','20100101','20110101')

CREATE PARTITION SCHEME [part_sch_dt] AS PARTITION [part_fct_dt]
   TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
       [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])


-- -------------------------------------------------------------
-- table entière standard: 

select * into data from data_ref 
create clustered index clu_dt on data(dt) with sorted_data

-- -------------------------------------------------------------
-- table entière compressée : rows

select * into data_comp_row from data_ref
create clustered index clu_dt on data_comp_row(dt) with sorted_data
ALTER TABLE data_comp_row REBUILD WITH (DATA_COMPRESSION = ROW)

-- -------------------------------------------------------------
-- table entière compressée : page

select * into data_comp_page from data_ref
create clustered index clu_dt on data_comp_page(dt) with sorted_data
ALTER TABLE data_comp_page REBUILD WITH (DATA_COMPRESSION = PAGE);

-- -------------------------------------------------------------
-- Table partitionnée sans compression

select * into data_part from data_ref
create clustered index clu_dt on data_part(dt) ON part_sch_dt(dt) 
go

-- -------------------------------------------------------------
-- Table partitionnée : partiellement compressée ROW
select * into data_part_comp_row from data_ref
create clustered index clu_dt on data_part_comp_row(dt) ON part_sch_dt(dt)
ALTER TABLE data_part_comp_row
   REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1 TO 8) ) ;
go

-- -------------------------------------------------------------
-- Table partitionnée : partiellement compressée PAGE

select * into data_part_comp_page from data_ref
create clustered index clu_dt on data_part_comp_page(dt) ON part_sch_dt(dt)
ALTER TABLE data_part_comp_page
   REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1 TO 8) ) 

Taille des objets

L’efficacité de la compression est patente, près de 20% de réduction pour le mode ROW et 40% pour le mode PAGE.

À noter un impact important du partitionnement : les gains sont très nettement moins élevés, respectivement 16% pour le mode ROW et 30% pour le mode PAGE. Cela montre l’importance de l’ordre de tri des données lors de la prise en compte de la compression.

Test #1 : Sélection grande plage - valeurs anciennes

Ce test consiste à extraire 6 mois de données - anciennes (dans la plage de compression), soit 107000 lignes.

select * 
into   #tmp1 
from   data
where  dt between '20050601' and '20051231' 

Pas de surprise, le nombre d’I/O relevé par requête est fonction du nombre de pages de la table. Autre élément prévisible, la compression par page est toujours plus lente que par ligne.

Sur les tables non partitionnées, la durée d’exécution est sensiblement identique. Pas de dégradation notable, bilan positif donc, bien que décevant au regard du nombre d’I/Os sauvés.

Le cas des tables partitionnées est plus inquiétant, la dégradation est très significative avec près de 50% de durée supplémentaire pour le mode PAGE alors même que le nombre d’I/Os est 40% inférieur.

Premier test mitigé donc.

Test #2 : Sélection plage moyenne - valeurs anciennes

Ce test consiste à extraire 1 mois de données - anciennes (dans la plage de compression), soit 15500 lignes.

select * 
into   #tmp1 
from   data
where  dt between '20050601' and '20050701' 

On retrouve notre nombre d’I/O fonction du nombre de pages de la table.

Confirmation du mauvais comportement de la compression par PAGE pour une table partitionnée : près de 50% de temps supplémentaire.

De manière générale la compression influe négativement sur les performances et de manière plus marquée que lors du test précédent. Rappel, les tests sont menés avec les données en cache, c’est donc le surcoût de la décompression de chaque ligne qui est observé ici.

Test #3 : Sélection grande plage - valeurs récentes

Ce test consiste à extraire 6 mois de données - récentes (dans la plage de données non compressées pour les partitions).

select * 
into   #tmp1 
from   data
where  dt between '20100601' and '20101231' 

Concernant les trois premiers tests pour la table non partitionnée, le résultat ne change pas par rapport au test #1.

Phénomène rassurant, les performances d’accès à des partitions non compressées sont cohérentes. Bien que les différentes mesures montrent une durée très légèrement - mais systématique - supérieure.

Test #4 : Sélection plage moyenne - valeurs récentes

select * 
into   #tmp1 
from   data
where  dt between '20100601' and '20100701' 

Les résultats, qui confirment ceux du test #2, sont une nouvelle fois mitigés. Le coût de la décompression en cache a une influence très forte et négative sur les performances. Dans le cadre d’une table non partitionnée, +60% de dégradations pour une compression en mode ROW et +100% pour une compression en mode PAGE, +35% de dégradations dans les autres cas.

Test #5 : insertions

Dans ce test, l’équivalent de 6 mois de données est inséré en mode insert/select

select * into #tmp1 from data_ref where dt between '20050601' and '20050701' 
go
insert data select * from #tmp1
go

La différence en terme d’I/Os entre ces tests n’est pas significative ou plutôt les quelques centaines d’I/O gagnés selon le contexte n’ont pas d’impact positif sur les performances. En revanche, la conséquence est réelle pour les performances : près de 30% de dégradation pour le mode PAGE, 10% pour le mode ROW.

On retrouve peu ou prou le comportement illustré par la sélection du test #1.

Test #6 : Mises à jour

L’équivalent de 1 mois de données pour deux colonnes de type statique est modifié dans ce test.

update dataset comment='xxx', value=4 where dt between '20050601' and '20050701' 

Aucune suprise, le comportement est cohérent avec les mesures réalisées jusqu’ici : moins d’I/Os mais plus longue durée d’exécution.

L’impact de la compression PAGE est très négatif lors des mises à jour : avec ce type de compression, le temps de mise à jour est 2 fois supérieur dans le cas d’une table non partitionnée, et 4 fois supérieur dans le cas d’une table partitionnée !

Test #7 : Suppressions

L’équivalent de 1 mois de données est supprimé ici.

delete datawhere dt between '20050601' and '20050701' 

À noter que notre exemple contient un index clustered : la suppression provoque un tri et une réécriture de pages de données amplifiant les effets observés lors de la mise à jour.

Conclusion

La promesse de gain d’espace est tenue par cette option de compression de données, même si son efficacité n’est spectaculaire que sur des modèles incorrectement définis.

Cependant, moins d’I/Os induits ne signifie pas systématiquement des meilleurs temps de réponse, la transformation systématique des lignes compressées ayant encore un coût très important

Bien que décevants au regard de la promesse initiale, ces relatifs mauvais résultats ne doivent pas occulter les avantages certains de cette solution. Maîtriser la croissance d’une base, les coûts induits en terme de maintenance et de stockage rendent cette option totalement pertinente.

De même, les impacts négatifs peuvent s’avérer transparents ou du moins acceptables, selon le contexte de mise en place.

Aussi, l’implémentation de la compression ne peut être réalisée sans analyse ni tests préalables.

Quelques conseils

Avant toute mise en œuvre de la solution de compression

• évaluer la pertinence du modèle et son typage.
• partitionner les tables importantes en séparant les données utiles des autres.
• dans le cadre d’applications OLTP à forte concurrence d’accès, étudier très soigneusement la mise en place de la solution. La compression doit être appliquée à bon escient.
• privilégier la compression ROW.

Requêtes utiles

La compression est-elle utilisée dans la base en cours ?

if exists ( select 1 from sys.dm_db_persisted_sku_features where feature_name='Compression' ) 
     print 'Compression utilisee'
else
     print 'Pas de table compressee'
go

Quelles tables sont compressées ?

select 'name' = OBJECT_NAME(object_id), index_id, partition_number, rows, 
       'type' = data_compression_desc 
from   sys.partitions 
where  data_compression > 0
go
          name   index_id   partition_number   rows    type
--------------------------------------------------
test          0                  1  20000    PAGE

Statistiques d’utilisation des objets

select * from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
go

...

retourne pas mal d’informations intéressantes comme le nombre de lectures, d’opérations d’écriture.

Supprimer une compression

alter table ... with ( data_compression = NONE ) 
go

Estimer le gain de compression

execute sp_estimate_data_compression_savings 'dbo', 'data', NULL, NULL, 'ROW'
go
          Object  schema  inx_id  part#  curSize(Kb)  EstimateSize(Kb) ...
------- ------- ------- ------ ------------ ---------------- ...
data    dbo     1       1           245896            196512 ...

L’estimation est réalisée par échantillonage et le résultat ici très pertinent.

execute sp_spaceused data_comp_row
go
          Object            rows     reserved       data ...
-------------- ------- ------------ ---------- ...
data_comp_row  1809500    197648 KB  196888 KB ...

Annexe

Démonstration de la compression ROW par calcul

La formule ci-dessous est extraite de MSDN (Estimating the size of a Heap ) et montre par le calcul l’effet du transtypage.

-- NB  = 2 + ((NC + 7) / 8) 
       = 2 + ((1  + 7) / 8)
       = 3
-- RS  = FDS + VDS + NB + 4 
       = 10  + 0   + 3  + 4
       = 17
-- RPP = 8096 / (RS + 2) 
       = 8096 / (17 + 2)
       = 426
-- NP  = NR    / RPP 
       = 10000 / 426
       = 24

-- NB  = 2 + ((NC + 7) / 8) 
       = 2 + ((1  + 7) / 8)
       = 3
-- RS  = FDS + VDS + NB + 4 
       = 0   + 4   + 3  + 4
       = 11
-- RPP = 8096 / (RS + 2) 
       = 8096 / (11 + 2)
       = 622
-- NP  = NR    / RPP 
       = 10000 / 622
       = 16

On retrouve les valeurs équivalentes à celles, réelles, retournées par sp_spaceused.