Partager via

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 être ordonnées implicitement sans spécifier la ORDER clause. 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. L’ordre de l’index columnstore par la même load_date colonne explicitement dans la ORDER clause n’est pas susceptible de fournir un avantage supplémentaire en matière de performances.

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é en spécifiant la ORDER clause dans l’instruction CREATE COLUMNSTORE INDEX , le moteur de base de données trie les données dans chaque segment de chaque colonne d’ordre avant que le générateur d’index compresse les données 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 et améliorer les performances des requêtes

Lorsque vous générez un index columnstore ordonné, le moteur de base de données trie les données de manière optimale. 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), l’ordre d’une colonne dans un index columnstore peut être plein sans chevauchement de segment ou partiel avec un certain chevauchement de segment. Lorsqu’il existe moins de segments qui se chevauchent, une requête qui peut tirer parti de l’ordre des colonnes s’exécute plus rapidement.

Conseil / Astuce

Même si l’ordre dans une colonne d’un index columnstore est partiel, les segments peuvent toujours être éliminés (ignorés). Une commande complète n’est pas nécessaire pour obtenir des avantages en termes de performances si un ordre partiel évite de nombreux chevauchements de segments.

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

Prerequisites Type de commande
ONLINE = ON et MAXDOP = 1 Complète
ONLINE = OFF, MAXDOP = 1, et les données à trier s'insèrent parfaitement dans l'espace mémoire de l'espace de travail de requête. Complète
Tous les autres cas Partiel

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 et nécessite suffisamment d’espace libre en 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.

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, du nombre de segments qui se chevauchent 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 l’exemple suivant, la table T1 a un index columnstore cluster avec Col_C, Col_Bet Col_A sous forme de colonnes ordonnées.

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

La requête 1 bénéficie de l’index columnstore ordonné plus que les requêtes 2 et 3, car la requête 1 fait référence à toutes les colonnes ordonnées dans son prédicat.

-- 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_A = 'a';

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

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 une table avec 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, car les rowgroups compressés dans un index columnstore sont immuables.

Pour réduire le chevauchement des segments après l’insertion de nouvelles données ou la mise à jour des données existantes, régénérez l’index columnstore.

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 clusterisé ordonné en ligne avec tri complet sur une table en tas

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

Reconstruire un index columnstore en cluster ordonné en ligne avec un ordre complet

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

Contenu connexe

  • Last updated on