Partager via

Index columnstore - Performances des requêtes

  • Article

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

Cet article inclut des recommandations pour atteindre les performances de requête rapides avec les index columnstore.

Les index Columnstore peuvent atteindre jusqu’à 100 fois plus de performances sur les charges de travail d’analyse et d’entreposage de données, et jusqu’à 10 fois plus de compression des données que les index rowstore traditionnels. Ces recommandations aident vos requêtes à atteindre les performances rapides des requêtes que les index columnstore sont conçus pour fournir.

Recommandations pour améliorer les performances de requête

Voici quelques recommandations pour tirer pleinement parti de tous les avantages des index columnstore.

1. Organisez les données pour éliminer davantage de rowgroups dans une analyse de table complète.

  • Choisissez soigneusement l’ordre d’insertion. Dans un entrepôt de données standard, les données sont généralement insérées dans un ordre chronologique et analysées dans la dimension de temps. C’est le cas, par exemple, des analyses de ventes trimestrielles. Pour ce type de charge de travail, l’élimination des rowgroups est automatique. Dans SQL Server 2016 (13.x), les rowgroups de nombres peuvent être ignorés dans le processus de traitement des requêtes.

  • Utilisez un index cluster rowstore. Si le prédicat de requête commun se trouve sur une colonne (par exemple, C1) non lié à l’ordre d’insertion, créez un index cluster rowstore sur la colonne C1. Ensuite, supprimez l’index cluster rowstore et créez un index columnstore cluster. Si vous créez explicitement l’index MAXDOP = 1columnstore cluster, l’index columnstore cluster obtenu est parfaitement classé sur la colonne C1. Si vous spécifiez MAXDOP = 8, vous voyez le chevauchement des valeurs entre huit rowgroups. Pour un index columnstore non cluster (NCCI), si la table a un index cluster rowstore, les lignes sont déjà triées par la clé d’index cluster. Dans ce cas, l’index columnstore non cluster est également commandé automatiquement. Un index columnstore ne conserve pas intrinsèquement l’ordre des lignes. À mesure que de nouvelles lignes sont insérées ou que des lignes plus anciennes sont mises à jour, vous devrez peut-être répéter le processus, car les performances des requêtes d’analyse peuvent se dégrader.

  • Implémentez le partitionnement de tables. Vous pouvez partitionner l’index columnstore, puis utiliser l’élimination de partition pour réduire le nombre de rowgroups à analyser. Par exemple, une table de faits stocke les achats effectués par les clients. Un modèle de requête courant consiste à rechercher des achats trimestriels par customer. Dans ce cas, combinez la colonne d’ordre d’insertion avec le partitionnement sur customer la colonne. Chaque partition contient des lignes pour chaque customer, triée lors de l’insertion. Envisagez également d’utiliser le partitionnement de tables s’il est nécessaire de supprimer des données plus anciennes du columnstore. Le basculement et la troncation des partitions qui ne sont pas nécessaires est une stratégie efficace pour supprimer des données sans générer de fragmentation.

  • Évitez de supprimer de grandes quantités de données. La suppression de lignes compressées d’un rowgroup n’est pas une opération synchrone. Il serait coûteux de décompresser un rowgroup, de supprimer la ligne, puis de le recompresser. Par conséquent, lorsque vous supprimez des données des rowgroups compressés, ces rowgroups sont toujours analysés, même s’ils retournent moins de lignes. Si le nombre de lignes supprimées pour plusieurs rowgroups est suffisamment grand pour être fusionné en moins de rowgroups, la réorganisation du columnstore augmente la qualité de l’index et les performances des requêtes s’améliorent. Si votre processus de suppression de données vide généralement des rowgroups entiers, envisagez d’utiliser le partitionnement de table. Supprimez les partitions qui ne sont plus nécessaires et tronquez-les, au lieu de supprimer des lignes.

    Remarque

    À compter de SQL Server 2019 (15.x), le tuple-mover est aidé par une tâche de fusion en arrière-plan. Cette tâche compresse automatiquement des rowgroups OPEN delta plus petits qui ont existé depuis un certain temps, comme déterminé par un seuil interne, ou fusionne les rowgroups compressés à partir duquel un grand nombre de lignes a été supprimé. Cela améliore la qualité de l’index columnstore dans le temps. Si vous supprimez de grandes quantités de données de l’index columnstore, envisagez de fractionner cette opération en lots de suppression plus petits au fil du temps. Le traitement par lots permet à la tâche de fusion en arrière-plan de gérer la tâche de fusion de groupes de lignes plus petits et améliore la qualité de l’index. Ensuite, il n’est pas nécessaire de planifier des fenêtres de maintenance de réorganisation des index après la suppression des données. Pour plus d'informations sur les termes et les concepts columnstore, consultez Index Columnstore : vue d'ensemble.

