Lire en anglais

Partager via

Index columnstore – Aide à la conception

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

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 suppose que vous êtes familier avec l’architecture et la terminologie des columnstores. 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 larges plages de données, 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 c’est le cas, consultez l’aide à la conception d’un index 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 arbre B (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 indice columnstore clusterisé peut avoir un ou plusieurs indices B-tree non-clusterisés. Vous pouvez facilement essayer les index columnstore. 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 columnstore Recommandations sur 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 columnstore ordonné À utiliser lorsqu’un index columnstore cluster est interrogé via une seule colonne ou un jeu de colonnes de prédicat ordonné. Ce guide est similaire au choix des colonnes clés d’un index clusterisé rowstore, bien que les rowgroups sous-jacents compressés se comportent différemment. Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX et Optimisation des performances avec des index columnstore ordonnés. 10x en moyenne
Index d’arbre B (B-tree) non cluster sur un index columnstore cluster À utiliser pour :

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

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 un stockage supplémentaire pour les index non clusterisés.
Index columnstore non cluster sur un index de segment de mémoire ou arbre B (B-tree) basé sur un disque À utiliser pour :

1) Une charge de travail OLTP ayant certaines requêtes analytiques. Vous pouvez supprimer les index B-tree créés pour les analyses et les remplacer par un index columnaire non-clusterisé.

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 un entrepôt de données distinct en créant un index columnstore non cluster 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 columnstore non clusterisés sur un disque, sauf que la table de base est une table en mémoire. L’index columnstore est un index supplémentaire.

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

L’index columnstore cluster 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 le niveau de performance des requêtes sur les tables de faits et de dimensions d’entrepôt de données de grande taille. Les index de store de colonnes en cluster sont mieux adaptés 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.

Envisagez d'utiliser un index columnstore en grappes lorsque :

  • 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 risquez de ne pas bénéficier des avantages liés au niveau de performance des requêtes et à la compression de columnstore.
  • 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 de compression et de performances de requête liés à l’utilisation d’un index de colonne en cluster.

N’utilisez pas d’index columnstore cluster 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 les taux de compression et les performances des requêtes jusqu’à ce que vous exécutiez une opération appelée réorganisation, ce 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 Index columnstore dans un entrepôt de données.

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

Pour connaître la disponibilité des index columnstore ordonnés, consultez index Columnstore : Vue d’ensemble.

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

  • Lorsque les données sont relativement statiques (sans écritures et suppressions fréquentes) et que la clé d’index columnstore ordonnée est statique, les index columnstore ordonnés peuvent offrir des avantages significatifs en matière de performances par rapport aux index columnstore non ordonnés ou aux index rowstore pour les charges de travail analytiques.
  • Plus il y a de valeurs distinctes dans la première colonne de la clé de l'index columnstore ordonné, plus les gains de performance peuvent être importants. 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é d’index columnstore ordonné qui est fréquemment interrogée et qui peut bénéficier de l’élimination de segments, en particulier la première colonne de la clé. Les gains de performances en raison de l’élimination des segments sur d’autres colonnes de la table sont moins prévisibles.
  • Cas d’usage où seules les données analytiques les plus récentes doivent être interrogées, par exemple, les 15 dernières secondes, les index columnstore ordonnés peuvent fournir une élimination de segment pour les données plus anciennes. La première colonne de la clé des données de columnstore ordonnées doit être les données de date/heure, telles qu’une date/heure insérée ou créée. L’élimination des segments serait plus efficace dans un index columnstore ordonné que dans un index columnstore non ordonné.
  • Envisagez d’utiliser des index 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 de segments.

Un index columnstore 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 comportant un grand nombre d’opérations d’écriture, la qualité de l’élimination de segments diminue 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 B-tree non clusterés ou rowstore en tant qu'index secondaires sur un index de magasin de colonnes clusteré. L'index B-tree non-clusterisé 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 B-tree. Étant donné qu’une valeur non unique ne parvient pas à être insérée dans l’index B-tree, SQL Server ne peut pas insérer la valeur dans le columnstore.

Envisagez d’utiliser un 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 columnstore non cluster pour l’analytique en temps réel

À partir de SQL Server 2016 (13.x), vous pouvez avoir un index columnstore non clusterisé sur une table en rowstore basée sur disque 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 columnstore non clusterisé.

Envisagez d'utiliser un index columnstore non-clusterisé pour :

  • En temps réel, exécutez une analyse sur une table rowstore transactionnelle. Vous pouvez remplacer les index d’arbre B (B-tree) existants qui sont conçus pour l’analyse par un index columnstore non-clusterisé.

  • É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 columnstore non-clusterisé sur les tables de transactions.

SQL Server 2016 (13.x) propose plusieurs stratégies pour rendre ce scénario performant. Il est facile de l’essayer, car vous pouvez activer un index columnstore non cluster sans modification de 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 de stockage par lignes s’appliquent également aux index de stockage par colonnes.

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. En procédant ainsi, les sauvegardes futures ignorent 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 performances des requêtes peuvent être plus lentes, ce qui peut être 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 du niveau de performance associée à la compression de columnstore. Par exemple, si vous chargez un million de lignes dans une table avec 10 partitions et chaque partition reçoit 100 000 lignes, toutes les lignes vont aux rowgroups delta.

Exemple :

  • Chargez 1 000 000 lignes dans une partition ou une table non partitionnée. Vous obtenez un groupe de lignes compressé avec 1 000 000 de 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 sont trop petits pour des performances de compression et de requête optimales.

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 prenant plus de temps, il n'est pas bien adapté à des performances de requêtes rapides.

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 convertir un heap ou un index 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 peut être trop lente. 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’avez pas besoin de les mettre à jour.

    Cet exemple convertit un index rowstore groupé dans une table appelée MyFactTable en un index columnstore groupé. Le nom d’index, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, reste identique.

    SQL 
    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 Remarques
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 columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL) Convertissez un segment de mémoire ou arbre B (B-tree) 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 de type columnstore en table de type 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 il peut y avoir des moments où vous devez convertir. 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 un entrepôt 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 un entrepôt de données Montre comment combiner des index B-tree et columnstore pour appliquer des contraintes de clé primaire à l’index columnstore.
Annuler un index columnstore DROP INDEX (Transact-SQL) L’annulation d’un index columnstore utilise la syntaxe DROP INDEX standard utilisée par les index d’arbre B (B-tree). La suppression d’un index columnstore cluster 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 de type columnstore ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE défragmente les index columnstore en ligne.
Fusionner des tables avec des 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 amas rowstore ou un index B-tree existant en un index columnstore cluster, ou pour créer un index columnstore non-cluster, référez-vous à CREATE COLUMNSTORE INDEX (Transact-SQL).