Index columnstore - Guide de conception

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Recommandations générales pour la conception d’index columnstore. Quelques décisions conceptuelles judicieuses peuvent vous aider à obtenir les performances de requête élevées et l’excellente compression des données que les index columnstore sont censés fournir.

Prérequis

Cet article part du principe que vous connaissez la terminologie et l’architecture de columnstore. Pour plus d’informations, consultez les index Columnstore : Vue d’ensemble et Architecture d’index Columnstore.

Connaître vos exigences en matière de données

Avant de concevoir un index columnstore, essayez d’en savoir le plus possible sur vos exigences en matière de données. Par exemple, essayez de répondre à ces questions :

  • Quelle est la taille ma table ?
  • Mes requêtes effectuent-elles principalement une analytique sur de grandes plages de valeurs ? Les index columnstore sont conçus pour bien fonctionner avec les analyses sur de grandes plages, plutôt que pour rechercher des valeurs spécifiques.
  • Ma charge de travail effectue-t-elle un grand nombre de mises à jour et de suppressions ? Les index columnstore sont performants quand les données sont stables. Les requêtes doivent mettre à jour et supprimer moins de 10 % des lignes.
  • Ai-je des tables de faits et de dimension pour un entrepôt de données ?
  • Dois-je effectuer une analytique sur une charge de travail transactionnelle ? Si oui, consultez le Guide de conception de columnstore pour l’analytique opérationnelle en temps réel.

Vous n’avez peut-être pas besoin d’un index columnstore. Les index rowstore (ou B-tree) avec des segments de mémoire ou des index cluster fonctionnent de manière optimale sur les requêtes qui recherchent une valeur spécifique au sein des données ou sur les requêtes qui interrogent une petite plage de valeurs. Utilisez les index rowstore avec des charges de travail transactionnelles, car ils ont tendance à nécessiter principalement des recherches de table plutôt que des analyses de table sur des plages étendues.

Choisir l’index columnstore le plus adapté à vos besoins

Un index columnstore est un index cluster ou non cluster. Un index columnstore cluster peut avoir un ou plusieurs index d’arbre B (B-tree) non-cluster. Les index columnstore sont faciles à essayer. Si vous créez une table en tant qu’index columnstore, vous pouvez facilement la reconvertir en table rowstore en supprimant l’index columnstore.

Voici un récapitulatif des options et des recommandations.

Option de columnstore Quand l’utiliser Compression
Index columnstore cluster À utiliser pour :

1) Une charge de travail d’entrepôt des données traditionnelle avec un schéma en étoile ou en flocon

2) Des charges de travail Internet des objets (IOT) qui insèrent de grands volumes de données avec des mises à jour et des suppressions minimales.		 10x en moyenne
Index cluster columnstore ordonné À utiliser lorsqu’un index cluster columnstore est interrogé via une colonne ou un jeu de colonnes de prédicat ordonné unique. Cette aide est similaire au choix des colonnes clés d’un index rowstore en cluster, bien que les groupes de lignes sous-jacents compressés se comportent différemment. Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes. 10x en moyenne
Index d’arbre B (B-tree) non cluster sur un index cluster columnstore Utilisez pour effectuer les tâches suivantes :

1. Appliquer des contraintes de clé primaire et de clé étrangère sur un index cluster columnstore.

2. Accélérer les requêtes qui recherchent des valeurs spécifiques ou de petites plages de valeurs.

3. Accélérer les mises à jour et les suppressions de lignes spécifiques.		 10x en moyenne, plus du stockage supplémentaire pour les index non cluster.
Index columnstore non cluster sur un segment de mémoire sur disque ou un index d’arbre B (B-tree) À utiliser pour :

1) Une charge de travail OLTP ayant certaines requêtes analytiques. Vous pouvez supprimer les index d’arbre B (B-tree) créés pour l’analytique, et les remplacer par un index columnstore non-cluster.

