Index columnstore - Performances des requêtes

Article
08/23/2023

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

Les index columnstore sont conçus pour rendre le traitement des requêtes beaucoup plus rapide. Les recommandations suivantes vous permettront d’atteindre les performances attendues.

Par rapport aux index rowstore traditionnels, les index columnstore permettent d’améliorer jusqu’à 100 fois les performances des charges de travail liées à l’analyse et à l’entreposage des données, et d’obtenir un taux de compression des données jusqu’à 10 fois supérieur. Les index columnstore sont conçus pour rendre le traitement des requêtes beaucoup plus rapide. Ces recommandations vous aident à atteindre les performances attendues. Vous trouverez des explications complémentaires sur les performances des index columnstore à la fin de cet article.

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.

Optimisez 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), vous trouverez les groupes de lignes numériques ignorés dans le cadre du traitement des requêtes.
Optimisez l’index cluster rowstore. Si le prédicat de requête commun se trouve dans une colonne (par exemple, C1) qui n’est pas liée à l’ordre d’insertion de la ligne, vous pouvez créer un index cluster rowstore dans les colonnes C1, puis créer des index cluster columnstore en supprimant l’index cluster rowstore. Si vous créez l’index cluster columnstore explicitement avec MAXDOP = 1, l’index cluster columnstore obtenu est parfaitement ordonné dans la colonne C1. Si vous spécifiez MAXDOP = 8, vous observez un chevauchement des valeurs entre huit rowgroups. Ce cas se produit souvent quand vous créez l’index columnstore initial pour un jeu de données volumineux. Notez que, dans les index non-cluster columnstore (NCCI), les lignes sont déjà ordonnées si la table de base rowstore a un index cluster. Dans ce cas, l’index non cluster columnstore résultant est automatiquement ordonné. Un point important à retenir est que l’index columnstore ne conserve pas l’ordre des lignes par héritage. Au fur et à mesure que vous ajoutez de nouvelles lignes ou que vous mettez à jour des lignes existantes, vous devrez répéter ce processus si vous constatez une baisse des performances des requêtes analytiques.
Optimisez le partitionnement de table. 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 effectués par un client spécifique, vous pouvez combiner la commande d’insertion avec le partitionnement sur la colonne client. Dans chaque partition, les lignes sont classées par ordre chronologique pour un client spécifique. En outre, envisagez d’utiliser le partitionnement de table si vous devez supprimer des données du columnstore. Le fait d’extraire et de tronquer les partitions qui ne sont plus nécessaires est une stratégie efficace pour supprimer les données sans générer la fragmentation introduite par des rowgroups plus petits.
É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 recomprimer. Par conséquent, si vous supprimez des données de rowgroups compressés, ceux-ci seront analysés même s’ils retournent moins de lignes. Si le nombre de lignes supprimées pour plusieurs rowgroups est suffisamment important pour que ceux-ci soient fusionnés dans moins de rowgroups, la réorganisation du columnstore augmente la qualité de l’index et les performances des requêtes sont améliorées. Si votre processus de suppression des données vide généralement des rowgroups entiers, envisagez d’utiliser le partitionnement de table, d’extraire les partitions qui ne sont plus nécessaires et de les tronquer au lieu de supprimer des lignes.

Note

À compter de SQL Server 2019 (15.x), le tuple-mover est aidé par une tâche de fusion en arrière-plan qui compresse automatiquement des rowgroups OPEN delta plus petits qui existent 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 devez supprimer de grandes quantités de données de l’index columnstore, envisagez de fractionner cette opération en lots plus petits de suppression dans le temps, ce qui permet à la tâche de fusion en arrière-plan de gérer la tâche de fusion de rowgroups plus petits et d’améliorer la qualité de l’index, ce qui évite d’avoir à planifier des fenêtres de maintenance de réorganisation de l’index après la suppression de données.
Pour plus d’informations sur les termes et concepts columnstore, consultez les 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 de 1 million de lignes, SQL Server n’utilise qu’un seul thread pour créer l’index columnstore.

Si votre table comporte plus de 1 million de lignes, mais QUE 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 pour s’adapter à l’allocation de mémoire disponible. Dans certains cas, le DOP doit être réduit à un pour pouvoir créer l'index sous une mémoire contrainte.

