Partager via

Réglage du niveau de performance avec des index columnstore ordonnés

  • Article

S’applique à : SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

En activant une élimination efficace des segments, les index columnstore ordonnés offrent des performances plus rapides en ignorant de grandes quantités de données ordonnées qui ne correspondent pas au prédicat de requête. Le chargement de données dans un index columnstore ordonné et son classement via les reconstructions d’index peuvent prendre plus de temps que dans un index non ordonné en raison de l’opération de tri des données, toutefois, les requêtes d’index columnstore triées peuvent s’exécuter plus rapidement après.

Lorsque les utilisateurs interrogent une table columnstore, l’optimiseur vérifie les valeurs minimales et maximales stockées dans chaque segment. Les segments en dehors des limites du prédicat de la requête ne sont pas lus à partir du disque vers la mémoire. Une requête peut se terminer plus rapidement si le nombre de segments à lire et leur taille totale sont plus petits.

Pour connaître la disponibilité de l’index columnstore ordonné, consultez Disponibilité de l’index columnstore ordonné.

Pour plus d’informations sur les fonctionnalités récemment ajoutées pour les index columnstore, consultez Nouveautés des index columnstore.

Index columnstore ordonné et non ordonné

Dans un index columnstore, les données de chaque colonne de chaque rowgroup sont compressées dans un segment distinct. Chaque segment contient des métadonnées décrivant ses valeurs minimales et maximales, de sorte que les segments qui se trouvent en dehors des limites du prédicat de requête ne sont pas lus à partir du disque pendant l’exécution de la requête.

Lorsqu’un index columnstore n’est pas ordonné, le générateur d’index ne trie pas les données avant de les compresser en segments. Cela signifie que des segments avec des plages de valeurs qui se chevauchent peuvent se produire, ce qui conduit les requêtes à lire plus de segments depuis le disque et à prendre plus de temps pour se terminer.

Lorsque vous créez un index columnstore ordonné, le moteur de base de données trie les données existantes en fonction des clés de commande que vous spécifiez avant que le générateur d’index les compresse en segments. Avec les données triées, le chevauchement de segments est réduit ou éliminé, ce qui permet aux requêtes d’avoir une élimination de segment plus efficace et ainsi des performances plus rapides, car il y a moins de segments à lire à partir du disque.

Selon la mémoire disponible, la taille des données, le degré de parallélisme, le type d’index (clusteré ou non cluster) et le type de build d’index (hors connexion ou en ligne), le tri des index columnstore ordonnés peut être complet (aucun chevauchement de segment) ou partiel (certains chevauchements de segments). Par exemple, le tri partiel se produit lorsque la mémoire disponible est insuffisante pour un tri complet. Les requêtes utilisant un index columnstore ordonné s’exécutent souvent plus rapidement qu’avec un index non ordonné, même si l’index ordonné a été généré à l’aide d’un tri partiel.

Le tri complet est fourni pour les index columnstore en cluster ordonnés créés ou reconstruits avec les deux options ONLINE = ON et MAXDOP = 1. Dans ce cas, le tri n’est pas limité par la mémoire disponible, car il utilise la base de données tempdb pour déverser les données qui ne correspondent pas à la mémoire. Cela peut ralentir le processus de génération d’index en raison des E/S supplémentaires tempdb. Toutefois, avec une reconstruction d’index en ligne, les requêtes peuvent continuer à utiliser l’index existant pendant la reconstruction du nouvel index ordonné.

Le tri complet peut également être fourni pour les index columnstore en cluster et non en cluster ordonnés, créés ou reconstruits avec les options ONLINE = OFF et MAXDOP = 1 si la quantité de données à trier est suffisamment petite pour s’ajuster entièrement à la mémoire disponible.

Dans tous les autres cas, le tri dans les index columnstore ordonnés est partiel.

Note

Actuellement, les index columnstore ordonnés peuvent être créés ou reconstruits en ligne uniquement dans Azure SQL Database et dans Azure SQL Managed Instance avec la stratégie de mise à jour Always-up-to-date.