2. Planifiez suffisamment de mémoire pour créer des index columnstore en parallèle

La création d'un index columnstore par défaut est une opération parallèle tant que la mémoire est contrainte. La création de l'index en parallèle requiert plus de mémoire que la création de l'index en série. Lorsqu'il y a suffisamment de mémoire, la création d'un index columnstore prend 1,5 fois plus de temps que créer un arbre B sur les mêmes colonnes.

La mémoire requise pour créer un index columnstore dépend du nombre de colonnes, du nombre de colonnes de chaîne, du degré de parallélisme (DOP), et des caractéristiques des données. Par exemple, si votre table comporte moins d’un million de lignes, SQL Server utilise un seul thread pour créer l’index columnstore.

Si votre table comporte plus d’un million de lignes, mais SQL Server ne peut pas obtenir une allocation de mémoire suffisante pour créer l’index à l’aide de MAXDOP, SQL Server diminue MAXDOP automatiquement si nécessaire. Dans certains cas, DOP doit être réduit à un pour générer l’index sous mémoire contrainte dans l’allocation de mémoire disponible.

Depuis SQL Server 2016 (13.x), la requête fonctionne toujours en mode batch. Dans les versions antérieures, l’exécution en mode batch est uniquement utilisée quand le degré de parallélisme défini est supérieur à un.

Explication des performances columnstore

Les index columnstore optimisent les performances de requête en combinant l’utilisation du mode batch qui accélère le traitement en mémoire avec plusieurs techniques qui réduisent considérablement les E/S nécessaires. Étant donné que les requêtes d’analyse analysent un grand nombre de lignes, elles sont généralement liées aux E/S, et par conséquent, la réduction des E/S pendant l’exécution des requêtes est essentielle à la conception des index columnstore. Une fois que les données sont lues en mémoire, il est essentiel de réduire le nombre d’opérations en mémoire.

Les index columnstore réduisent le nombre d’E/S et optimisent les opérations en mémoire grâce à la forte compression des données, à l’élimination de columnstore et de rowgroup, et au traitement en mode batch.

Compression des données

Les index Columnstore atteignent jusqu’à 10 fois plus de compression des données que les index rowstore. Cela réduit sensiblement le nombre d’E/S nécessaires pour l’exécution des requêtes d’analyse, améliorant ainsi les performances de requête.

  • Les index columnstore lisent les données compressées directement sur le disque, ce qui réduit le nombre d’octets de données à lire en mémoire.

  • Les index Columnstore stockent les données sous forme compressée en mémoire, ce qui réduit les E/S en évitant de lire les mêmes données en mémoire. Par exemple, avec une compression de 10 fois, les index columnstore peuvent conserver 10 fois plus de données en mémoire, par rapport au stockage des données sous forme non compressée. Avec plus de données en mémoire, il est plus probable que l’index columnstore trouve les données dont elle a besoin en mémoire sans entraîner de lectures inutiles à partir du disque.

  • Les index columnstore compressent les données par colonne plutôt que par ligne. C’est ce qui permet d’atteindre des taux de compression élevés et de diminuer le volume des données stockées sur le disque. Chaque colonne est compressée et stockée séparément. Les données d’une colonne ont toujours le même type de données et ont tendance à avoir des valeurs similaires. Les techniques de compression de données columnstore sont idéales pour atteindre des taux de compression plus élevés lorsque les valeurs sont similaires.

Par exemple, une table de faits stocke les adresses client et a une colonne pour country-region. Le nombre total de valeurs possibles est inférieur à 200. Certaines de ces valeurs sont répétées plusieurs fois. Si la table de faits comporte 100 millions de lignes, la country-region colonne se compresse facilement et nécessite peu de stockage. La compression de ligne par ligne n’est pas en mesure de tirer parti de la similarité des valeurs de colonne de cette façon et doit utiliser plus d’octets pour compresser les valeurs de la country-region colonne.

Élimination de colonne

Avec les index columnstore, les colonnes qui ne sont pas utiles pour le résultat d’une requête ne sont pas lues. L’élimination des colonnes réduit davantage les E/S pour l’exécution des requêtes et améliore donc les performances des requêtes.

  • L’élimination de colonne est possible, car les données sont organisées et compressées par colonne. En revanche, quand les données sont stockées par ligne, les valeurs de colonne dans chaque ligne sont stockées physiquement ensemble et ne peuvent pas être facilement séparées. Le processeur de requêtes doit lire dans une ligne entière pour récupérer des valeurs de colonne spécifiques, ce qui augmente les E/S, car les données supplémentaires sont inutilement lues en mémoire.