2) De nombreuses charges de travail OLTP traditionnelles qui effectuent des opérations Extract, Transform et Load (ETL) pour déplacer des données vers un entrepôt de données distinct. Vous pouvez éliminer les opérations ETL et l’entrepôt de données distinct en créant un index non cluster columnstore sur certaines des tables OLTP.		 NCCI est un index supplémentaire qui nécessite en moyenne 10 % de stockage en plus.
Index columnstore sur une table en mémoire Mêmes recommandations que pour les index non cluster columnstore sur une table sur disque, mais la table de base est une table en mémoire. L’index columnstore est un index supplémentaire.

Utiliser un index cluster columnstore pour les tables d’entrepôt de données de grande taille

L’index cluster columnstore est plus qu’un index, il s’agit du principal stockage de table. Il offre une compression élevée des données et améliore sensiblement les performances de requête sur les tables de faits et de dimension d’entreposage des données de grande taille. Les index cluster columnstore conviennent mieux aux requêtes analytiques qu’aux requêtes transactionnelles, car les requêtes analytiques ont tendance à effectuer des opérations sur de grandes plages de valeurs, plutôt que de rechercher des valeurs spécifiques.

Utilisez un index cluster columnstore quand :

  • Chaque partition a au moins un million de lignes. Les index columnstore ont des rowgroups dans chaque partition. Si la table est trop petite pour remplir un rowgroup dans chaque partition, vous ne tirerez pas parti des avantages de columnstore en matière de performances de requête et de compression.
  • Les requêtes effectuent principalement une analytique sur des plages de valeurs. Par exemple, pour rechercher la valeur moyenne d’une colonne, la requête doit analyser toutes les valeurs de la colonne. Elle effectue ensuite une agrégation des valeurs en les additionnant, afin de déterminer la moyenne.
  • La plupart des insertions concernent des volumes importants de données, avec des mises à jour et des suppressions minimales. De nombreuses charges de travail telles qu’Internet des objets (IOT) insèrent de grands volumes de données avec des mises à jour et des suppressions minimales. Ces charges de travail peuvent bénéficier des gains en matière de compression et de performances de requête liés à l’utilisation d’un index cluster columnstore.

N’utilisez pas d’index cluster columnstore quand :

  • La table nécessite des types de données varchar(max), nvarchar(max) ou varbinary(max). Vous pouvez également concevoir l’index columnstore afin qu’il n’inclue pas ces colonnes (s’applique à : SQL Server 2016 (13.x) et versions antérieures).
  • Les données de table ne sont pas permanentes. Utilisez plutôt un segment de mémoire ou une table temporaire quand vous avez besoin de stocker et de supprimer les données rapidement.
  • La table a moins d’un million de lignes par partition.
  • Plus de 10 % des opérations sur la table sont des mises à jour et des suppressions. Un nombre élevé de mises à jour et de suppressions provoquent une fragmentation. La fragmentation affecte le taux de compression et les performances de requête jusqu’à ce que vous exécutiez une opération appelée « Réorganisation », qui force toutes les données dans le columnstore et supprime la fragmentation. Pour plus d’informations, consultez Réduction de la fragmentation d’index dans les index columnstore.

Pour plus d’informations, consultez les index Columnstore dans l’entreposage de données.

Utiliser un index cluster columnstore ordonné pour les tables d’entrepôt de données de grande taille

Pour obtenir la disponibilité d’index columnstore ordonnée, consultez les index Columnstore : Vue d’ensemble#ordered-columnstore-index-availability).

Envisagez d’utiliser un index cluster columnstore ordonné dans les scénarios suivants :

  • Lorsque les données sont relativement statiques (sans écritures et suppressions fréquentes) et que la clé de l’index cluster columnstore ordonné est statique, les index cluster columnstore ordonnés peuvent offrir de gros avantages du point de vue des performances par rapport aux index cluster columnstore non ordonnés ou aux index cluster rowstore pour les charges de travail analytiques.
  • Les valeurs les plus distinctes dans la première colonne de la clé d’index columnstore cluster triée, plus les gains de performances peuvent être élevés pour les index columnstore cluster ordonnés. Cela est dû à une amélioration de l’élimination des segments pour les données de chaîne. Pour en savoir plus, consultez l'élimination des segments.
  • Choisissez une clé de l’index cluster columnstore ordonné qui sera fréquemment interrogée et peut tirer parti de l’élimination des segments, en particulier la première colonne de la clé. Les gains de performances dus à l’élimination des segments sur d’autres colonnes de la table seront moins prévisibles.
  • Pour les cas d’utilisation où seules les données analytiques les plus récentes doivent être interrogées, par exemple, les 15 dernières secondes, les index cluster columnstore ordonnés peuvent fournir une élimination des segments pour les données plus anciennes. La première colonne de la clé des données du cluster columstore ordonné doit contenir les données de date et d’heure, telles qu’une date ou une heure insérée ou créée. L’élimination des segments serait plus efficace dans un index cluster columnstore ordonné que dans un index cluster columnstore non ordonné.
  • Envisagez d’utiliser des index cluster columnstore ordonnés sur des tables contenant des clés avec des données GUID, où le type de données uniqueidentifier peut désormais être utilisé pour l’élimination des segments.

