Réglage des performances avec des index columnstore cluster ordonnés

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

En activant une élimination efficace des segments, les index columnstore en cluster ordonné (CCI) fournissent des performances beaucoup 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 des données dans une table à index columnstore cluster ordonné peut prendre plus de temps que dans une table à index columnstore cluster non-ordonné en raison de l’opération de tri des données. Toutefois, les requêtes peuvent s’exécuter plus rapidement après avec l’index columnstore cluster ordonné.

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

Pour obtenir la disponibilité de l’index columnstore ordonné, consultez la disponibilité de l’index de colonne ordonné.

Index columnstore cluster ordonné et non ordonné

Par défaut, pour chaque table créée sans option d’index, un composant interne (générateur d’index) crée un index columnstore cluster non ordonné dessus. Les données incluses dans chaque colonne sont compressées dans un segment de rowgroup distinct de l’index columnstore cluster. Des métadonnées existent sur la plage de valeurs de chaque segment, si bien que les segments qui se trouvent en dehors des limites du prédicat de la requête ne sont pas lus à partir du disque pendant l’exécution de la requête. Un index columnstore cluster offre le niveau de compression de données le plus élevé et réduit la taille des segments à lire si bien que les requêtes peuvent s’exécuter plus rapidement. En revanche, étant donné que le générateur d’index ne trie pas les données avant de les compresser dans des segments, les segments peuvent avoir des plages de valeurs qui se chevauchent, ce qui oblige les requêtes à lire plus de segments à partir du disque et prolongent donc leur durée d’exécution.

Lors de la création d’une cci ordonnée, sql Moteur de base de données trie les données existantes en mémoire par la ou les clés d’ordre avant que le générateur d’index les compresse en segments d’index. Avec les données triées, le chevauchement de segments est réduit, ce qui permet aux requêtes d’éliminer plus efficacement des segments et donc d’accélérer leurs performances, car le nombre de segments à lire à partir du disque est plus petit. Si toutes les données peuvent être triées en mémoire simultanément, le chevauchement de segments peut être évité. En raison des tables volumineuses dans les entrepôts de données, ce scénario ne se produit pas souvent.

Pour vérifier les plages de segments d’une colonne, exécutez la commande suivante avec le nom de la table et le nom de la colonne :

SQL

SELECT
o.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,
cls.max_data_id-cls.min_data_id as difference 

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 o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Notes

Dans une table avec index columnstore cluster ordonnée, les nouvelles données résultant du même lot d’opérations DML ou de chargement de données sont triées dans ce lot : il n’y a pas de tri global de toutes les données de la table. Les utilisateurs peuvent reconstruire l’index columnstore cluster ordonné pour trier toutes les données de la table. Pour une table partitionnée, la reconstruction est effectuée une partition à la fois. Les données de la partition en cours de reconstruction sont « hors connexion » et ne sont pas disponibles tant que la reconstruction n’est pas terminée pour cette partition.

Performances des requêtes

Le gain de performance d’une requête d’un index columnstore cluster 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 du DWU et de la classe de ressources choisis pour l’exécution de la requête. Les utilisateurs doivent passer en revue tous ces facteurs avant de choisir les colonnes de tri lors de la conception d’une table à index columnstore cluster ordonné.

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

• Les requêtes ont des prédicats d’égalité, d’inégalité ou de plage
• Les colonnes de prédicat et les colonnes d’index columnstore cluster ordonné sont les mêmes.

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

SQL

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

Les performances de la requête 1 et de la requête 2 peuvent tirer meilleur parti de l’ICC trié par rapport aux autres requêtes, car elles référencent toutes les colonnes ICC ordonnées.

SQL

-- 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 du chargement des données

Les performances du chargement des données dans une table à index columnstore cluster ordonné sont similaires à celles d’une table partitionnée. Le chargement des données dans une table à index columnstore cluster ordonné peut prendre plus de temps que dans une table à index columnstore cluster non-ordonné en raison de l’opération de tri des données. Toutefois, les requêtes peuvent s’exécuter plus rapidement après avec l’index columnstore cluster ordonné.

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 paramètre de degré maximal de parallélisme (MAXDOP) durant la création d’un index columnstore cluster ordonné. Les stratégies suivantes réduisent le chevauchement des segments lors de la création d’un index columnstore cluster (CCI) ordonné.

