[SQL Server] Index : petit tour d’horizon sur l’index column store

Ce billet présente les indexes column store, leurs bénéfices et leurs limites.

Présentation des indexes column store

Notions d’index column store

Les indexes column store sont de nouveaux objets d’indexation apparus à partir de SQL Server 2012, et permettant d’optimiser les performances de requêtes sur un gros volume de données, généralement stockées dans des datawarehouses.

Dans ce blog a été abordé l’architecture d’un index « traditionnel » et la manière dont les données sont gérées. Supposons que l’on possède la table suivante :


Du point de vue d’un index « traditionnel », le stockage se fera comme suit :


Il s’agit, bien sûr, d’un schéma fait arbitrairement pour la démonstration. Et on note que les données sont stockées par ligne.

Contrairement aux indexes « traditionnels » qui stockent les données en ligne (au sein d’une structure B-Tree), les indexes column store utilisent les colonnes pour leur stockage, tout en les compressant drastiquement afin de réduire les opérations I/O des requêtes voulant les traiter.

Concrètement, les données de chaque colonne sont stockées ensemble dans une même page. Le schéma – lui aussi arbitraire – ci-dessous donne un petit aperçu sur la manière dont les données seront stockées par l’index column store:


Comment l’index column store fonctionne…

Pour comprendre comment un index column store fonctionne, il faut s’intéresser à sa structure, à commencer par ses zones de stockage : les segments et les dictionnaires.

Un segment est un objet LOB (Large Object Binary) hautement compressé et pouvant contenir jusqu’à un million de lignes. Les données au sein d’un segment correspondent à un groupe de lignes consécutives. Et chaque colonne possède une entrée pour chaque segment, dont les informations peuvent être consultées dans la vue de catalogue sys.column_store_segments.

Une colonne peut être partagée en plusieurs segments, et chaque segment peut être constitué de plusieurs pages de données, ce qui fait que lors de la compression en mémoire, les données sont transférées du disque vers la mémoire par segment, et non par page.

Du fait de la limitation de lignes par segment, toutes les données d’une colonne ne peuvent pas toujours être stockées au sein d’un simple segment. Et dans ce cas de figure, pour chaque colonne, des segments supplémentaires sont créés et regroupés au sein de groupes de lignes multiples.


On peut souligner que dans le schéma ci-dessous des colonnes utilisent un dictionnaire. En effet, outre les segments, un index column store possède un autre élément de stockage qui a son importance : les dictionnaires.

Les dictionnaires sont des objets très utilisés dans le processus de stockage en colonne comme moyen d’encodage efficace des grands types de données, à commencer par les chaînes de caractères. Les banques de données – au sein des segments – sont représentées comme des numéros d’entrée dans le dictionnaire, et les valeurs réelles sont, quant à elles, stockées dans celui-ci.

Cette approche de fonctionnement permet de compresser efficacement les valeurs redondantes, mais apporte de mauvais résultats si toutes les valeurs sont distinctes (dans la mesure où la volumétrie du stockage augmente…), ce qui fait que les larges colonnes possédant des valeurs distinctes font de mauvaises candidates à l’indexation column store.

L’index column store contient, pour chaque colonne, 2 types de dictionnaires :

  • Dictionnaire primaire : il s’agit d’un dictionnaire global, utilisé par tous les segments d’une colonne de chaînes de caractères (et parfois, non-chaînes de caractères, avec valeurs distinctes).
  • Dictionnaire secondaire : il s’agit d’un dictionnaire d’entrées non-référencées dans un dictionnaire principal. Il peut être partagé par plusieurs segments d’une colonne, via une relation 1-N.

Les dictionnaires sont consultables dans la vue de catalogue sys.column_store_dictionaries.

Notons que :

  • Toute colonne n’utilise pas forcément un dictionnaire.
  • Les colonnes non-chaînes peuvent utiliser un dictionnaire primaire.
  • Une colonne de chaînes de caractères possède toujours un dictionnaire primaire, et certains de ses segments peuvent utiliser un dictionnaire secondaire.

Et enfin, le stockage des dictionnaires est similaire à celui des segments, avec une taille limitée à 2 Go et stockage en BLOB sur une unité d’allocation.

Chaque segment de colonnes possède deux informations intéressantes : min_data_id et max_data_id (tous les deux consultables dans la vue de catalogue sys.column_store_segments).