Un index columnstore cluster ordonné peut ne pas être aussi efficace dans ces scénarios :

  • Comme pour d’autres index columnstore, un taux élevé d’activité d’insertion peut créer des E/S de stockage excessives.
  • Pour les charges de travail où il existe un grand nombre d’opérations d’écriture, la qualité de l’élimination des segments sera réduite au fil du temps en raison de la maintenance du rowgroup par le moteur de tuple. Cela peut être atténué par une maintenance régulière de l’index columnstore avec ALTER INDEX REORGANIZE.

Ajouter des index d’arbre B (B-tree) non-cluster pour améliorer l’efficacité des recherches dans les tables

À partir de SQL Server 2016 (13.x), vous pouvez créer des index d’arbre B (B-tree) non cluster en tant qu’index secondaires sur un index cluster columnstore. L’index d’arbre B (B-tree) non-cluster est mis à jour à mesure que l’index columnstore est modifié. Il s’agit d’une fonctionnalité puissante que vous pouvez utiliser à votre avantage.

L’index d’arbre B (B-tree) secondaire vous permet de rechercher efficacement des lignes spécifiques sans avoir à analyser toutes les lignes. D’autres options sont également disponibles. Par exemple, vous pouvez appliquer une contrainte de clé primaire ou étrangère à l’aide d’une contrainte UNIQUE sur l’index d’arbre B (B-tree). Étant donné qu’une valeur non unique ne peut pas être insérée dans l’index d’arbre B (B-tree), SQL Server ne peut pas insérer la valeur dans le columnstore.

Utilisez un index d’arbre B (B-tree) sur un index columnstore pour :

  • Exécuter des requêtes qui recherchent des valeurs particulières ou de petites plages de valeurs.
  • Appliquer une contrainte, telle qu’une contrainte de clé primaire ou de clé étrangère.
  • Effectuer des opérations de mise à jour et de suppression de manière efficace. L’index d’arbre B (B-tree) peut localiser rapidement les lignes spécifiques pour les mises à jour et les suppressions sans avoir à analyser toute la table ou la partition d’une table.
  • Vous disposez de stockage supplémentaire pour stocker l’index d’arbre B (B-tree).

Utiliser un index non cluster columnstore pour l’analytique en temps réel

À partir de SQL Server 2016 (13.x), vous pouvez avoir un index non cluster columnstore sur une table sur disque rowstore ou dans une table OLTP en mémoire. Vous pouvez ainsi exécuter une analytique en temps réel sur une table transactionnelle. Pendant que les transactions ont lieu sur la table sous-jacente, vous pouvez exécuter l’analytique sur l’index columnstore. Étant donné qu’une même table gère les deux index, les modifications sont accessibles en temps réel aux index rowstore et columnstore.

Un index columnstore offrant une compression des données dix fois supérieure à celle d’un index rowstore, il n’a besoin que d’une petite quantité de stockage supplémentaire. Par exemple, si la table rowstore compressée prend 20 Go, l’index columnstore peut nécessiter 2 Go supplémentaires. L’espace supplémentaire requis dépend également du nombre de colonnes dans l’index non cluster columnstore.

