Data Compression, comment l’implémenter ?

ROW ou PAGE?

Il faut maintenant choisir le mode de compression à adopter pour chacun d’entre eux.. Pour cela, il faut jongler entre les critères précédemment retenus : le nombre de SELECT, et le nombre d’UPDATE car plus il y aura de SELECT et moins d’UPDATE, plus la compression PAGE sera indiquée. A l’inverse, moins il y aura de SELECT et plus d’UPDATE, plus la compression ROW sera quant à elle indiquée. Nous pouvons représenter ces contraintes comme suit : figure 2(voir Club Abonné).

Data Compression

Le tout est de savoir ou positionner le curseur, ce qui devra se faire au cas par cas.
Il est intéressant de noter que lorsque l’on active la compression de niveau PAGE sur un index, seul le niveau feuille recevra ce niveau de compression. Le ou les niveaux intermédiaires seront quant à eux compressés en ROW et ce pour des raisons de performance :

–    Les pages qui composent les niveaux intermédiaires sont peu nombreuses : le gain attendus en les compressant en PAGE serait minime

–    Ces pages sont très souvent mises à jour, les compresser en niveau PAGE irait à l’encontre des principes précédemment évoqués.

Mode opératoire

Tous les exemples qui suivent sont accompagnés d’illustrations que vous pouvez retrouver dans le numéro de décembre 2012 d’IT Pro Magazine, accessible depuis le Club Abonné.

La compression peut être implémentée graphiquement via la management studio, ou en Transact-SQL. Les assistants graphiques étant clairs et intuitifs, je ne m’étendrai pas sur le sujet et j’utiliserai les commandes transact-SQL dans les exemples à suivre.

Le principe de base est le suivant : pour activer la compression sur un objet donné, il faut procéder à sa reconstruction, en précisant le niveau de compression souhaité : NONE, ROW ou PAGE. Comme il s’agit effectivement de reconstruire un objet, l’opération peut se révéler extrêmement couteuse en temps et en ressource. Il faudra donc s’assurer dans un premier que le moteur dispose de suffisamment  de ressource pour mener à bien l’opération (on veillera en particulier à ce qu’il y ait suffisamment d’espace dans la base de données, dans le journal de transaction et dans la tempdb).

Ces précautions prises, voyons cela concrètement, au travers d’exemples. Nous contrôlerons l’état de la compression à chaque étape. Je rappelle que la compression peut s’activer au niveau d’une partition, nous trouvons donc l’information du type de compression appliqué dans la dmv sys.partitions. La commande suivante nous donnera l’état de la compression pour chaque partition (s’il y en a plusieurs) de chaque index de notre table exemple, ainsi que le nombre de pages utilisé par l’objet : figure 3.

SELECT
    object_name(SP.object_id) AS ‘Table’,
    SI.name AS ‘Index’, Si.type_desc ,
    SP.partition_number,data_compression_desc,
    SU.total_pages,
    SU.total_pages*8/1024 TailleMB
FROM sys.indexes SI
INNER JOIN sys.partitions SP
INNER JOIN sys.allocation_units SU ON SU.container_id = SP.hobt_id
ON SI.object_id = SP.object_id AND SI.index_id = SP.index_id
WHERE object_name(SP.object_id) = ‘Catalog’
ORDER BY [table], [Index], partition_number

Exemple 1 : compression PAGE sur un HEAP

Reprenons notre table catalog déjà mentionnée dans cet article. Nous avons préalablement supprimé son Index Cluster, pour répondre au présent test. Voici sa composition initiale : figure 4.

Nous voyons qu’aucun index n’est compressé au départ. En effet, rien n’avait été précisé au moment de la création de la table, le comportement par défaut étant de ne pas activer la compression.

Pour compresser le HEAP, nous allons lancer sa reconstruction en précisant un niveau de compression PAGE, car nous l’avons vu précédemment, le type ROW offrait un taux de compression trop faible pour être réellement intéressant: figure 5.

Nous avons maintenant le niveau de compression suivant : figure 6.

Nous voyons que le HEAP est maintenant compressé au niveau page, et que sa taille est passée de  4189MB  à 2506MB

Continuons et compressons les deux index non cluster : figure 7.

Et nous obtenons maintenant : figure 8.

Finalement, la taille totale de la table (data + index) est passée de 5143MB à 2831MB.

À noter que lorsque l’on active la compression sur le HEAP et qu’il est donc reconstruit, les index non cluster le sont également, en interne, mais sans toutefois hériter du niveau de compression. Cela est dû au fait que la reconstruction du heap provoque le changement de ses RIDs, qui sont référencés dans les index non cluster, nécessitant ainsi leur reconstruction. Dans notre exemple, les index non cluster sont donc reconstruit deux fois, une première fois en interne par le rebuild du HEAP pour mettre à jour les RIDs, une seconde fois manuellement afin de leur appliquer le niveau compression souhaité. Il n’est malheureusement pas possible de reconstruire le HEAP et ces index en une seule fois tout en activant le niveau de compression souhaité.

Une alternative cependant consisterait à créer un index cluster avec le mode de compression désiré, puis en le reconstruisant à l’aide du mot clé ALL (voir exemple ci-dessous) avant de le supprimer. Le heap résultant ainsi que tous les index non cluster conserverai alors le niveau de compression. Selon les cas, cette méthode peut s’avérer plus rapide suivant les cas (nombre d’index non cluster et volumétrie de la table).

Exemple 2 : compression PAGE sur un Index CLUSTER

Il s’agit dans ce cas de reconstruire l’index cluster, en lui appliquant le paramètre de compression souhaité. Si tous les index non cluster doivent également être compressés avec le même niveau de compression que l’index cluster, il est possible de tout reconstruire en une fois, à l’aide du mot clé ALL : figure 9.

Lancement de la compression de tous les index simultanément : figure 10.

Voici ce que l’on obtient : figure 11.

Tous les index ont bien été reconstruits, et bénéficient maintenant du niveau de compression PAGE.

Exemple 3 : Compression PAGE / ROW sur partition

Tester la compression au niveau partition. Nous avons préalablement partitionné notre table catalog pour obtenir le résultat suivant : figure 12.

Activer la compression page sur les objets au niveau de la partition 1, la compression ROW sur la partition 2 tout en laissant les partitions 3 et 4 non compressées : figure 13.

Nous obtenons ainsi le résultat suivant : figure 14.

Attention néanmoins à certains problèmes de performances qui peuvent se produire lorsque l’on active différents niveaux de compression sur les partitions d’une table. Je vous invite à prendre connaissance de la KB suivante pour en savoir plus.