Ces métadonnées représentent les valeurs minimales et maximales de colonnes stockées dans le segment cible. Ces valeurs ne peuvent pas être aisément interprétées directement dans la vue de catalogue sys.column_store_segments car elles doivent être décodées suivant le type d’encodage du segment concerné, et éventuellement suivant le dictionnaire utilisé.

En sachant qu’un segment contient des valeurs minimales et maximales, l’optimiseur de requêtes, via le moteur de stockage, peut décider d’ignorer un ou plusieurs segments ne respectant pas les conditions de la requête traitée. On parle ici d’élimination de segment (ou segment elimination).

Performances et index column store

L’index column store offre des améliorations non-négligeables lors du traitement de requêtes destinées à balayer un gros volume de données.

 Compression de données et réduction des opérations I/O

Pour doper les performances d’une requête, l’index column store procède en compressant en mémoire les données les plus importantes via l’utilisation de la technologie xVelocity (ex-VertiPaq). C’est cette compression en mémoire qui permet de réduire les opérations I/O en limitant le nombre de lectures disque et en augmentant le taux de mise-en-cache de données dans la mémoire tampon (buffer cache hit ratio).

    Traitement en mode batch

Avant SQL Server 2012, quand l’optimiseur de requêtes traitait une requête, il bénéficiait seulement du mode ligne (row mode), les traitements étant ainsi effectués que sur une seule ligne à la fois. Avec l’incorporation de l’index column store, c’est un nouveau mode de traitement de requête qui a été implémenté : le mode batch.

Ce mode de traitement est basé sur un ensemble d’algorithmes conçus pour traiter les données en batches séparés.

Chaque batch est stocké comme un vecteur dans une zone dédiée de la mémoire, et représente typiquement près de 1000 lignes de données. Dans la mesure où l’index column store a été, à la base, développé pour les datawarehouses, où les requêtes balayent généralement un grand nombre de lignes de données en utilisant peu de colonnes, il intègre naturellement l’utilisation du batch mode en plus de la compression de données. De cette façon, il favorise le traitement de données compressées par bloc.

Notons que l’optimiseur de requêtes peut, toujours depuis SQL Server 2012, utiliser à la fois le mode batch et le mode ligne lors du traitement d’une requête. Pour ce faire, l’optimiseur peut créer des plans qui intègrent des sous-arbres en mode batch, même si l’arbre principal est en mode ligne.


Utilisation d’un index column store

    Utilisation et bonnes pratiques courantes

La syntaxe basique de création d’un index column store en T-SQL est la suivante :

CREATE [ CLUSTERED | NONCLUSTERED ] COLUMNSTORE INDEX index_name
ON [database_name. [schema_name ] . | schema_name . ] table_name
( column [ ,...n ] )
[ WITH ( <columnstore_index_option> [ ,...n ] ) ]
[ ON {
partition_scheme_name ( column_name )
| filegroup_name
 | "default"
 }
]
[ ; ]
<columnstore_index_option> ::=
{
 DROP_EXISTING = { ON | OFF }
 | MAXDOP = max_degree_of_parallelism
}

Le mot-clé NONCLUSTERED est facultatif. Et l’utilisation de CLUSTERED n’est possible qu’à partir de SQL Server 2014.

Maintenant se pose la question suivante : quelles sont les bonnes pratiques d’utilisation d’un tel index ? Pour une meilleure utilisation de l’index column store, voici quelques règles principales à respecter :

  1. Cibler les tables les plus larges. Les indexes column store sont plutôt à créer sur des tables de faits – au sein d’un datawarehouse. Toutefois, en cas de présence d’une table de dimensions assez large, contenant des millions d’enregistrements, l’utilisation d’un index column store peut être envisagée.
  2. Cibler toutes les colonnes de la table bénéficiant de l’index column store. Cela est d’autant plus recommandé qu’une requête qui référencie une colonne non-indexée par le column store ne pourra pas pleinement bénéficier de ses avantages.
  3. Mettre à jour régulièrement les indexes et contrôler son état de fragmentation. Pour ce faire, un plan de maintenance contenant des opérations de réindexation et de mises à jour de statistiques peut être implémenté afin de maintenir les indexes en bonne santé.
  4. Eviter les jointures de chaînes de caractères directement sur les colonnes indexées par le column store. Les jointures sur des colonnes de chaînes de caractères sont moins efficaces que celles de type entier. Privilégier les jointures sur des clés surrogates ou colonnes d’entiers.
  5. Si SQL Server 2012, éviter les jointures externes sur les tables indexées par le column store. En effet, les jointures externes ne peuvent pas bénéficier du traitement en mode batch, sauf à partir de SQL Server 2014.
  6. Eviter l’utilisation NOT IN (<sous_requête>) sur des tables indexées par le column store. Cette opération force le passage en mode ligne. En revanche, NOT IN (<liste_valeurs>) n’a pas ce problème.