Utilisez un index non cluster columnstore pour :

  • Exécuter une analytique en temps réel sur une table rowstore transactionnelle. Vous pouvez remplacer les index d’arbre B (B-tree) existants qui sont conçus pour l’analytique par un index columnstore non-cluster.

  • Éliminer la nécessité d’un entrepôt de données distinct. En règle générale, les entreprises exécutent des transactions sur une table rowstore, puis chargent les données dans un entrepôt de données distinct pour exécuter l’analytique. Pour de nombreuses charges de travail, vous pouvez éliminer le processus de chargement et l’entrepôt de données distinct en créant un index non cluster columnstore sur des tables transactionnelles.

SQL Server 2016 (13.x) propose plusieurs stratégies pour rendre ce scénario performant. Il est très facile de l’essayer, car vous pouvez activer un index non cluster columnstore sans modifier votre application OLTP.

Pour ajouter des ressources de traitement supplémentaires, vous pouvez exécuter l’analytique sur un secondaire lisible. Le recours à un secondaire lisible sépare le traitement de la charge de travail transactionnelle de celui de la charge de travail analytique.

Pour plus d’informations, consultez Prise en main de Columnstore pour l’analytique opérationnelle en temps réel

Pour plus d’informations sur le choix du meilleur index columnstore, consultez le blog de Sunil Agarwal intitulé Quel est l’index columnstore le plus adapté à ma charge de travail ?.

Utiliser des partitions de table pour les performances de requête et la gestion des données

Les index columnstore prennent en charge le partitionnement, ce qui est un bon moyen de gérer et d’archiver les données. Le partitionnement améliore également les performances de requête en limitant les opérations à une ou plusieurs partitions.

Utiliser des partitions pour simplifier la gestion des données

Pour les tables volumineuses, le seul moyen pratique de gérer les plages de données consiste à utiliser des partitions. Les avantages offerts par les partitions pour les tables rowstore s’appliquent également aux index columnstore.

Par exemple, les tables rowstore et columnstore utilisent des partitions pour :

  • Contrôler la taille des sauvegardes incrémentielles. Vous pouvez sauvegarder des partitions dans des groupes de fichiers distincts, puis les marquer comme étant en lecture seule. Ainsi, les sauvegardes ultérieures ignoreront les groupes de fichiers en lecture seule.
  • Réduisez les coûts de stockage en déplaçant une partition plus ancienne vers un stockage moins coûteux. Par exemple, vous pouvez utiliser le basculement de partition pour déplacer une partition vers un emplacement de stockage moins coûteux.
  • Optimisez l’efficacité des opérations en les limitant à une partition. Par exemple, vous pouvez cibler uniquement les partitions fragmentées pour la maintenance d’index.

En outre, avec un index columnstore, vous utilisez le partitionnement pour :

  • Économiser 30 % de plus sur les coûts de stockage. Vous pouvez compresser des partitions plus anciennes avec les options de COLUMNSTORE_ARCHIVE compression. Les données seront plus lentes pour les performances de requête, ce qui est acceptable si la partition est rarement interrogée.

Utiliser des partitions pour améliorer les performances de requête

Grâce aux partitions, vous pouvez limiter vos requêtes pour analyser uniquement des partitions spécifiques, ce qui limite le nombre de lignes à analyser. Par exemple, si l’index est partitionné par année et que la requête analyse des données de l’année précédente, elle n’a besoin d’analyser que les données d’une seule partition.

Utiliser moins de partitions pour un index columnstore

À moins d’avoir une taille de données suffisamment importante, un index columnstore offre de meilleures performances avec moins de partitions que pour un index rowstore. Si vous n’avez pas au moins un million de lignes par partition, la plupart de vos lignes risquent d’aller dans le deltastore où elles ne bénéficient pas de l’amélioration des performances de compression de columnstore. Par exemple, si vous chargez un million de lignes dans une table avec 10 partitions et que chaque partition reçoit 100 000 lignes, toutes les lignes iront dans des rowgroups delta.

Exemple :

  • Chargez 1 000 000 lignes dans une partition ou une table non partitionnée. Vous obtenez un rowgroup compressé avec 1 000 000 lignes. C’est parfait pour bénéficier d’une compression des données et de performances de requête élevées.
  • Chargez 1 000 000 lignes uniformément dans 10 partitions. Chaque partition reçoit 100 000 lignes, ce qui est inférieur au seuil minimal pour la compression columnstore. Ainsi, l’index columnstore peut avoir 10 rowgroups delta avec 100 000 lignes dans chaque. Il existe des moyens de forcer les rowgroups delta dans le columnstore. Toutefois, s’il s’agit des seules lignes de l’index columnstore, les rowgroups compressés seront trop petits pour bénéficier d’un meilleur niveau de performance de compression et de requête.