Par exemple, si une table contient 50 colonnes et que la requête porte seulement sur cinq de ces colonnes, l’index columnstore récupère uniquement les cinq colonnes en question à partir du disque. Il ignore la lecture dans les 45 autres colonnes, réduisant les E/S d’un autre 90 %, en supposant que toutes les colonnes sont de taille similaire. Si les mêmes données sont stockées dans un rowstore, le processeur de requêtes doit lire les 45 colonnes restantes.

Élimination de rowgroup

Dans une analyse de table complète, un grand pourcentage des données n’entre généralement pas dans les critères du prédicat de requête. L’index columnstore utilise des métadonnées pour ignorer les rowgroups qui contiennent des données non pertinentes pour le résultat de la requête, tout cela sans entraîner d’E/S supplémentaires. Cette fonction, appelée élimination de rowgroup, réduit les E/S nécessaires pour l’analyse de tables complètes et, par conséquent, améliore les performances de requête.

Quand un index columnstore doit-il effectuer une analyse de table complète ?

À compter de SQL Server 2016 (13.x), vous pouvez créer un ou plusieurs index rowstore non cluster standard, ou B-tree, sur un index columnstore cluster. Les index non-cluster B-tree peuvent accélérer une requête qui est définie avec un prédicat d’égalité ou avec un prédicat comportant une petite plage de valeurs. Pour les prédicats plus complexes, l’optimiseur de requête peut choisir d’effectuer une analyse de table complète. Sans possibilité d’ignorer les rowgroups, une analyse complète de table peut prendre du temps, en particulier pour les tables volumineuses.

Quand la fonction d’élimination de rowgroup est-elle intéressante pour une requête d’analyse de table complète ?

Par exemple, une entreprise de vente au détail modélise ses données de vente à l’aide d’une table de faits avec un index columnstore cluster. Chaque nouvelle vente stocke différents attributs de la transaction, y compris la date à laquelle un produit est vendu. Il est intéressant de noter que même si les index columnstore ne garantissent pas un ordre trié, les lignes de cette table sont chargées dans un ordre trié de date. Au fil du temps, cette table augmente. L’entreprise de vente au détail conserve peut-être les données de vente des dix dernières années, mais elle peut vouloir effectuer une requête analytique portant uniquement sur un agrégat du dernier trimestre. Les index columnstore peuvent ignorer les données des 39 trimestres précédents en examinant seulement les métadonnées de la colonne « date ». Il s’agit d’une réduction de 97 % de la quantité de données lues en mémoire et traitées.

Quels sont les rowgroups ignorés dans une analyse de table complète ?

Pour déterminer les rowgroups à éliminer, l’index columnstore se réfère aux métadonnées pour stocker les valeurs minimale et maximale de chaque segment de colonne pour chaque rowgroup. Quand aucune des plages de segments de colonne ne répond aux critères de prédicat de requête, l’ensemble du rowgroup est ignoré sans effectuer d’E/S réelles. Cela fonctionne, car les données sont généralement chargées dans un ordre trié. Bien que le tri des lignes ne soit pas garanti, les valeurs de données similaires se trouvent souvent dans le même rowgroup ou dans un rowgroup voisin.

Pour plus d’informations sur les rowgroups, consultez Indications pour la conception d’index columnstore.

Exécution en mode batch

L’exécution en mode batch désigne le fait de traiter simultanément un jeu de lignes, pouvant généralement contenir jusqu’à 900 lignes, pour gagner en efficacité. Par exemple, la requête SELECT SUM (Sales) FROM SalesData agrège les ventes totales de la table SalesData. En mode batch, le moteur d’exécution de la requête calcule l’agrégat dans le groupe de 900 valeurs. Cela permet de répartir les coûts d’accès aux métadonnées et d’autres types de traitement entre toutes les lignes du lot, plutôt que de payer les coûts par ligne, réduisant ainsi considérablement le chemin de code. Le traitement en mode batch fonctionne sur des données compressées lorsque cela est possible et élimine certains des opérateurs d’échange utilisés par le traitement du mode ligne, accélérant les requêtes d’analyse par ordre de grandeur.