Pour vérifier les plages de segments d’une colonne et déterminer s’il existe un chevauchement de segments, utilisez la requête suivante, en remplaçant les espaces réservés par votre schéma, votre table et vos noms de colonnes :

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Par exemple, la sortie de cette requête pour un index columnstore entièrement trié peut se présenter comme suit. Notez qu’il n’existe aucun chevauchement dans les colonnes min_data_id et max_data_id pour différents segments.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Note

Dans un index columnstore ordonné, les nouvelles données résultant du même lot d’opérations de chargement de données ou DML sont triées dans ce lot uniquement. Il n’existe aucun tri global qui inclut des données existantes dans la table.

Pour trier les données dans l’index après avoir inséré de nouvelles données ou mis à jour des données existantes, régénérez l’index.

Pour une reconstruction hors connexion d’un index columnstore partitionné, la reconstruction est effectuée une partition à la fois. Les données de la partition en cours de reconstruction ne sont pas disponibles tant que la reconstruction n’est pas terminée pour cette partition.

Les données restent disponibles lors d’une reconstruction en ligne. Pour plus d’informations, consultez Effectuer des opérations d’index en ligne.

Performances des requêtes

Le gain de performances d’un index columnstore ordonné dépend des modèles de requête, de la taille des données, de la façon dont les données sont triées, de la structure physique des segments et des ressources de calcul disponibles pour l’exécution des requêtes.

Les requêtes avec les modèles suivants s’exécutent généralement plus rapidement avec des index columnstore ordonnés.

  • Requêtes qui ont des prédicats d’égalité, d’inégalité ou de plage de valeurs.
  • Requêtes où les colonnes de prédicat et les colonnes CCI ordonnées sont identiques.

Dans cet exemple, la table T1 a un index columnstore clusterisé ordonné dans la séquence de Col_C, Col_B, et Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

Les performances de la requête 1 et 2 peuvent tirer parti de l’index columnstore ordonné plus que la requête 3 et 4, car elles référencent toutes les colonnes ordonnées.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Performances de chargement des données

Les performances de la charge des données dans une table avec un index columnstore ordonné sont similaires à celles d’une table partitionnée. Le chargement de données peut prendre plus de temps qu’avec un index columnstore non ordonné en raison de l’opération de tri des données, mais les requêtes peuvent s’exécuter plus rapidement par la suite.

Réduire le chevauchement de segments

Le nombre de segments qui se chevauchent dépend de la taille des données à trier, de la mémoire disponible et du degré maximal de parallélisme (MAXDOP) lors de la génération d’index columnstore ordonnée. Les stratégies suivantes réduisent le chevauchement des segments, mais elles peuvent rendre le processus de génération d’index plus long.

  • Si la génération d’index en ligne est disponible, utilisez les options ONLINE = ON et MAXDOP = 1 lors de la création d’un index columnstore organisé en cluster. Cela crée un index entièrement trié.
  • Si la création de l'index en ligne n'est pas disponible, utilisez l'option MAXDOP = 1.
  • Triez les données en fonction des clés de tri avant le chargement.

Lorsque MAXDOP est supérieur à 1, chaque thread utilisé pour la création d'un index columnstore ordonné fonctionne sur un sous-ensemble de données et le trie localement. Il n’y a pas de tri global sur les données triées par différents threads. L’utilisation de threads parallèles peut réduire le temps de création de l’index, mais elle génère plus de segments qui se chevauchent que lors de l’utilisation d’un seul thread. L'utilisation d'une opération monothreadée offre la qualité de compression la plus élevée. Vous pouvez spécifier MAXDOP avec la commande CREATE INDEX.

Exemples

Rechercher les colonnes ordonnées et l’ordinal d’ordre

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Créer un index columnstore ordonné

Index columnstore classé en cluster :

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Index columnstore classé non en cluster :

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Ajouter ou supprimer des colonnes d’ordre et reconstruire un index columnstore ordonné existant

Index columnstore classé en cluster :

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Index columnstore classé non en cluster :

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Créer un index columnstore en cluster ordonné en ligne avec tri complet sur une table de tas

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Reconstruire en ligne un index columnstore partitionné et ordonné avec un tri complet

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Contenu connexe