Pour plus d’informations sur le partitionnement, voir le blog de Sunil Agarwal intitulé Dois-je partitionner mon index columnstore ?.

Choisir la méthode de compression des données appropriée

L’index columnstore propose deux options pour la compression des données : la compression columnstore et la compression d’archive. Vous pouvez choisir l’option de compression quand vous créez l’index, ou la changer ultérieurement à l’aide de ALTER INDEX ... REBUILD.

Utiliser la compression columnstore pour de meilleures performances de requête

La compression columnstore offre en général des taux de compression 10 fois supérieurs aux index rowstore. Il s’agit de la méthode de compression standard pour les index columnstore. Elle offre des performances de requête élevées.

Utiliser la compression d’archive pour une meilleure compression des données

La compression d’archive est conçue pour offrir une compression maximale quand les performances de requête ne sont pas aussi importantes. Elle permet d’obtenir des taux de compression des données supérieures à la compression columnstore, mais elle a un prix. La compression et la décompression des données étant plus longues, cette approche ne convient pas si la rapidité des requêtes est cruciale.

Utiliser des optimisations quand vous convertissez une table rowstore en index columnstore

Si vos données sont déjà dans une table rowstore, vous pouvez utiliser CREATE COLUMNSTORE INDEX pour convertir la table en index cluster columnstore. Les optimisations décrites ci-dessous permettent d’améliorer les performances de requête après la conversion de la table.

Utiliser MAXDOP pour améliorer la qualité de rowgroup

Vous pouvez configurer le nombre maximal de processeurs pour la conversion d’un segment de mémoire ou d’un index d’arbre B (B-tree) en cluster en un index columnstore. Pour configurer les processeurs, utilisez l’option de degré maximal de parallélisme (MAXDOP).

Si vous avez de grandes quantités de données, MAXDOP 1 sera probablement trop lent. Augmenter MAXDOP à 4 donne de bons résultats. Si l’une des conséquences est que certains rowgroups n’ont pas le nombre de lignes optimal, vous pouvez exécuter ALTER INDEX REORGANIZE pour les fusionner en arrière-plan.

Conserver l’ordre de tri d’un index d’arbre B (B-tree)

Étant donné que l’index d’arbre B (B-tree) stocke déjà les lignes dans un ordre trié, le fait de conserver cet ordre quand les lignes sont compressées dans l’index columnstore peut améliorer les performances.

L’index columnstore ne trie pas les données, mais il utilise des métadonnées pour effectuer le suivi des valeurs minimales et maximales de chaque segment de colonne dans chaque rowgroup. Lors de l’analyse d’une plage de valeurs, il peut calculer rapidement quand il faut ignorer le rowgroup. Quand les données sont triées, davantage de rowgroups peuvent être ignorés.

Pour conserver l’ordre de tri pendant la conversion :

  • Utilisez CREATE COLUMNSTORE INDEX avec la clause DROP_EXISTING. Cela conserve également le nom de l’index. Si vous avez des scripts qui utilisent déjà le nom de l’index rowstore, vous n’aurez pas besoin de les mettre à jour.

    Cet exemple convertit un index rowstore cluster sur une table nommée MyFactTable en un index cluster columnstore. Le nom d’index, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, reste identique.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
ON MyFactTable
WITH (DROP_EXISTING = ON);

Comprendre l’élimination des segments

Chaque rowgroup contient un segment de colonne pour chaque colonne dans la table. Chaque segment de colonne est compressé et stocké sur un support physique.

Il existe des métadonnées associées à chaque segment pour permettre une élimination rapide des segments sans les lire. Le choix du type de données peut avoir un impact significatif sur le niveau de performance des requêtes en fonction des prédicats de filtre courants pour les requêtes sur l’index columnstore. Pour en savoir plus, consultez l'élimination des segments.