Il n’est pas possible d’exécuter tous les opérateurs d’exécution de requête en mode batch. Par exemple, les opérations DML (Data Manipulation Language) telles que l’insertion, la suppression ou la mise à jour sont exécutées une ligne à la fois. L’opérateur en mode Batch, tel que Scan, Join, Aggregate, Sort, etc. peut améliorer les performances des requêtes. Depuis l’introduction de l’index columnstore dans SQL Server 2012 (11.x), nous nous efforçons d’étendre la prise en charge d’opérateurs exécutables en mode batch. Le tableau suivant montre les opérateurs qui s’exécutent en mode batch en fonction de la version du produit.

Opérateurs en mode batch Quand l’utiliser SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) et SQL Database1 Commentaires
Opérations DML (insert, delete, update, merge) non non non DML n’est pas une opération en mode batch, car elle n’est pas effectuée en parallèle. Même si nous rendons possible le traitement batch en mode série, l’ajout du traitement des opérations DML en mode batch n’offre pas d’avantages significatifs.
Columnstore Index Scan SCAN Non disponible Oui Oui Pour les index columnstore, nous pouvons transmettre le prédicat en mode Push vers le nœud SCAN.
Analyse d’index columnstore (non cluster) SCAN Oui oui oui Oui
Index Seek Non disponible Non disponible non Nous effectuons une opération de recherche via un index non-cluster en arbre B (B-tree) en rowmode.
Compute Scalar Expression ayant pour résultat une valeur scalaire. Oui oui Oui Comme tous les opérateurs en mode batch, il existe certaines restrictions sur le type de données.
Concatenation UNION et UNION ALL non oui Oui
filter Application de prédicats Oui oui Oui
Hash Match Fonctions d’agrégation basées sur le hachage, jointure de hachage externe, jointure de hachage droite, jointure de hachage gauche, jointure interne droite, jointure interne gauche Oui oui Oui Restrictions d’agrégation : pas de valeurs min/max pour les chaînes. Les fonctions d’agrégation disponibles sont sum/count/avg/min/max.
Restrictions de jointure : pas de jointures sans correspondance de type sur les types non entiers.
merge join non non non
requêtes multithread Oui oui Oui
boucles imbriquées non non non
requêtes à thread unique exécutées sous MAXDOP 1 non non Oui
requêtes à thread unique avec un plan de requête série non non Oui
sort Tri par clause sur SCAN avec l’index columnstore. non non Oui
Top Sort non non Oui
Window Aggregates Non disponible Non disponible Oui Nouvel opérateur dans SQL Server 2016 (13.x).

1 S’applique à SQL Server 2016 (13.x), aux niveaux SQL Database Premium, aux niveaux Standard - S3 et versions ultérieures, et à tous les niveaux vCore, et au système de plateforme d’analyse (PDW)

Pour plus d’informations, consultez le Guide d’architecture de traitement des requêtes.

Agrégation en mode Push

Chemin d’exécution standard utilisé pour le calcul d’agrégation qui récupère les lignes qualifiées du nœud SCAN et agrège les valeurs en mode batch. Bien que cela offre de bonnes performances, à partir de SQL Server 2016 (13.x), l’opération d’agrégation peut être envoyée (push) au nœud SCAN. Le pushdown d’agrégation améliore les performances des calculs agrégés par ordre de grandeur au-dessus de l’exécution en mode Batch, à condition que les conditions suivantes soient remplies :

  • Les agrégats sont MIN, MAX, SUM, COUNT et COUNT(*).
  • L’opérateur d’agrégation doit être au-dessus d’un nœud SCAN ou d’un nœud SCAN avec une clause GROUP BY.
  • Cet agrégat n’est pas un agrégat distinct.
  • La colonne d’agrégation n’est pas une colonne de chaîne.
  • La colonne d’agrégation n’est pas une colonne virtuelle.
  • Le type de données d’entrée et de sortie doit être l’un des éléments suivants et doit correspondre à 64 bits :
    • tinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal et numeric avec précision <= 18
    • smalldate, date, datetime, datetime2, heure

Par exemple, le pushdown d’agrégation est effectué dans les deux requêtes suivantes :

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Prédicats de chaîne en mode Push

Quand vous créez un schéma d’entrepôt de données, le modèle de schéma recommandé est un schéma en étoile ou en flocon contenant une ou plusieurs tables de faits et de nombreuses tables de dimension.

Conseil

La table de faits stocke les mesures ou transactions d’entreprise et la table de dimension stocke les dimensions sur lesquelles doit porter l’analyse des faits. Pour plus d’informations sur la modélisation dimensionnelle, consultez Modélisation dimensionnelle dans Microsoft Fabric.

Par exemple, un fait est un enregistrement représentant la vente d’un produit particulier dans une région spécifique, tandis que la dimension représente un ensemble de régions, produits, etc. Les tables de faits et de dimension sont associées par une relation de clé primaire/étrangère. Les requêtes analytiques les plus courantes associent une ou plusieurs tables de dimensions avec la table de faits.

