Problème :

On stocke des données XML dans une table qui commence à grossir sérieusement et il devient important de réfléchir à des solutions pour essayer de gagner un peu de place de stockage pour éviter que la table explose dans les mois à venir :

Il n’est pas possible de compresser les données à l’aide d’un index Clustered ColumnStore :

La compression de donnée ne donne pas un bien meilleur résultat :

On voit dans le meilleur des cas un gain de 500 Mo soit moins de 1% de gain.

Solution 1 :

On va tenter d’utiliser la méthode « Compress » fournie par Microsoft, pour ça on va recréer une nouvelle table en reprenant les données initiales en les compressant :

SELECT  [pk_contenu_xml], 
        [numero_facture], 
        [fichier_xml], 
        COMPRESS(CONVERT(VARCHAR(MAX), [contenu_xml])) as [contenu_xml], 
        [formulaire], 
        [version], 
        [flag_facture_inserer], 
        [type_bord], 
        [audit_add_date], 
        [audit_add_fk_idsuichgfic], 
        [audit_add_fk_idsuitrt]
INTO RV_XML.[dbo].[contenu_xmlrv_edd_compress]
FROM RV_XML.[dbo].[contenu_xmlrv_edd]

Après création de la table on peut voir que la taille est bien moindre, la compression a permis de réduire la base à 20% de sa taille initiale.

Les données sont cependant inexploitables à présent :

Pour restituer les données on va créer une vue qui décompresse les données à l’aide de la méthode « Decompress » et les ressort au format XML :

CREATE VIEW v_contenu_xmlrv_edd
AS
SELECT  [pk_contenu_xml], 
        [numero_facture], 
        [fichier_xml], 
        CONVERT(XML, CONVERT(VARCHAR(MAX), DECOMPRESS([contenu_xml]))) as [contenu_xml], 
        [formulaire], 
        [version], 
        [flag_facture_inserer], 
        [type_bord], 
        [audit_add_date], 
        [audit_add_fk_idsuichgfic], 
        [audit_add_fk_idsuitrt]
FROM RV_XML.[dbo].[contenu_xmlrv_edd_compress]

Les données sont désormais sous le format initial :

Solution 2 :

On va maintenant essaye de simplement convertir la colonne en NVARCHAR(MAX) et d’activer la compression sur la table.

SELECT  [pk_contenu_xml], 
        [numero_facture], 
        [fichier_xml], 
        CONVERT(VARCHAR(MAX), [contenu_xml]) as [contenu_xml], 
        [formulaire], 
        [version], 
        [flag_facture_inserer], 
        [type_bord], 
        [audit_add_date], 
        [audit_add_fk_idsuichgfic], 
        [audit_add_fk_idsuitrt]
INTO RV_XML.[dbo].[contenu_xmlrv_edd_varchar]
FROM RV_XML.[dbo].[contenu_xmlrv_edd]

Après création de la table on voit qu’elle est toujours de la même taille.

Le gros avantage cependant est que les données restent toujours plutôt exploitables pour peu qu’on n’essaye pas d’appliquer des fonctions XML dessus.

On va maintenant essayer de mettre de la compression de données pour voir si on peut améliorer les choses :
!

On voit que la compression n’apporte pas grand chose, ce qui est assez logique puisque les données de type LOB sont stockées hors de la table, la compression n’a donc pas d’effet ici.

Essayons aussi de créer un index Cluster ColumnStore.

On voit qu’il n’y a pas de soucis lors de la création de l’index Cluster Columnstore cette fois.

On voit qu’une compression a bien été effectuée (~30% de la taille initiale), même si elle est moins importante que dans la solution précédente.

Pour restituer les données au format initial, on va simplement refaire une vue qui va convertir les données VARCHAR(MAX) en XML :

CREATE VIEW v_contenu_xmlrv_edd
AS
SELECT  [pk_contenu_xml], 
        [numero_facture], 
        [fichier_xml], 
        CONVERT(XML, [contenu_xml]) as [contenu_xml], 
        [formulaire], 
        [version], 
        [flag_facture_inserer], 
        [type_bord], 
        [audit_add_date], 
        [audit_add_fk_idsuichgfic], 
        [audit_add_fk_idsuitrt]
FROM RV_XML.[dbo].[contenu_xmlrv_edd_varchar]

Conclusion :

La méthode de compression de Microsoft fourni des résultats satisfaisants, même si on espérait une compression plus proche des 90% comme cela est souvent le cas lorsqu’on archive des fichiers XML de par la répétition des balises qui permet bien souvent une très bonne compression.

Le gros avantage est la place que cela va nous permettre de gagner, cependant il y a quand même 2 petites attentions à noter :

Tout d’abord il y a un ajout d’un overhead au niveau du CPU que ça soit lors de l’insertion de la ligne pour la compression, ou bien lors de la lecture de lignes via la vue pour la décompression de la donnée.
Ensuite cela veut dire que le point d’entrée et de sortie ne sont pas les mêmes, notre procédure qui est chargée d’insérer les données dans la table est unique et donc il est simple de modifier ce point d’entrée, la vue sera renommée avec l’ancien nom de la table pour ne pas avoir à modifier les nombreuses procédures qui utilisent ces données. Cette action n’est pas forcément simple à réaliser dans tous les environnements en fonction des utilisations.

La première solution sera plutôt adaptée à des utilisations pour lesquelles le nombre de ligne accédées est relativement restreint de par le ratio de compression apporté.

Dans le cas d’un usage plus important des données, comme dans le cas dde balayages pour des requêtes BI, la deuxième solution sera probablement plus indiquée.

D’autres options peuvent être explorées tel que de compresser via une DLL intégrée ou un outil tiers, la compression en sera surement plus optimale et d’autres options telles que le chiffrement des données peuvent ainsi être intégrées, mais cela demande plus de travail qui n’était pas pertinent dans notre cas.

Erik Rob in a deeper article, detail how MSSQL manage XML