• Créez un index columnstore cluster ordonné avec OPTION (MAXDOP = 1). Chaque thread utilisé pour la création d’un index columnstore cluster ordonné fonctionne sur un sous-ensemble des données et les 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 nécessaire à la création d’un index columnstore cluster ordonné, mais génère plus de segments qui se chevauchent que l’utilisation d’un thread unique. L’utilisation d’une seule opération avec threads offre la qualité de compression la plus élevée. Vous pouvez spécifier MAXDOP avec les commandes ou CREATE TABLE les CREATE INDEX commandes. Par exemple :

SQL

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

• Pré-triez les données par clé ou clés de tri avant de les charger dans des tables.

Voici un exemple de distribution de table à index columnstore cluster ordonné sans aucun segment qui se chevauche après l’application des recommandations ci-dessus. L’cci ordonnée est ordonnée sur une colonne bigint sans doublons.

Créer un index columnstore cluster ordonné sur des tables volumineuses

La création d’un index columnstore cluster ordonné est une opération hors connexion. Pour des tables sans partitions, les données ne sont pas accessibles aux utilisateurs tant que le processus de création de l’index columnstore cluster ordonné n’est pas terminé. Pour des tables partitionnées, dans la mesure où le moteur crée la partition d’index columnstore cluster ordonné par partition, les utilisateurs peuvent quand même accéder aux données dans les partitions pour lesquelles la création du index columnstore cluster ordonné n’est pas en cours. Vous pouvez utiliser cette option pour minimiser le temps d’arrêt lors de la création d’un index columnstore cluster ordonné sur des tables volumineuses :

1. Créez des partitions sur la grande table cible (appelée Table_A).
2. Créez une table d’index columnstore cluster ordonné vide (appelée Table_B) avec les mêmes table et schéma de partition que Table_A.
3. Basculez une partition de Table_A vers Table_B.
4. Exécutez ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> pour régénérer la partition basculée sur Table_B.
5. Répétez les étapes 3 et 4 pour chaque partition dans Table_A.
6. Une fois toutes les partitions basculées de Table_A vers Table_B et régénérées, laissez Table_A et renommez Table_B en Table_A.

Fonctionnalités de SQL Server 2022

SQL Server 2022 (16.x) a introduit des index columnstore cluster ordonnés similaires à la fonctionnalité dans des pools dédiés Azure Synapse.

• SQL Server 2022 (16.x) et versions ultérieures et d’autres plateformes SQL prennent en charge les fonctionnalités d’élimination de segments en cluster améliorées pour les types de données string, binaire et guid, ainsi que le type de données datetimeoffset pour une mise à l’échelle supérieure à deux. Auparavant, 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.
• Actuellement, seuls SQL Server 2022 (16.x) et versions ultérieures et d’autres plateformes SQL prennent en charge l’élimination de rowgroup columnstore cluster pour le préfixe des LIKE prédicats, 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'.

Pour obtenir la disponibilité de l’index columnstore ordonné, consultez la disponibilité de l’index de colonne ordonné.

Pour plus d’informations, consultez Nouveautés des index columnstore.

Pour plus d’informations sur les index columnstore ordonnés dans des pools SQL dédiés dans Azure Synapse Analytics, consultez Réglage des performances avec des index columnstore en cluster ordonnés.

Exemples

R. Pour vérifier les colonnes ordonnées et le numéro d’ordre :

SQL

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

B. Pour modifier un numéro de colonne, ajouter ou supprimer des colonnes dans la liste des ordres ou passer d’un index columnstore cluster à un index columnstore cluster ordonné :

SQL

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Contenu connexe

• Indications pour la conception d’index columnstore
• Index columnstore - Conseils en matière de chargement de données
• Bien démarrer avec columnstore pour l’analytique opérationnelle en temps réel
• Index Columnstore dans l’entreposage de données
• Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources
• Index columnstore - Architecture
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)