Prenons l’exemple d’une table de dimension Products. Une clé primaire classique est ProductCode, généralement représentée sous forme de chaîne. Pour les performances des requêtes, il est recommandé de créer une clé de substitution, généralement une colonne entière , pour faire référence à la ligne de la table de dimension à partir de la table de faits.

L’index columnstore exécute des requêtes d’analyse avec des jointures et des prédicats impliquant des clés numériques ou entières efficacement. SQL Server 2016 (13.x) a amélioré les performances des requêtes d’analyse avec des colonnes basées sur des chaînes de manière significative, en faisant descendre les prédicats avec des colonnes de chaîne vers le nœud SCAN.

Le pushdown de prédicat de chaîne tire parti du dictionnaire principal/secondaire créé pour les colonnes afin d’améliorer les performances de la requête. Par exemple, considérez un segment de colonne de chaîne dans un rowgroup composé de 100 valeurs de chaîne distinctes. Chaque valeur de chaîne distincte est référencée 10 000 fois en moyenne, en supposant qu’un million de lignes. Avec le pushdown de prédicat de chaîne, l’exécution de la requête calcule le prédicat par rapport aux valeurs du dictionnaire. Si le prédicat est qualifié, toutes les lignes faisant référence à la valeur du dictionnaire sont automatiquement qualifiées. Cela améliore les performances de deux manières :

  • Seule la ligne qualifiée est retournée, ce qui réduit le nombre de lignes qui doivent sortir du nœud d’analyse.
  • Le nombre de comparaisons de chaînes est réduit. Dans cet exemple, seulement 100 chaînes doivent être comparées au lieu d’un million. Il existe certaines limitations :
    • Il n’y a pas de transmission Push des prédicats de type string pour les rowgroups delta. Il n’existe pas de dictionnaire pour les colonnes des rowgroups delta.
    • Il n’y a pas de transmission Push des prédicats de type string si le dictionnaire dépasse la taille de 64 Ko.
    • Les expressions évaluant les valeurs Null ne sont pas prises en charge.

Élimination des segments

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.

Dans les données columnstore, les groupes de lignes sont constitués de segments de colonne. Il existe des métadonnées associées à chaque segment pour permettre une élimination rapide des segments sans les lire. Cette élimination de segment s’applique aux types de données numériques, de date et d’heure, et au type de données datetimeoffset avec une échelle inférieure ou égale à deux. À compter de SQL Server 2022 (16.x), les fonctionnalités d’élimination des segments s’étendent aux types de données string, binary, guid et datetimeoffset pour une mise à l’échelle supérieure à deux.

Après la mise à niveau vers une version de SQL Server qui prend en charge l’élimination de segment min/max de chaîne (SQL Server 2022 (16.x) et versions ultérieures), l’index columnstore ne bénéficie pas de cette fonctionnalité tant qu’elle n’est pas reconstruite à l’aide d’un REBUILD ou DROP/CREATE.

L’élimination des segments ne s’applique pas aux types de données LOB, tels que les longueurs de type de données (max).

Actuellement, seul SQL Server 2022 (16.x) et les versions ultérieures prennent en charge l’élimination de rowgroup de columnstore cluster pour le préfixe de prédicats LIKE, par exemple column LIKE 'string%'. L’élimination des segments n’est pas prise en charge pour l’utilisation hors préfixe de LIKE, par exemple column LIKE '%string'.

Les index columnstore en cluster ordonnés bénéficient également de l’élimination des segments , en particulier pour les colonnes de chaîne. Dans les index cluster columnstore ordonnés, l’élimination de segments sur la première colonne de la clé d’index est la plus efficace, car elle est triée. Les gains de performances dus à l’élimination des segments sur d’autres colonnes de la table seront moins prévisibles. Pour plus d’informations sur les index cluster columnstore ordonnés, consultez Utiliser un index cluster columnstore ordonné pour les tables d’entrepôt de données de grande taille. Pour obtenir la disponibilité de l’index columnstore ordonné, consultez la disponibilité de l’index de colonne ordonné.

À l’aide de l’option de connexion de requête SET STATISTICS IO, vous pouvez afficher l’élimination des segments en action. Recherchez une sortie telle que la suivante pour indiquer que l’élimination de segment s’est produite. Les groupes de lignes sont constitués de segments de colonne, ce qui peut indiquer l’élimination des segments. L’exemple de sortie suivant SET STATISTICS IO d’une requête, environ 83 % de données ont été ignorées par la requête :

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Contenu connexe