À compter de 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 portent sur un grand nombre de lignes, elles sont généralement dépendantes des E/S. La réduction des E/S pendant l’exécution des requêtes est donc une exigence essentielle dans la conception des index columnstore. Une fois que les données ont été lues en mémoire, il est primordial 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 offrent un taux de compression des données dix fois supérieur aux 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 dans un format compressé en mémoire. Cela réduit le nombre de lectures en mémoire de données identiques et, au final, le nombre d’E/S. Par exemple, avec une compression dix fois plus élevée, les index columnstore peuvent conserver dix fois plus de données en mémoire que si les données étaient stockées dans un format non compressé. Du fait qu’il y ait davantage de données en mémoire, l’index columnstore a plus de chances de trouver les données dont il a besoin dans la mémoire sans entraîner des lectures supplémentaires sur le 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 et ont souvent des valeurs similaires. Les méthodes de compression de données offrent des taux de compression particulièrement élevés en présence de valeurs similaires.
Par exemple, si une table de faits stocke les adresses client et a une colonne pour le pays/la région, le nombre total de valeurs possibles est inférieur à 200. Certaines de ces valeurs sont répétées de nombreuses fois. Si la table de faits comporte 100 millions de lignes, la colonne pays/région compresse facilement et nécessite très 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 utilise plus d’octets pour compresser les valeurs dans la colonne pays/région.

É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. Cette fonction, appelée élimination de colonne, réduit également les E/S nécessaires pour l’exécution d’une requête et améliore ainsi les performances de requête.

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 une ligne entière pour récupérer les valeurs de certaines colonnes. Il lit donc inutilement davantage de données en mémoire, ce qui augmente les E/S.
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. L’index ne lit pas les 45 autres colonnes. Cela représente une réduction supplémentaire de 90 % des E/S, en supposant que toutes les colonnes sont de taille similaire. Si les mêmes données étaient stockées dans un rowstore, le processeur de requêtes devrait lire les 45 autres colonnes.

É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 B-tree non cluster standard sur un index columnstore cluster comme vous pouvez le faire sur un tas rowstore. 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 la fonction d’élimination de rowgroup, l’analyse de table complète serait très longue, surtout 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 ?

Prenons l’exemple d’une entreprise de vente au détail qui stocke ses données de vente dans une table de faits ayant un index cluster columnstore. Chaque nouvelle vente est enregistrée avec les différents attributs de la transaction, tels que la date de vente d’un produit. Curieusement, même si l’index columnstore ne garantit pas un ordre de tri, les lignes de cette table sont chargées dans un ordre chronologique. Cette table grossit au fil du temps. 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 ». Cela représente une réduction supplémentaire de 97 % du volume des 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. Si aucun segment de colonne ne correspond à la plage de valeurs définie dans les critères du prédicat de requête, le rowgroup entier est ignoré sans entraîner d’E/S supplémentaires. Ce principe fonctionne, car les données sont généralement chargées dans un ordre trié et, même si les lignes ne sont pas forcément triées, les valeurs de données similaires sont souvent situées dans le même rowgroup ou dans un rowgroup proche.

Pour plus d’informations sur les rowgroups, consultez Les instructions de 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 s’effectue sur les données compressées quand cela est possible et élimine certains opérateurs d’échange utilisés par le traitement en mode ligne. Cette méthode accélère l’exécution des requêtes analytiques 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 comme Insert, Delete ou Update sont exécutées ligne par ligne. Les opérateurs en mode batch ciblent les opérateurs tels que Scan, Join, Aggregate, Sort, etc. pour améliorer la vitesse de traitement des requêtes. Étant donné que l’index columnstore a été introduit dans SQL Server 2012 (11.x), il existe un effort soutenu pour développer les opérateurs qui peuvent être exécutés en mode batch. Le tableau ci-dessous répertorie les opérateurs exécutables en mode batch pour chaque version du produit.