Il s’agit de tâches pour créer et tenir à jour des index columnstore.

Tâche Articles de référence Notes
Créer une table sous forme de columnstore CREATE TABLE (Transact-SQL) À partir de SQL Server 2016 (13.x), vous pouvez créer la table en tant qu’index cluster columnstore. Il est inutile de créer au préalable une table rowstore, puis de la convertir en columnstore.
Créer une table mémoire avec un index columnstore. CREATE TABLE (Transact-SQL) À partir de SQL Server 2016 (13.x), vous pouvez créer une table optimisée en mémoire avec un index columnstore. L’index columnstore peut également être ajouté après la création de la table, à l’aide de la syntaxe ALTER TABLE ADD INDEX.
Convertir une table rowstore en table columnstore CREATE COLUMNSTORE INDEX (Transact-SQL) Convertissez un segment de mémoire ou un arbre B existant en columnstore. Les exemples montrent comment gérer les index existants, ainsi que le nom de l’index lors de cette conversion.
Convertir une table columnstore en rowstore CREATE CLUSTERED INDEX (Transact-SQL) ou Reconvertir une table columnstore en segment rowstore Cette conversion n’est généralement pas nécessaire, mais le cas peut se présenter. Les exemples montrent comment convertir un columnstore en segment de mémoire ou index cluster.
Créer un index columnstore sur une table rowstore CREATE COLUMNSTORE INDEX (Transact-SQL) Une table rowstore ne peut avoir qu’un seul index columnstore. À partir de SQL Server 2016 (13.x) , l’index columnstore peut avoir une condition de filtrage. Les exemples affichent la syntaxe de base.
Créer des index performants pour l’analytique opérationnelle Bien démarrer avec columnstore pour l’analytique opérationnelle en temps réel Décrit comment créer des index columnstore et B-tree complémentaires pour que les requêtes OLTP utilisent des index B-tree et que les requêtes analytiques utilisent des index columnstore.
Créer des index columnstore performants pour l’entreposage des données Index Columnstore dans l’entreposage de données Décrit comment utiliser des index B-tree sur les tables columnstore pour créer des requêtes performantes en matière d’entreposage des données.
Utiliser un index B-tree pour appliquer une contrainte de clé primaire sur un index columnstore. Index Columnstore dans l’entreposage de données Montre comment combiner des index B-tree et columnstore pour appliquer des contraintes de clé primaire sur l’index columnstore.
Abandonner un index columnstore DROP INDEX (Transact-SQL) L’abandon d’un index columnstore utilise la syntaxe DROP INDEX standard utilisée par les index B-tree. L’abandon d’un index cluster columnstore convertit la table columnstore en segment de mémoire.
Supprimer une ligne d’un index columnstore DELETE (Transact-SQL) Utilisez DELETE (Transact-SQL) pour supprimer une ligne.

Ligne columnstore : SQL Server marque la ligne comme étant supprimée logiquement, mais ne récupère pas le stockage physique pour la ligne tant que l’index n’est pas reconstruit.

Ligne deltastore : SQL Server supprime la ligne logiquement et physiquement.
Mettre à jour une ligne dans l’index columnstore UPDATE (Transact-SQL) Utilisez UPDATE (Transact-SQL) pour mettre à jour une ligne.

Ligne columnstore : SQL Server marque la ligne comme étant supprimée logiquement, puis insère la ligne mise à jour dans le delta store.

Ligne deltastore : SQL Server met à jour la ligne dans le delta store.
Obliger toutes les lignes du deltastore à aller dans le columnstore. ALTER INDEX (Transact-SQL) ... REBUILD

Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources		 ALTER INDEX avec l’option REBUILD oblige toutes les lignes à aller dans le columnstore.
Défragmenter un index columnstore ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE défragmente les index columnstore en ligne.
Fusionner des tables avec les index columnstore MERGE (Transact-SQL)

Contenu connexe

Pour créer un index columnstore vide pour :

Pour plus d’informations sur la façon de convertir un segment de mémoire rowstore ou un index B-tree existant dans un index cluster columnstore, ou pour créer un index non cluster columnstore, reportez-vous à CREATE COLUMNSTORE INDEX (Transact-SQL).