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

S’applique à : SQL Server 2022 (16.x) et versions ultérieures Base de données SQL AzureInstance gérée SQL AzureBase de données SQL Azure dans Microsoft Fabric

Les index columnstore ordonnés peuvent améliorer les performances en sautant de grandes quantités de données ordonnées qui ne correspondent pas au prédicat de requête. Lorsqu'on charge des données dans un index columnstore ordonné et que l'ordre est maintenu par une reconstruction de l'index, cela prend plus de temps que dans un index non ordonné. Cependant, les requêtes indexées peuvent s'exécuter plus rapidement avec un columnstore ordonné.

Lorsqu’une requête lit un index columnstore, le moteur de base de données vérifie les valeurs minimales et maximales stockées dans chaque segment de colonne. Le processus élimine les segments qui se trouvent en dehors des limites du prédicat de requête. En d’autres termes, il ignore ces segments lors de la lecture de données à partir d’un disque ou d’une mémoire. Une requête se termine plus rapidement si le nombre de segments à lire et leur taille totale est beaucoup plus petit.

Avec certains modèles de chargement de données, les données d’un index columnstore peuvent déjà être ordonnées. Par exemple, si des chargements de données se produisent tous les jours, les données peuvent être classées par une load_date colonne. Dans ce cas, les performances des requêtes peuvent déjà tirer parti de cet ordre implicite. Ordonner l'index columnstore par la même colonne load_date n'est probablement pas avantageux en termes de performances supplémentaires.

Pour connaître la disponibilité de l’index columnstore ordonné dans différentes plateformes SQL et versions de SQL Server, 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, afin que le processus d’exécution de requête puisse ignorer les segments qui se trouvent en dehors des limites du prédicat de 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 les segments avec des plages de valeurs qui se chevauchent peuvent se produire, ce qui entraîne la lecture de requêtes d’autres segments pour obtenir les données requises. Les requêtes peuvent donc prendre plus de temps pour être complétées.

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’utiliser une élimination de segment plus efficace et ainsi des performances plus rapides, car il y a moins de segments et moins de données à lire.

Réduire le chevauchement des segments

Lorsque vous générez un index columnstore ordonné, le moteur de base de données trie les données de manière optimale. En fonction de la mémoire disponible, de la taille des données, du degré de parallélisme, du type d’index (clustered ou non cluster) et du type de build d’index (hors connexion ou en ligne), le tri des index columnstore ordonnés peut être plein sans chevauchement de segment ou partiel avec un chevauchement de segments.

Le tableau suivant décrit le type de tri résultant lorsque vous créez ou régénérez un index columnstore ordonné, en fonction des options de génération d’index.

Dans le premier cas où les deux ONLINE = ON et MAXDOP = 1 sont présents, le tri n’est pas limité par la mémoire de l’espace de travail de requête, car une construction en ligne d’un index en magasin de colonnes ordonné utilise la base de données tempdb pour déverser les données qui n’entrent pas en mémoire. Cette approche peut ralentir le processus de génération d’index en raison des E/S supplémentaires tempdb . Toutefois, étant donné que la build d’index est effectuée en ligne, les requêtes peuvent continuer à utiliser l’index existant pendant la génération du nouvel index ordonné.

De même, avec une reconstruction hors connexion d’un index columnstore partitionné, la reconstruction est effectuée une partition à la fois. D’autres partitions restent disponibles pour les requêtes.

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 cela entraîne davantage de segments qui se chevauchent que lors de l’utilisation d’un seul thread.

Vous pouvez créer ou reconstruire des index columnstore ordonnés en ligne uniquement dans certaines plateformes SQL et versions de SQL Server. Pour plus d’informations, consultez le résumé des fonctionnalités pour les versions du produit.

Dans SQL Server, les opérations d’index en ligne ne sont pas disponibles dans toutes les éditions. Pour plus d’informations, consultez Éditions et fonctionnalités prises en charge de SQL Server 2025 et Effectuer des opérations d’index en ligne.

Ajouter de nouvelles données ou mettre à jour des données existantes

Les nouvelles données résultant d’un lot DML ou d’une opération de chargement en bloc sur un index columnstore ordonné sont triées dans ce lot uniquement. Il n’existe aucun tri global qui inclut des données existantes dans la table. Pour réduire les chevauchements de segments après l’insertion de nouvelles données ou la mise à jour des données existantes, régénérez l’index.

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 qualité du tri 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 les 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 d’une charge de 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.

Examples

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);

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

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       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;

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);

Déterminer la qualité de tri d’un index columnstore ordonné

Cet exemple détermine la qualité de tri pour tous les index columnstore ordonnés dans la base de données. Dans cet exemple, la qualité de tri est définie comme un ratio de segments non superposés à tous les segments pour chaque colonne d’ordre, exprimée sous la forme d’un pourcentage.

WITH ordered_column_segment
AS (SELECT p.object_id,
           i.name AS index_name,
           ic.column_store_order_ordinal,
           cls.row_count,
           cls.column_id,
           cls.min_data_id,
           cls.max_data_id,
           LAG(max_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS prev_max_data_id,
           LEAD(min_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS next_min_data_id
    FROM sys.partitions AS p
         INNER JOIN sys.indexes AS i
             ON p.object_id = i.object_id
            AND p.index_id = i.index_id
         INNER JOIN sys.column_store_segments AS cls
             ON p.partition_id = cls.partition_id
         INNER JOIN sys.index_columns AS ic
             ON ic.object_id = p.object_id
            AND ic.index_id = p.index_id
            AND ic.column_id = cls.column_id
    WHERE ic.column_store_order_ordinal > 0)
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       index_name,
       INDEXPROPERTY(object_id, index_name, 'IsClustered') AS is_clustered_column_store,
       COL_NAME(object_id, column_id) AS order_column_name,
       column_store_order_ordinal,
       SUM(row_count) AS row_count,
       SUM(is_overlapping_segment) AS overlapping_segments,
       COUNT(1) AS total_segments,
       (1 - SUM(is_overlapping_segment) / COUNT(1)) * 100 AS order_quality_percent
FROM ordered_column_segment
CROSS APPLY (SELECT CAST (IIF (prev_max_data_id > min_data_id
                 OR next_min_data_id < max_data_id, 1, 0) AS FLOAT) AS is_overlapping_segment
            ) AS ios
GROUP BY object_id, index_name, column_id, column_store_order_ordinal
ORDER BY schema_name, object_name, index_name, column_store_order_ordinal;

Contenu connexe

• Indications de conception d’index columnstore
• Indexes Columnstore : aide au chargement des données
• Prise en main des index columnstore pour l’analytique opérationnelle en temps réel
• Index columnstore dans un entrepôt de données
• Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources
• Architecture des index columnstore
• CRÉER UN INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)