A noter que l’utilisation d’UNION ALL entre deux tables, l’une indexée par le column store, l’autre non, empêche l’optimiseur de requêtes de bénéficier du mode batch.

    Limites principales

Comme tout objet qui se respecte, l’index column store possède bien sûr quelques limites. En voici une liste non-exhaustive :

  1. Il ne peut supporter que jusqu’à 1024 colonnes.
  2. Certains types de colonnes ne sont pas supportés : decimal avec précision supérieure à 18 chiffres, numeric (idem que decimal), xml, (n)varchar(max), (var)binary… Plus de détails ici.
  3. Sous SQL Server 2012, l’index column store ne peut pas être clusterisé. Mais cette limite n’existe plus à partir de SQL Server 2014, avec l’incorporation du clustered index column store.
  4. Contrairement aux clustered index column store, la modification d’un index column store non-cluster n’est pas possible via ALTER INDEX. La solution serait plutôt de le recréer. En revanche, ALTER INDEX peut être utilisé pour sa reconstruction ou sa désactivation.
  5. Il ne peut être créé dans une vue (indexée ou non).
  6. Il ne peut supporter des colonnes éparses (c’est-à-dire, des sparse columns qui sont optimisées pour les valeurs NULL grâce à la réduction de l’espace nécessaire pour lesdites valeurs).
  7. Il ne peut être directement trié (via DESC ou ASC). En effet, toute opération de tri peut potentiellement impacter négativement l’efficacité de l’index. A noter que tout index column store est nativement ordonné selon les algorithmes de compression utilisé.
  8. Sous SQL Server 2012, l’index column store ne peut supporter les opérations DML de type INSERT, UPDATE et DELETE. La musique est tout autre à partir de SQL Server 2014, le clustered index column store permettant leur support. La mise-à-jour est rendue possible grâce à l’incorporation de deux nouveaux composants : les delete bitmaps et les delta stores. En guise d’explication rapide, voici comment fonctionnent les mises-à-jour au sein d’un index column store : 
  • Les insertions sont stockées dans le delta store qui est une structure de données en B-Tree (donc, avec les données stockées en ligne) où les insertions et les modifications sont stockées jusqu’à ce qu’une limite prédéfinie de lignes traitées soit atteinte (approximativement 1 million de lignes), ce qui forcera le moteur de stockage à « fermer » le delta store. Dès la fermeture, un processus appelé Tuple Mover va compresser le delta store dans un nouveau segment avant de supprimer (ou plutôt, désallouer) le delta store traité. Dans le cas des insertions en bloc (bulk-inserts), les données sont directement converties en format de colonne, au lieu d’être stockées dans le delta store et stockées dans le (ou les) (nouveau(x)) segment(s) idoine(s).
  • Les suppressions sont stockées dans le delete bitmap qui est une structure dédiée aux enregistrements supprimés. Si l’enregistrement existe dans un delta store, il sera simplement supprimé dudit delta.
  • Les opérations de modifications sont traitées comme une combinaison de suppressions et d’insertions : l’enregistrement édité est supprimé (i.e., son identifiant est de ligne est ajouté dans le delete bitmap) et sa nouvelle version ajoutée dans un delta store. Et lors de la consultation du delete bitmap, la présence de la ligne supprimée permettra à SQL Server de supprimer l’ancienne version de l’enregistrement du delta store pour ne conserver que la nouvelle.

Vous pouvez également jeter un coup d’oeil ici.

Pour aller plus loin…

Ce billet a permis de présenter l’intérêt de l’index column store.

Gardez œil ici pour d’autres billets sur le même sujet (performances, etc…).

Vous pouvez également faire un saut ici si vous souhaitez continuer à approfondir le sujet (notamment au niveau des limitations et bonnes pratiques).

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s