Opérateurs en mode batch	Contexte d’utilisation	SQL Server 2012 (11.x)	SQL Server 2014 (12.x)	SQL Server 2016 (13.x) et SQL Database¹	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.
columnstore index Scan (non cluster)	SCAN	oui	oui	oui	oui
Index Seek		Indisponible	Indisponible	non	Nous effectuons une opération de recherche via un index B-tree non cluster en mode ligne.
Compute Scalar	Expression ayant pour résultat une valeur scalaire.	oui	oui	oui	Des restrictions s’appliquent au type de données. Cela est vrai pour tous les opérateurs en mode batch.
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
trier	Tri par clause sur SCAN avec l’index columnstore.	non	Non	oui
Top Sort		non	Non	oui
Window Aggregates		Indisponible	Non disponible	oui	Nouvel opérateur dans SQL Server 2016 (13.x).

¹ S’applique à SQL Server 2016 (13.x), aux niveaux SQL Database Premium, aux niveaux Standard - S3 et versions ultérieures, ainsi qu’à 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.

Pushdown d’agrégation

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, mais avec SQL Server 2016 (13.x), l’opération d’agrégation peut être envoyée (push) au nœud SCAN pour améliorer les performances du calcul agrégé 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, moneyet decimalnumeric avec précision <= 18
- smalldate, date, datetime, datetime2, time

Par exemple, une agrégation en mode Push est effectuée dans les deux requêtes ci-dessous :

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. 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.

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. ProductCode est une clé primaire classique qui est généralement représentée par un type de données string. Pour améliorer les performances des requêtes, il est recommandé de créer une clé de substitution, généralement une colonne de type integer, pour faire référence à la ligne dans la table de dimension à partir de la table de faits.

L’index columnstore offre de très bonnes performances pour l’exécution de requêtes analytiques avec des jointures/prédicats impliquant des clés numériques ou entières. Toutefois, pour beaucoup de charges de travail client, l’utilisation de colonnes de type string associant des tables de faits/dimension, les performances de requête avec l’index columnstore n’étaient pas aussi bonnes. SQL Server 2016 (13.x) améliore les performances des requêtes d’analyse avec des colonnes basées sur des chaînes de manière significative en transmettant les prédicats avec des colonnes de chaîne au nœud SCAN.

Pour améliorer les performances de requête, la transmission Push des prédicats de type string utilise le dictionnaire principal/secondaire créé pour les colonnes. Par exemple, prenons un segment de colonne de type string situé dans un rowgroup de 100 valeurs string distinctes. Cela signifie que chaque valeur string est référencée 10 000 fois sur environ un million de lignes.

Avec la transmission Push des prédicats de type string, la requête calcule le prédicat d’après les valeurs dans le dictionnaire et, en cas d’éligibilité, toutes les lignes faisant référence à la valeur de dictionnaire sont automatiquement qualifiées. Cela améliore les performances de deux manières :

Seule la ligne qualifiée est renvoyée, ce qui réduit le nombre de lignes à transmettre à partir du nœud SCAN.
Le nombre de comparaisons de chaînes s’en trouve considérablement réduit. Dans cet exemple, seulement 100 chaînes doivent être comparées au lieu d’un million. Les limitations suivantes s’appliquent :
- 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.
- Aucun prédicat de chaîne n’est envoyé si le dictionnaire dépasse 64 Ko d’entrées.
- Les expressions ayant pour résultat une valeur 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 avec 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 chaîne, binaire, identificateur unique 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’une fonction REBUILD ou DROP/CREATE.

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

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

Dans Azure Synapse Analytics et à partir de SQL Server 2022 (16.x), vous pouvez créer des index columnstore en cluster ordonnés, ce qui permet de classer par colonnes pour faciliter l’élimination des segments, en particulier pour les colonnes de chaîne. Dans les index columnstore cluster 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 en raison de l’élimination des segments sur d’autres colonnes de la table seront moins prévisibles. Pour plus d’informations sur les index columnstore en cluster ordonnés, consultez Utiliser un index columnstore cluster ordonné pour les tables d’entrepôt de données volumineuses.

À l’aide de l’option de connexion de requête SET STATISTICS IO, vous pouvez afficher l’élimination des segments en action. Recherchez la 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 SET STATISTICS IO ci-dessous d’une requête, environ 83 % des données ont été ignorées par la requête :

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