Guide de conception et d’architecture d’index SQL Server et Azure SQL

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

L'engorgement des applications de base de données est souvent imputable à des index mal conçus ou en nombre insuffisant. La conception d'index efficaces est primordiale pour le bon fonctionnement des bases de données et des applications. Ce guide de conception d’index contient des informations sur l’architecture des index, ainsi que des bonnes pratiques permettant de créer des index performants et adaptés aux besoins de votre application.

Ce guide suppose que le lecteur connaît les types d’index disponibles. Pour obtenir description générale des types d'index, consultez Types d'index.

Ce guide couvre les types d’index suivants :

Format de stockage principal	Type d’index
Rowstore sur disque
	Cluster
	Non-cluster
	Unique
	Filtré
ColumnStore
	Columnstore cluster
	Columnstore non-cluster
Optimisé pour la mémoire
	Hash
	Non-cluster à mémoire optimisée

Pour plus d’informations sur les index XML, consultez Vue d’ensemble des index XML et Index XML sélectifs (SXI).

Pour plus d’informations sur les index spatiaux, consultez Vue d’ensemble des index spatiaux.

Pour plus d’informations sur les index de recherche en texte intégral, consultez Remplir les index de recherche en texte intégral.

Principes de base de la conception d’index

Pensez à un livre ordinaire : à la fin du livre, il existe un index qui permet de rechercher rapidement les informations dans le livre. L’index est une liste triée de mots clés, chacun accompagné d’un ensemble de numéros de page pointant vers les pages où il se trouve.

Un index rowstore n’est pas différent : il s’agit d’une liste triée de valeurs et, pour chaque valeur, il existe des pointeurs vers les pages de données où ces valeurs se trouvent. L’index lui-même est stocké sur les pages, appelées pages d’index. Dans un livre ordinaire, si l’index s’étend sur plusieurs pages et que vous devez rechercher des pointeurs vers toutes les pages qui contiennent le mot « SQL », par exemple, vous devez feuilleter jusqu’à ce que vous trouviez la page d’index qui contient le mot clé « SQL ». À partir de là, vous suivez les pointeurs vers toutes les pages du livre. Cela peut être optimisé si, au tout début de l’index, vous créez une page unique qui contient une liste alphabétique de chaque lettre. Par exemple : « A à D - page 121 », « E à G - page 122 », et ainsi de suite. Cette page supplémentaire évite de feuilleter l’index pour rechercher le point de départ. Une telle page n’existe pas dans les livres réguliers, mais elle existe dans un index rowstore. Cette page unique est appelée page racine de l’index. La page racine est la page de démarrage de l’arborescence utilisée par un index. En suivant l’analogie de l’arborescence, les pages de fin qui contiennent des pointeurs vers les données réelles sont appelées « pages feuilles » de l’arborescence.

Un index est une structure sur disque ou en mémoire associée à une table ou à une vue qui accélère l’extraction des lignes de la table ou de la vue. Un index rowstore contient des clés créées à partir d’une ou plusieurs colonnes de la table ou de la vue. Pour les index rowstore, ces clés sont stockées dans une arborescence (arbre B+) qui permet au moteur de base de données de trouver rapidement et efficacement la ou les lignes associées aux valeurs de clé.

Un index rowstore stocke les données organisées logiquement sous la forme d’une table avec des lignes et des colonnes, et physiquement stockées dans un format de données de ligne appelé rowstore¹, ou stockés dans un format de données en colonne appelé columnstore.

Le choix d'index adaptés à une base de données et à sa charge de travail est une opération complexe qui vise à trouver un compromis entre vitesse des requêtes et coûts de mise à jour. Les index rowstore sur disque étroits, c’est-à-dire les index ne comportant que quelques colonnes dans la clé d’index, nécessitent moins d’espace disque et de besoins de maintenance. En revanche, les index larges couvrent plus de requêtes. Vous devrez éventuellement essayer plusieurs conceptions différentes avant de trouver l'index le plus performant. Il est possible d'ajouter, de modifier et de supprimer des index sans affecter le schéma de la base de données ou la conception des applications. Par conséquent, vous ne devez pas hésiter à expérimenter des index différents.

L’optimiseur de requête dans le moteur de base de données choisit de manière fiable l’index le plus efficace dans la plupart des cas. Votre stratégie globale de conception d’index doit fournir différents index pour l’optimiseur de requête à choisir et lui faire confiance pour prendre la bonne décision. Ce procédé permet de réduire le temps d'analyse et produit de bons résultats dans bon nombre de cas. Pour voir quels index l’optimiseur de requête utilise pour une requête spécifique, dans SQL Server Management Studio, dans le menu Requête , sélectionnez Inclure le plan d’exécution réel.

L'utilisation d'index n'est pas forcément synonyme de bonnes performances, et inversement, de bonnes performances ne sauraient être nécessairement attribuables à l'utilisation d'index efficaces. Si l'utilisation d'un index contribuait toujours à produire les meilleurs résultats, le travail de l'optimiseur de requête en serait simplifié. En réalité, le choix d'un index inapproprié peut aboutir à des performances moins que satisfaisantes. Par conséquent, la tâche de l’optimiseur de requête consiste à sélectionner un index ou une combinaison d’index, uniquement lorsqu’il améliore les performances et pour éviter la récupération indexée lorsqu’il entrave les performances.

¹ Rowstore est la méthode standard de stockage des données de table relationnelles. Rowstore fait référence à la table où le format de stockage de données sous-jacent est un tas, une arborescence B+ (index cluster) ou une table optimisée en mémoire. Le rowstore basé sur disque exclut les tables optimisées en mémoire.

Tâches de conception d’index

La stratégie de conception d’index que nous recommandons est constituée des tâches suivantes :

1. Comprendre les caractéristiques de la base de données elle-même.

   • Par exemple, s’agit-il d’une base de données OLTP (traitement transactionnel en ligne) dont les données sont souvent modifiées et devant garantir un débit élevé ? Les index et tables à mémoire optimisée sont particulièrement bien adaptés pour ce scénario, car ils offrent une structure sans verrous. Pour plus d’informations, consultez Index pour les tables à mémoire optimisée, ou Indications pour la conception d’index non-cluster pour les tables à mémoire optimisée et Indications pour la conception d’index de hachage pour les tables à mémoire optimisée dans ce guide.
   • Ou s’agit-il d’un exemple de base de données DSS (système de support de décision) ou OLAP (entreposage de données) devant traiter rapidement des jeux de données très volumineux ? Les index columnstore sont particulièrement bien adaptés aux jeux de données d’entreposage de données standard. Les index columnstore peuvent transformer l'expérience utilisateur des entrepôts de données en améliorant considérablement les performances des requêtes communes liées aux entrepôts de données, par exemple en matière de filtrage, d'agrégation, de regroupement et de jointure en étoile. Pour plus d’informations, consultez Index columnstore - Présentation et Indications pour la conception d’index columnstore dans ce guide.

2. Comprendre les caractéristiques des requêtes les plus fréquemment utilisées. Par exemple, savoir qu’une requête fréquemment utilisée joint deux tables ou plus vous permet de déterminer le meilleur type d’index à utiliser.

3. Comprendre les caractéristiques des colonnes utilisées dans les requêtes. Par exemple, un index est idéal pour les colonnes qui ont un type de données entier et qui sont également des colonnes uniques ou non null. Pour les colonnes qui ont des sous-ensembles de données bien définis, vous pouvez utiliser un index filtré dans SQL Server 2008 (10.0.x) et versions ultérieures. Pour plus d'informations, consultez Instructions de conception d'index filtrés dans ce guide.

4. Identifier les options d'index qui peuvent améliorer les performances au moment de la création ou de la maintenance de l'index. Par exemple, si vous créez un index cluster dans une table volumineuse existante, vous avez tout intérêt à utiliser l’option d’index ONLINE. Cette option permet en effet la poursuite des activités concurrentes sur les données sous-jacentes pendant la création ou la reconstruction de l'index. Pour plus d’informations, consultez Définir les options d’index.

5. Déterminer l'emplacement de stockage optimal pour l'index.

   Un index non-cluster peut être stocké dans le même groupe de fichiers que celui auquel appartient la table sous-jacente, ou dans un groupe de fichiers distinct. L'emplacement de stockage des index peut améliorer les performances des requêtes par l'amélioration des performances d'E/S des disques. Par exemple, en stockant un index non-cluster dans un groupe de fichiers résidant sur un disque différent de celui du groupe de fichiers de la table, vous pouvez améliorer les performances, car plusieurs disques peuvent être lus simultanément. Une autre solution consiste à utiliser un schéma de partition sur plusieurs groupes de fichiers pour les index cluster et non-cluster. Si vous envisagez de recourir au partitionnement, vous devez déterminer si l'index doit être aligné, c'est-à-dire, partitionné plus ou moins de la même façon que la table, ou s'il doit être partitionné de façon indépendante. Découvrez davantage d’informations dans la section Placement d’index sur les groupes de fichiers ou les schémas de partition au sein de cet article.

6. Quand vous identifiez des index manquants à l’aide de vues DMV (vues de gestion dynamique), par exemple sys.dm_db_missing_index_details et sys.dm_db_missing_index_columns, des variantes d’index similaires peuvent vous être proposées pour la même table et les mêmes colonnes. Examinez les index existants de la table ainsi que les suggestions d’index manquants pour empêcher la création d’index en double. Découvrez davantage d’informations dans Paramétrer les index non-cluster avec les suggestions d’index manquants.

Instructions générales en matière de conception d’index

Les administrateurs de bases de données expérimentés peuvent concevoir de bons ensembles d’index, mais cette tâche est complexe, chronophage et sujette à erreurs, même dans le cas de bases de données et de charges de travail peu complexes. La compréhension des caractéristiques de votre base de données, de vos requêtes et de vos colonnes de données peut vous aider à créer des index optimaux.

Considérations liées à la base de données

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux bases de données :

• La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE, car à mesure que les données de la table changent, tous les index doivent être mis à jour en conséquence. Par exemple, si une colonne est utilisée dans plusieurs index et que vous exécutez une instruction UPDATE qui modifie les données de cette colonne, chaque index contenant cette colonne doit être mis à jour, ainsi que la colonne de la table de base sous-jacente (segment de mémoire ou index cluster).

  • Évitez que les tables mises à jour ne soient trop abondamment indexées et faites en sorte que les index soient étroits, c'est-à-dire qu'ils comprennent le moins de colonnes possible.

  • Utilisez de nombreux index pour améliorer les performances des requêtes sur les tables possédant des besoins réduits en matière de mise à jour, mais de grands volumes de données. Un grand nombre d’index peut aider les performances des requêtes qui ne modifient pas les données, telles que les instructions SELECT, car l’optimiseur de requête a plus d’index à choisir pour déterminer la méthode d’accès la plus rapide.

• L’indexation de petites tables peut ne pas être optimale, car l’optimiseur de requête peut prendre plus de temps pour parcourir l’index recherchant des données que pour effectuer une analyse de table de base. Par conséquent, les index de petites tables peuvent ne jamais être utilisés, mais doivent néanmoins être gérés, car les données de la table changent.

• Les index de vues peuvent vous permettre d'améliorer considérablement les performances lorsque la vue contient des agrégations, des jointures de tables ou une combinaison d'agrégations et de jointures. La vue n’a pas besoin d’être explicitement référencée dans la requête pour que l’optimiseur de requête l’utilise.

• Les bases de données sur les réplicas principaux dans Azure SQL Database génèrent automatiquement des recommandations relatives aux performances de Database Advisor pour les index. Vous pouvez éventuellement activer le réglage automatique des index.

• Le Magasin des requêtes permet d’identifier les requêtes avec des performances sous-optimales et fournit un historique des plans d’exécution de requête qui documentent les index sélectionnés par l’optimiseur.

Considérations concernant les requêtes

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux requêtes :

• Créez des index non cluster sur les colonnes fréquemment utilisées dans des prédicats et des conditions de jointure dans des requêtes. Il s’agit de vos colonnes SARGable¹. Toutefois, évitez d'ajouter des colonnes superflues. L'ajout d'un trop grand nombre de colonnes d'index peut avoir une influence négative sur les performances de gestion des index et de l'espace disque.

• La couverture des index peut améliorer les performances des requêtes, car toutes les données nécessaires pour répondre aux exigences de la requête existent dans l'index proprement dit. Cela signifie que seules les pages d'index, et non les pages de données de la table ou de l'index cluster, sont nécessaires pour récupérer les données demandées, réduisant ainsi globalement le nombre d'E/S des disques. Par exemple, une requête de colonnes A et B sur une table avec un index composite créé sur les colonnes A, B et C peut récupérer les données spécifiées à partir de l’index uniquement.

  Important

  Un index couvrant désigne un index non cluster qui résout directement un ou plusieurs résultats de requête similaires sans avoir accès à sa table de base ni entraîner de recherches.

  Ces index ont toutes les colonnes SARGable nécessaires au niveau feuille. Ainsi, les colonnes retournées par la clause SELECT et tous les arguments WHERE et JOIN sont couvertes par l’index.

  Il y a potentiellement beaucoup moins E/S pour exécuter la requête si l’index est assez petit par rapport aux lignes et aux colonnes de la table proprement dite, ce qui signifie qu’il s’agit d’un véritable sous-ensemble de la totalité des colonnes.

  Les index couvrants peuvent être intéressants lorsque seule une petite partie d’une grande table est sélectionnée et qu’elle est définie par un prédicat fixe, comme les colonnes éparses qui contiennent seulement quelques valeurs non NULL, par exemple.

• Rédigez des requêtes insérant ou modifiant un maximum de lignes en une seule instruction, plutôt que de recourir à plusieurs requêtes pour mettre à jour les mêmes lignes. De cette façon, la maintenance d'index optimisée peut être exploitée.

• Évaluez le type de requête et la manière dont les colonnes sont utilisées dans la requête. Par exemple, une colonne utilisée dans un type de requête de correspondance exacte constitue un candidat valable à un index non-cluster ou cluster.

¹ Le terme SARGable dans les bases de données relationnelles fait référence à un prédicat S earch ARGument capable d’utiliser un index pour accélérer l’exécution de la requête.

Remarques sur les colonnes

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux colonnes :

• Veillez à ce que la clé d'index des index cluster soit courte. En outre, les index cluster bénéficient d’être créés sur des colonnes uniques ou non null.

• Les colonnes qui sont des types de données ntext, text, image, varchar(max), nvarchar(max)et varbinary(max) ne peuvent pas être spécifiées en tant que colonnes clés d’index. Cependant, les types de données varchar(max), nvarchar(max), varbinary(max)et xml peuvent participer à des index non-cluster en tant que colonnes d’index non-clés. Pour plus d'informations, consultez la section Index avec colonnes inclusesdans ce guide.

• Un type de données xml ne peut être qu'une colonne clé dans un index XML. Pour plus d’informations, consultez Index XML. SQL Server 2012 SP1 a introduit un nouveau type d’index XML appelé index XML sélectif. Ce nouvel index peut améliorer les performances d’interrogation sur les données stockées au format XML, permettre l’indexation plus rapide des charges de travail de données XML volumineuses et améliorer la scalabilité en réduisant les coûts de stockage de l’index lui-même. Pour plus d’informations, consultez Index XML sélectifs (SXI).

• Vérifiez l'unicité des colonnes. Un index unique plutôt que non unique sur la même combinaison de colonnes procure des informations supplémentaires à l'optimiseur de requête, ce qui améliore l'utilité de l'index. Pour plus d'informations, consultez Instructions de conception d'index uniques dans ce guide.

• Examinez la distribution des données dans la colonne. Bien souvent, la longueur d'exécution d'une requête est due à l'indexation d'une colonne comportant peu de valeurs uniques ou à la réalisation d'une jointure sur ce type de colonne. Il s’agit d’un problème fondamental avec les données et les requêtes, et ne peut généralement pas être résolu sans identifier cette situation. Par exemple, un répertoire téléphonique physique trié par ordre alphabétique sur le nom ne accélère pas la localisation d’une personne si toutes les personnes de la ville sont nommées Smith ou Jones. Pour plus d'informations sur la distribution de données, consultez Statistics.

• Envisagez d'utiliser des index filtrés sur les colonnes qui ont des sous-ensembles bien définis, par exemple les colonnes éparses, les colonnes contenant principalement des valeurs NULL, les colonnes contenant des catégories de valeurs et les colonnes contenant des plages de valeurs distinctes. Un index filtré bien conçu peut améliorer les performances des requêtes et réduire les coûts de maintenance et de stockage.

• Considérez l’ordre des colonnes si l’index contient plusieurs colonnes. La colonne utilisée dans la clause WHERE dans une clause WHERE égale à (), supérieure à (=), inférieure à (>), inférieure à (<) ou UNE condition de recherche BETWEEN, ou qui participe à une jointure, doit être placée en premier. Les colonnes supplémentaires doivent être classées en fonction de leur niveau de différenciation, c'est-à-dire de la plus distincte à la moins distincte.

  Par exemple, si l'index est défini en tant que LastName, la valeur FirstName de l'index sera utile si la condition de recherche est WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'. Toutefois, l’optimiseur de requête n’utilise pas l’index pour une requête qui n’a recherché que sur FirstName (WHERE FirstName = 'Jane').

• Pensez à indexer les colonnes calculées. Pour plus d'informations, consultez Indexes on Computed Columns.

Caractéristiques des index

Après avoir déterminé qu’un index est approprié pour une requête, vous pouvez sélectionner le type d’index qui convient le mieux à votre situation. Un index doit posséder les caractéristiques suivantes :

• être cluster ou non-cluster ;
• être unique ou non unique ;
• être à une ou plusieurs colonnes ;
• être trié par ordre croissant ou décroissant d'après les colonnes qui le constituent ;
• table entière plutôt que filtré pour les index non cluster.
• être columnstore ou rowstore ;
• Hachage et non cluster pour les tables mémoire optimisées

Vous pouvez également personnaliser les caractéristiques de stockage initiales de l'index afin d'optimiser ses performances ou sa maintenance en définissant une option telle que FILLFACTOR. Vous pouvez également déterminer l'emplacement de stockage de l'index en utilisant des groupes de fichiers ou des schémas de partition pour optimiser les performances.

Placement d’index sur des groupes de fichiers ou des schémas de partitions

Lors du développement de votre stratégie de conception des index, vous devez tenir compte du placement de ces index sur les groupes de fichiers associés à la base de données. Une sélection rigoureuse du groupe de fichiers ou du schéma de partition peut améliorer les performances des requêtes.

Par défaut, les index sont stockés dans le même groupe de fichiers que la table de base sur laquelle est créé l'index. Un index cluster non partitionné et la table de base résident toujours dans le même groupe de fichiers. Toutefois, vous pouvez effectuer les opérations suivantes :

• créer des index non cluster dans un groupe de fichiers différent de celui de la table de base ou de l'index cluster ;
• partitionner des index cluster et non-cluster pour qu'ils concernent plusieurs groupes de fichiers ;
• déplacer une table d'un groupe de fichiers à un autre en supprimant l'index cluster et en spécifiant un nouveau groupe de fichiers ou un nouveau schéma de partition dans la clause MOVE TO de l'instruction DROP INDEX ou en utilisant l'instruction CREATE INDEX avec la clause DROP_EXISTING.

Créer l'index non-cluster dans un autre groupe de fichiers permet de réaliser des gains de performances si les groupes de fichiers utilisent des lecteurs physiques différents avec leurs propres contrôleurs. Les informations d'index et les données peuvent alors être lues en parallèle par plusieurs têtes de disques. Par exemple, si la Table_A du groupe de fichiers f1 et l' Index_A du groupe de fichiers f2 sont utilisés par la même requête, des gains de performances sont possibles, car les deux groupes de fichiers sont utilisés totalement sans contention. Toutefois, si Table_A elle est analysée par la requête, mais Index_A n’est pas référencée, seul le groupe f1 de fichiers est utilisé. ce qui n'apporte aucun gain de performance.

Étant donné que vous ne pouvez pas prédire le type d’accès et le moment où il se produit, il peut être préférable de répartir vos tables et index sur tous les groupes de fichiers. Ceci garantit l'accès à tous les disques, car toutes les données et tous les index sont répartis uniformément sur tous les disques, quel que soit le mode d'accès aux données. Cette approche est également plus simple pour les administrateurs système.

Partitions sur plusieurs groupes de fichiers

Vous pouvez également envisager de partitionner des index non-cluster et cluster sur disque sur plusieurs groupes de fichiers. Les index partitionnés sont partitionnés horizontalement ou par ligne, selon la fonction de partition. La fonction de partition définit le mode de mappage de chaque ligne sur un ensemble de partitions basé sur les valeurs de certaines colonnes, nommées colonnes de partition. Un schéma de partition spécifie le mappage des partitions sur un ensemble de groupe de fichiers.

Le partitionnement d'un index peut présenter les avantages suivants :

• Systèmes évolutifs permettant de gérer plus facilement les grands index. Par exemple, les systèmes OLTP peuvent mettre en œuvre des applications sensibles aux partitions qui se chargent des grands index.

• Exécution plus rapide et plus efficace des requêtes. Lorsque les requêtes accèdent à plusieurs partitions d’un index, l’optimiseur de requête peut traiter des partitions individuelles en même temps et exclure des partitions qui ne sont pas affectées par la requête.

Pour plus d’informations, consultez Tables et index partitionnés.

Instructions de conception de l’ordre de tri d’index

Quand vous définissez des index, déterminez si les données de la colonne clé d’index doivent être stockées dans l’ordre croissant ou décroissant. Croissant est la valeur par défaut et maintient la compatibilité avec les versions antérieures du moteur de base de données. La syntaxe des instructions CREATE INDEX, CREATE TABLE et ALTER TABLE permet l'application des mot clés ASC (croissant) et DESC (décroissant) à chaque colonne d'un index et d'une contrainte.

La spécification de l'ordre dans lequel les valeurs de clé sont stockées dans un index est utile lorsque les requêtes référençant la table possèdent des clauses ORDER BY qui définissent différents sens pour la ou les colonnes clés de cet index. Dans ces situations, l'index peut supprimer la nécessité d'un opérateur SORT dans le plan de requête, ce qui rend la requête plus efficace. Par exemple, les acheteurs du service d’achat Adventure Works Cycles doivent évaluer la qualité des produits qu’ils achètent auprès des fournisseurs. Les acheteurs souhaitent notamment rechercher, parmi les produits envoyés par ces fournisseurs, ceux qui affichent un degré de rejet élevé.

Comme le montre la requête suivante sur l’exemple de base de données AdventureWorks, l’extraction des données en fonction de ce critère nécessite que la colonne RejectedQty de la table Purchasing.PurchaseOrderDetail soit triée dans l’ordre décroissant (de la valeur la plus élevée à la valeur la plus faible), et que la colonne ProductID soit triée dans l’ordre croissant (de la valeur la plus faible à la valeur la plus élevée).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,
    ProductID, DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;
GO

Le plan d’exécution ci-dessous pour cette requête montre que l’optimiseur de requête a utilisé un opérateur SORT pour retourner l’ensemble de résultats dans l’ordre spécifié par la clause ORDER BY.

Si un index rowstore sur disque est créé avec les colonnes clés correspondant à celles de la clause ORDER BY de la requête, l’opérateur SORT peut être supprimé du plan de requête, ce qui rend celui-ci plus efficace.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO

Une fois la requête réexécutée, le plan d'exécution ci-dessous montre que l'opérateur SORT a été supprimé et que l'index non-cluster nouvellement créé est utilisé.

Le moteur de base de données peut se déplacer de manière égale dans les deux sens. Index défini comme (RejectedQty DESC, ProductID ASC) pouvant toujours être utilisé pour une requête dans laquelle la direction de tri des colonnes de la clause ORDER BY est inversée. Par exemple, une requête possédant la clause ORDER BY ORDER BY RejectedQty ASC, ProductID DESC peut utiliser l'index.

L’ordre de tri ne peut être spécifié que pour les colonnes clés dans l’index. L’affichage catalogue sys.index_columns et la fonction INDEXKEY_PROPERTY indiquent si une colonne d’index est stockée dans l’ordre croissant ou décroissant.

Si vous suivez la procédure avec les exemples de code de l’exemple de base de données AdventureWorks, vous pouvez supprimer IX_PurchaseOrderDetail_RejectedQty avec l’instruction Transact-SQL suivante :

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

Metadata

Utilisez ces vues de métadonnées pour voir les attributs des index. Des informations supplémentaires sur l’architecture sont incorporées dans certaines de ces vues.

Note

Pour les index columnstore, toutes les colonnes sont stockées dans les métadonnées sous forme de colonnes incorporées. L'index columnstore n'a pas de colonnes clés.

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

sys.hash_indexes (Transact-SQL)

sys.memory_optimized_tables_internal_attributes (Transact-SQL)

instructions sur la conception des index

Les index cluster trient et stockent les lignes de données de la table en fonction de leurs valeurs de clé. Il n'y a qu'un index cluster par table car les lignes de données ne peuvent être triées que dans un seul ordre. Avec quelques exceptions, chaque table doit avoir un index cluster défini sur la colonne ou les colonnes, qui offre les éléments suivants :

• utilisables pour les requêtes fréquemment utilisées ;

• Fournit un degré élevé d’unicité.

  Note

  Lorsque vous créez une contrainte PRIMARY KEY, un index unique sur la ou les colonnes est automatiquement créé. Par défaut, cet index est cluster ; toutefois, vous pouvez spécifier un index non-cluster lorsque vous créez la contrainte.

• utilisables dans les requêtes de plage.

Si l’index cluster n’est pas créé avec la UNIQUE propriété, le moteur de base de données ajoute automatiquement une colonne unique de 4 octets à la table. Lorsqu’il est nécessaire, le moteur de base de données ajoute automatiquement une valeur unique à une ligne pour rendre chaque clé unique. Cette colonne et ses valeurs sont utilisées en interne et ne peuvent pas être consultées ou accessibles par les utilisateurs.

Architecture des index cluster

Les index rowstore sont organisés sous la forme d’arbres B+. Chaque page d’un arbre B+ d’index s’appelle un nœud d’index. Le nœud supérieur d’un arbre B+ (B+ tree) est le nœud racine. Les nœuds du niveau inférieur de l'index sont appelés les nœuds feuille. Tous les niveaux d'index situés entre la racine et les nœuds feuille s'appellent des niveaux intermédiaires. Dans un index cluster, les nœuds feuille contiennent les pages de données de la table sous-jacente. Les nœuds racine et de niveau intermédiaire contiennent les pages d'index dans lesquelles se trouvent les lignes d'index. Chaque ligne d’index contient une valeur de clé et un pointeur vers une page de niveau intermédiaire dans l’arbre B+ (B+ tree) ou vers une ligne de données dans le niveau feuille de l’index. Les pages de chaque niveau de l’index sont liées dans une liste doublement liée.

Les index cluster ont une ligne dans sys.partitions, avec index_id = 1 pour chaque partition utilisée par l’index. Par défaut, un index cluster possède une seule partition. Quand un index cluster a plusieurs partitions, chacune d’elles possède une arborescence B+ qui contient ses données. Par exemple, si un index cluster a quatre partitions, il existe quatre arborescences B+, à raison d’une dans chaque partition.

Selon les types de données de l’index cluster, chaque structure d’index cluster a une ou plusieurs unités d’allocation dans lesquelles stocker et gérer les données pour une partition spécifique. Au minimum, chaque index cluster a une unité d’allocation IN_ROW_DATA par partition. L’index cluster a également une unité d’allocation LOB_DATA par partition s’il contient des colonnes objet volumineux (LOB). Il a également une unité d’allocation ROW_OVERFLOW_DATA par partition s’il contient des colonnes de longueur variable qui dépassent la limite de taille de ligne de 8 060 octets.

Les pages de la chaîne de données et les lignes qu'elles rassemblent sont organisées en fonction de la valeur de la clé d'index cluster. Toutes les insertions sont faites à l'endroit où la valeur de clé de la ligne insérée correspond parfaitement à la séquence de tri parmi les lignes existantes.

L'illustration suivante montre la structure d'un index cluster dans une partition unique.

Considérations concernant les requêtes

Avant de créer des index cluster, découvrez comment vos données sont accessibles. Envisagez l'emploi d'un index cluster pour les requêtes qui :

• Retourne une plage de valeurs à l’aide d’opérateurs tels que BETWEEN, , >, >=<, et <=.

  Dès que la ligne comportant la première valeur est trouvée à l'aide de l'index cluster, les lignes présentant les valeurs indexées suivantes sont garanties comme étant adjacentes physiquement. Par exemple, si une requête extrait des enregistrements compris dans une plage de numéros de commandes, un index cluster sur la colonne SalesOrderNumber permet de localiser rapidement la ligne qui contient le premier numéro de commande, puis d'extraire toutes les lignes successives de la table jusqu'à ce que le dernier numéro de commande soit atteint.

• retournent des jeux de résultats volumineux ;

• utilisent des clauses JOIN ; ce sont en général des colonnes clés étrangères ;

• Utilisent des clauses ORDER BY ou GROUP BY.

  Un index sur les colonnes spécifiées dans la clause ORDER BY ou GROUP BY peut supprimer la nécessité pour le moteur de base de données de trier les données, car les lignes sont déjà triées. Les requêtes présentent dès lors des performances accrues.

Remarques sur les colonnes

En général, vous devez définir la clé d'index cluster avec le moins de colonnes possible. Envisagez les colonnes présentant un ou plusieurs des attributs suivants :

• Colonnes uniques ou qui contiennent de nombreuses valeurs distinctes

  Par exemple, l'ID d'un salarié l'identifie de manière unique. Un index cluster ou une contrainte PRIMARY KEY sur la colonne EmployeeID améliore les performances des requêtes qui recherchent des informations sur les salariés en fonction de leur ID. D'une autre manière, un index cluster peut être créé sur LastName, FirstName, MiddleName , car les enregistrements de salariés sont fréquemment groupés et interrogés de cette façon et l'association de ces colonnes présente toujours un niveau élevé de différenciation.

  Astuce

  S’il n’est pas spécifié différemment, lors de la création d’une contrainte PRIMARY KEY , le moteur de base de données crée un index cluster pour prendre en charge cette contrainte. Vous pouvez utiliser un uniqueidentifier pour garantir l’unicité en tant que PRIMARY KEY, mais ce n’est pas une clé de clustering efficace. Si vous utilisez un uniqueidentifier comme PRIMARY KEY, la recommandation est de le créer en tant qu’index non-cluster et d’utiliser une autre colonne (par exemple, un IDENTITY) pour créer l’index cluster.

• Accès séquentiel des colonnes

  Par exemple, l'ID d'un produit l'identifie de manière unique dans la table Production.Product de la base de données AdventureWorks2022 . Les requêtes dans lesquelles une recherche séquentielle est spécifiée, telles que WHERE ProductID BETWEEN 980 and 999, tireront parti d'un index cluster sur ProductID. car les lignes sont stockées dans l'ordre sur cette colonne clé.

• Colonnes définies comme IDENTITY.

• Colonnes fréquemment utilisées pour trier les données extraites d'une table

  Il peut s’avérer judicieux de cluster (trier physiquement) la table de cette colonne pour économiser le coût d’une opération de tri chaque fois que la colonne est interrogée.

Les index cluster ne sont pas un bon choix pour les attributs suivants :

• Les colonnes sujettes à des modifications fréquentes.

  Cela entraîne le déplacement de la ligne entière, car le moteur de base de données doit conserver les valeurs de données d’une ligne dans l’ordre physique. Cette observation est importante dans les systèmes de traitement transactionnel à haut volume où les données sont en général éphémères.

• Les clés étendues.

  Les clés étendues sont composées de plusieurs colonnes ou plusieurs colonnes de grande taille. Les valeurs de clé de l'index cluster sont utilisées par tous les index non-cluster comme clés de recherche. Tous les index non-cluster définis sur la même table sont considérablement plus grands car leurs entrées contiennent la clé de cluster et aussi les colonnes clés définies pour cet index non-cluster.

Instructions de conception d’index non cluster

Un index non-cluster rowstore sur disque contient les valeurs de clé d’index et les localisateurs de ligne qui pointent vers l’emplacement de stockage des données de table. Vous pouvez créer plusieurs index non cluster sur une table ou une vue indexée. En règle générale, les index non cluster doivent être conçus pour améliorer les performances des requêtes fréquemment utilisées qui ne sont pas couvertes par l’index cluster.

De la même manière que vous utilisez un index dans un livre, l'optimiseur de requête recherche une valeur de données en examinant l'index non-cluster afin de trouver l'emplacement qu'occupe la valeur dans la table, puis récupère directement les données à partir de cet emplacement. C'est pour cette raison que les index non cluster constituent une solution idéale pour les requêtes à correspondance exacte ; l'index contient en effet des entrées décrivant l'emplacement exact qu'occupent dans la table les valeurs de données recherchées dans les requêtes. Par exemple, pour interroger la table HumanResources.Employee pour tous les employés qui réfèrent à un responsable spécifique, l'optimiseur de requête peut utiliser l'index non cluster IX_Employee_ManagerID; sa colonne clé est ManagerID . L'optimiseur de requête recherche rapidement toutes les entrées de l'index qui correspondent à la valeur ManagerIDspécifiée. Chaque entrée d'index pointe vers la page et la ligne exactes de la table ou de l'index cluster contenant les données correspondantes. Après avoir trouvé toutes les entrées dans l'index, l'optimiseur de requête peut accéder directement à la page et à la ligne exactes pour récupérer les données.

Architecture des index non-cluster

Les index non-cluster rowstore sur disque ont la même arborescence B+ que les index cluster, à l’exception des éléments suivants :

• Les lignes de données de la table sous-jacente ne sont pas triées et stockées dans l’ordre en fonction de leurs clés non cluster.

• Le niveau feuille d’un index non cluster n’est pas constitué de pages de données, mais de pages d’index. Les pages d’index au niveau feuille d’un index non-cluster contiennent des colonnes clés et des colonnes incluses.

Dans les lignes des index non-cluster, le localisateur est soit un pointeur vers une ligne, soit une clé d'index cluster :

• Si la table est un tas, ce qui signifie qu’elle n’a pas d’index cluster, le localisateur de lignes est un pointeur vers la ligne. Le pointeur est construit à partir de l'ID du fichier, du numéro de la page et du numéro de ligne dans la page. Le pointeur complet est appelé une ID de ligne (RID).

• Si la table a un index cluster, ou si l'index est sur une vue indexée, le localisateur de ligne est la clé d'index cluster pour la ligne.

Les localisateurs de ligne garantissent également l’unicité des lignes d’index non-cluster. Le tableau suivant décrit comment le moteur de base de données ajoute des localisateurs de lignes à des index non cluster :

Type de table	Type d’index non-cluster	Localisateur de ligne
Tas
	Non unique	RID ajouté aux colonnes clés
	Unique	RID ajouté aux colonnes incluses
Index cluster unique
	Non unique	Clés d’index cluster ajoutées aux colonnes clés
	Unique	Clés d’index cluster ajoutées aux colonnes incluses
Index cluster non unique
	Non unique	Clés d’index cluster et indicateur d’unicité (le cas échéant) ajoutés aux colonnes clés
	Unique	Clés d’index cluster et indicateur d’unicité (le cas échéant) ajoutés aux colonnes incluses

Le moteur de base de données ne stocke jamais une colonne donnée deux fois dans un index non cluster. L’ordre de clé d’index spécifié par l’utilisateur lorsqu’il crée un index non cluster est toujours respecté : toutes les colonnes de localisateur de lignes qui doivent être ajoutées à la clé d’un index non cluster sont ajoutées à la fin de la clé, en suivant les colonnes spécifiées dans la définition d’index. Les colonnes de localisateur de lignes basées sur des clés d’index cluster dans un index non-cluster peuvent être utilisées par l’optimiseur de requête, qu’elles aient été spécifiées explicitement ou non dans la définition de l’index.

Les exemples suivants montrent comment les localisateurs de lignes sont implémentés dans des index non cluster :

Index cluster	Définition d’index non-cluster	Définition d’index non-cluster avec localisateurs de ligne	Explication
Index cluster unique avec des colonnes clés (A, B, C)	Index non-cluster non unique avec des colonnes clés (B, A) et des colonnes incluses (E, G)	Colonnes clés (B, A, C) et colonnes incluses (E, G)	L’index non-cluster n’étant pas unique, le localisateur de ligne doit être présent dans les clés d’index. Les colonnes B et A du localisateur de ligne étant déjà présentes, seule la colonne c est ajoutée. La colonne c est ajoutée à la fin de la liste des colonnes clés.
Index cluster unique avec une colonne clé (A)	Index non-cluster non unique avec des colonnes clés (B, C) et une colonne incluse (A)	Colonnes clés (B, C, A)	L’index non-cluster n’étant pas unique, le localisateur de ligne est ajouté à la clé. La A colonne n’est pas déjà spécifiée en tant que colonne clé. Elle est donc ajoutée à la fin de la liste des colonnes clés. La colonne A étant maintenant dans la clé, il est inutile de la stocker en tant que colonne incluse.
Index cluster unique avec des colonnes clés (A, B)	Index non-cluster unique avec une colonne clé (C)	Colonne clé (C) et colonnes incluses (A, B)	L’index non-cluster étant unique, le localisateur de ligne est ajouté aux colonnes incluses.

Les index non cluster ont une ligne dans sys.partitions avec index_id> 1 pour chaque partition utilisée par l’index. Par défaut, un index non-cluster contient une seule partition. Quand un index non cluster a plusieurs partitions, chaque partition possède une arborescence B+ qui contient les lignes d’index correspondantes. Par exemple, si un index non cluster a quatre partitions, il existe quatre arborescences B+, une dans chaque partition.

Selon les types de données de l’index non cluster, chaque structure d’index non cluster a une ou plusieurs unités d’allocation dans lesquelles stocker et gérer les données pour une partition spécifique. Au minimum, chaque index non cluster a une unité d’allocation IN_ROW_DATA par partition qui stocke les pages d’arborescence B+ d’index. L’index non cluster a également une unité d’allocation LOB_DATA par partition s’il contient des colonnes objet volumineux (LOB). En outre, il a une unité d’allocation ROW_OVERFLOW_DATA par partition s’il contient des colonnes de longueur variable qui dépassent la limite de taille de ligne de 8 060 octets.

L'illustration suivante montre la structure d'un index non-cluster avec une seule partition.

Considérations liées à la base de données

Les caractéristiques de la base de données sont importantes lors de la conception d'index non-cluster.

• Les bases de données ou les tables dont les mises à jour sont faibles, mais qui contiennent des volumes importants de données peuvent tirer parti de nombreux index non-cluster en vue d'améliorer les performances des requêtes. Envisagez de créer des index filtrés pour les sous-ensembles de données bien définis afin d'améliorer les performances des requêtes, réduire les coûts de stockage d'index et réduire les coûts de maintenance d'index par rapport aux index non cluster de table entière.

  Les applications et bases de données d'aide à la décision contenant principalement des données en lecture seule peuvent tirer parti de nombreux index non-cluster. L’optimiseur de requête a davantage d’index à choisir pour déterminer la méthode d’accès la plus rapide, et les caractéristiques de mise à jour faible de la maintenance d’index moyenne de la base de données n’empêchent pas les performances.

• Il est important d’éviter la surindexation des applications et bases de données OLTP (traitement transactionnel en ligne) qui contiennent des tables largement mises à jour. Les index doivent en outre être réduits, c'est-à-dire contenir le moins de colonnes possible.

  La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE, car tous les index doivent être mis à jour en conséquence à mesure que les données de la table changent.

Considérations concernant les requêtes

Avant de créer des index non cluster, vous devez comprendre comment vos données sont accessibles. Il est conseillé d'utiliser un index non-cluster pour les requêtes avec les attributs suivants :

• Utilisent des clauses JOIN ou GROUP BY.

  Créez plusieurs index non-cluster sur des colonnes impliquées dans les opérations de jointure et de regroupement, ainsi qu'un index cluster sur les colonnes clés étrangère éventuelles.

• Requêtes qui ne retournent pas de jeux de résultats volumineux.

  Créez des index filtrés pour couvrir les requêtes qui retournent un sous-ensemble bien défini de lignes d'une grande table.

  Astuce

  En général, la clause WHERE de l’instruction CREATE INDEX correspond à la clause WHERE d’une requête couverte.

• Contiennent des colonnes fréquemment impliquées dans les conditions de recherche d’une requête, comme une clause WHERE, qui retournent des correspondances exactes.

  Astuce

  Examinez le rapport coût/avantages associé à l’ajout de nouveaux index. Il est parfois préférable de consolider les requêtes supplémentaires nécessaires dans un index existant. Par exemple, vous pouvez envisager d’ajouter une ou deux colonnes de niveau feuille supplémentaires à un index existant si cela permet de couvrir plusieurs requêtes critiques, plutôt que d’avoir exactement un index par requête critique.

Remarques sur les colonnes

Il est conseillé d'utiliser des colonnes qui possèdent un ou plusieurs de ces attributs :

• Couvrent la requête.

  Performances accrues lorsque l'index contient toutes les colonnes de la requête. L’optimiseur de requête peut localiser toutes les valeurs de colonne dans l’index ; Les données d’index en cluster ou de table ne sont pas accessibles, ce qui entraîne moins d’opérations d’E/S sur disque. Utilisez un index avec colonnes incluses pour ajouter des colonnes couvrantes au lieu de créer une clé d’index de grande taille.

  Si la table a un index cluster, la ou les colonnes définies dans cet index sont automatiquement ajoutées à chaque index non-cluster de la table. Ceci peut produire une requête couverte sans spécifier les colonnes de l'index cluster dans la définition de l'index non-cluster. Par exemple, si une table a un index cluster sur la colonne C, un index non-cluster non unique sur les colonnes B et A a comme colonnes de valeurs Bclés, Aet C. Pour plus d’informations, consultez l’architecture d’index non cluster.

• Un nombre élevé de valeurs distinctes, comme une combinaison de nom et prénom, si un index cluster est utilisé pour d'autres colonnes.

  S’il existe très peu de valeurs distinctes, comme seulement 1 et 0, la plupart des requêtes n’utilisent pas l’index, car une analyse de table est généralement plus efficace. Pour ce type de données, envisagez de créer un index filtré sur une valeur distincte qui se produit uniquement dans quelques lignes. Par exemple, si la plupart des valeurs sont 0, l'optimiseur de requête peut utiliser un index filtré pour les lignes de données qui contiennent 1.

Utiliser des colonnes incluses pour étendre les index non cluster

Vous pouvez étendre la fonctionnalité des index non cluster en ajoutant des colonnes non-clés au niveau feuille de l'index non cluster. L'inclusion de colonnes non-clés permet de créer des index non-cluster qui couvrent davantage de requêtes. En effet, les colonnes non-clés présentent les avantages suivants :

• Elles peuvent contenir des types de données qui ne sont pas autorisés dans les colonnes de clés d'index.

• Elles ne sont pas prises en compte par le moteur de base de données lors du calcul du nombre de colonnes clés d’index ou de taille de clé d’index.

Un index contenant des colonnes non-clés incluses peut améliorer considérablement les performances des requêtes lorsque toutes les colonnes de la requête sont incluses dans l'index en tant que colonnes clés ou non-clés. Les gains de performances sont obtenus, car l’optimiseur de requête peut localiser toutes les valeurs de colonne dans l’index ; Les données d’index en cluster ou de table ne sont pas accessibles, ce qui entraîne moins d’opérations d’E/S sur disque.

Note

Lorsqu'un index contient toutes les colonnes auxquelles la requête fait référence, on dit qu'il couvre la requête.

Alors que les colonnes clés sont stockées à tous les niveaux de l'index, les colonnes non-clés sont stockées uniquement au niveau feuille.

Utiliser des colonnes incluses pour éviter les limites de taille

Vous pouvez inclure des colonnes non-clés dans un index non cluster pour éviter de dépasser les limitations actuelles de taille d'index, établies à 16 colonnes clés au maximum et une taille de clé d'index de 900 octets au maximum. Le moteur de base de données ne prend pas en compte les colonnes non clés lors du calcul du nombre de colonnes clés d’index ou de taille de clé d’index.

Par exemple, supposons que vous voulez indexer les colonnes suivantes de la table Document :

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

Comme les types de données nchar et nvarchar nécessitent deux octets par caractère, un index qui contient ces trois colonnes dépasse de 10 octets (455 * 2) la limitation de taille de 900 octets. En utilisant la clause INCLUDE de l'instruction CREATE INDEX , la clé d'index peut être définie en tant que (Title, Revision) et FileName en tant que colonne non-clé. De cette façon, la taille de clé d’index est de 110 octets (55 * 2), et l’index contient toujours toutes les colonnes requises. L'instruction ci-dessous crée cet index.

CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);
GO

Si vous suivez la procédure avec les exemples de code, vous pouvez supprimer cet index avec l’instruction Transact-SQL suivante :

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Directives sur les index contenant des colonnes incluses

Lors de la conception d'index non-cluster contenant des colonnes incluses, tenez compte des directives suivantes :

• Les colonnes non-clés sont définies dans la clause INCLUDE de l'instruction CREATE INDEX.

• Les colonnes non-clés peuvent être définies uniquement sur les index non-cluster de tables ou de vues indexées.

• Tous les types de données sont autorisés, à l'exception de text, ntextet image.

• Les colonnes calculées déterministes et précises ou imprécises peuvent être des colonnes incluses. Pour plus d'informations, consultez Indexes on Computed Columns.

• Comme pour les colonnes clés, les colonnes calculées dérivées des types de données image, ntextet text peuvent être des colonnes non-clés (incluses) tant que le type de données de la colonne calculée est autorisé en tant que colonne d’index non-clé.

• Les noms de colonnes ne peuvent pas être spécifiés dans la liste INCLUDE et dans la liste des colonnes clés.

• Les noms de colonnes ne peuvent pas être répétés dans la liste INCLUDE.

Directives sur la taille des colonnes

• Vous devez spécifier au moins une colonne clé. Le nombre maximal de colonnes non-clés est de 1023. Il équivaut au nombre maximal de colonnes de table moins 1.

• Les colonnes de clés d'index, colonnes non-clés exclues, doivent respecter les restrictions existantes de taille d'index, à savoir 16 colonnes clés au maximum et une taille totale de clé d'index de 900 octets.

• La taille totale de toutes les colonnes non-clés est limitée uniquement par la taille des colonnes spécifiées dans la clause INCLUDE ; par exemple, les colonnes varchar(max) sont limitées à 2 Go.

Directives sur la modification des colonnes

Lors de la définition d'une colonne de table définie en tant que colonne incluse, les restrictions suivantes s'appliquent :

• Les colonnes non clés ne peuvent pas être supprimées de la table, sauf si l’index est supprimé en premier.

• Les colonnes non clés ne peuvent pas être modifiées, sauf pour effectuer les opérations suivantes :

  • modifier la possibilité de valeur NULL de la colonne de NOT NULL à NULL ;

  • augmenter la longueur des colonnes varchar, nvarcharou varbinary .

    Note

    Ces restrictions sur la modification des colonnes s'appliquent également aux colonnes de clés d'index.

Recommandations de conception

La conception d'index non-cluster doit être réalisée avec une clé d'index de grande taille, de sorte que seules les colonnes utilisées pour la recherche sont les colonnes clés. Toutes les autres colonnes qui couvrent la requête doivent être des colonnes non-clés incluses. De cette façon, vous avez toutes les colonnes nécessaires pour couvrir la requête, mais la clé d’index elle-même est petite et efficace.

Par exemple, supposons que vous voulez concevoir un index qui couvre la requête ci-dessous.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';
GO

Pour couvrir la requête, chaque colonne doit être définie dans l'index. Même si vous pouviez définir toutes les colonnes en tant que colonnes clés, la taille de clé serait 334 octets. Comme la seule colonne vraiment utilisée comme critère de recherche est la colonne PostalCode , dont la longueur vaut 30 octets, une meilleure conception d'index définirait PostalCode comme colonne clé et inclurait toutes les autres colonnes comme colonnes non-clés.

L'instruction suivante crée un index contenant des colonnes incluses pour couvrir la requête.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Pour vérifier que l’index couvre la requête, créez l’index, puis affichez le plan d’exécution estimé.

Si le plan d’exécution affiche uniquement un opérateur SELECT et un opérateur Index Seek pour l’index IX_Address_PostalCode, la requête a été « couverte » par l’index.

Vous pouvez supprimer l’index avec l’instruction suivante :

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

Considérations relatives aux performances

Évitez d'ajouter des colonnes superflues. L'ajout de trop nombreuses colonnes d'index, clés et non-clés, peut avoir les conséquences suivantes sur les performances :

• Moins de lignes d’index s’ajustent sur une page. Ceci pourrait augmenter les E/S et réduire l'efficacité de la mémoire cache.

• Plus d’espace disque est nécessaire pour stocker l’index. En particulier, l’ajout des types de données varchar(max), nvarchar(max), varbinary(max)et xml en tant que colonnes d’index non-clés peut accroître considérablement l’espace disque nécessaire. En effet, les valeurs des colonnes sont copiées dans le niveau feuille de l'index. Par conséquent, elles résident à la fois dans l'index et dans la table de base.

• La maintenance d'un index peut accroître la durée nécessaire pour effectuer des modifications, des insertions, des mises à jour ou des suppressions à la table sous-jacente ou à la vue indexée.

Vous devez déterminer si les gains de performances des requêtes l’emportent sur les performances lors de la modification des données et dans les besoins supplémentaires en espace disque.

Instructions de conception d’index uniques

Un index unique garantit que la clé d'index ne contient aucune valeur dupliquée et que, par conséquent, chaque ligne de la table est unique d'une certaine manière. Spécifier un index unique n'a de sens que si l'unicité est une caractéristique des données elles-mêmes. Par exemple, si vous souhaitez que les valeurs de la colonne NationalIDNumber de la table HumanResources.Employee soient uniques, lorsque la clé primaire est EmployeeID, créez une contrainte UNIQUE sur la colonne NationalIDNumber . Si l’utilisateur tente d’entrer la même valeur dans cette colonne pour plusieurs employés, un message d’erreur s’affiche et la valeur dupliquée n’est pas entrée.

Lorsque vous utilisez un index unique multicolonne, celui-ci garantit que chaque combinaison de valeurs dans la clé d'index est unique. Par exemple, si un index unique est créé sur une combinaison des colonnes LastName, FirstNameet MiddleName , deux lignes de la table ne peuvent pas posséder la même combinaison de valeurs pour ces colonnes.

Tant les index cluster que les index non-cluster peuvent être uniques. Si les données contenues dans la colonne sont uniques, vous pouvez créer à la fois un index cluster unique et plusieurs index non-cluster uniques sur la même table.

Les index uniques présentent les avantages suivants :

• L'intégrité des données des colonnes définies est garantie.

• L'optimiseur de requête dispose d'informations utiles supplémentaires.

Lorsque vous créez une contrainte PRIMARY KEY ou UNIQUE, vous créez automatiquement un index unique sur les colonnes spécifiées. Il n'y a pas de différences significatives entre la création d'une contrainte UNIQUE et la création d'un index unique indépendant d'une contrainte. La validation des données se produit de la même manière et l’optimiseur de requête ne fait pas la distinction entre un index unique créé par une contrainte ou créé manuellement. Toutefois, vous devez créer une contrainte UNIQUE ou PRIMARY KEY sur la colonne lorsque votre objectif est de préserver l'intégrité des données. En procédant ainsi, l’objectif de l’index sera clair.

Considérations

• Un index unique, une contrainte UNIQUE ou une contrainte PRIMARY KEY ne peut pas être créé si des valeurs de clé dupliquées existent dans les données.

• Si les données sont uniques et que l'unicité doit être assurée, la création d'un index unique au lieu d'un index non unique sur la même combinaison de colonnes fournit des informations supplémentaires à l'optimiseur de requête, qui peut générer des plans d'exécution plus efficaces. La création d'un index unique (si possible en créant une contrainte UNIQUE) est recommandée dans ce cas.

• Un index non cluster unique peut contenir des colonnes non-clés incluses. Pour plus d'informations, consultez Index avec colonnes incluses.

Instructions de conception d’index filtrées

Un index filtré est un index non cluster optimisé, convenant tout particulièrement aux requêtes qui effectuent des sélections dans un sous-ensemble de données bien défini. Il utilise un prédicat de filtre pour indexer une partie des lignes de la table. Un index filtré bien conçu peut améliorer les performances des requêtes, réduire les coûts de maintenance des index et réduire les coûts de stockage des index par rapport aux index de table entière.

Les index filtrés peuvent présenter les avantages suivants par rapport aux index de table entière :

• Meilleures performances des requêtes et qualité de plan améliorée

  Un index filtré bien conçu améliore les performances des requêtes et la qualité du plan d'exécution car il est plus petit qu'un index non cluster de table entière et contient des statistiques filtrées. Les statistiques filtrées sont plus précises que les statistiques de table entière car elles couvrent uniquement les lignes de l'index filtré.

• Coûts réduits de maintenance des index

  La maintenance d'un index intervient uniquement lorsque les instructions de langage de manipulation de données (DML) affectent les données de l'index. Un index filtré réduit les coûts de maintenance des index par rapport à un index non cluster de table entière car il est plus petit et est demande une maintenance uniquement lorsque les données de l'index sont affectées. Il est possible d'avoir un grand nombre d'index filtrés, notamment s'ils contiennent des données qui sont rarement affectées. De la même façon, si un index filtré contient uniquement les données fréquemment affectées, la plus petite taille de l'index réduit le coût de la mise à jour des statistiques.

• Coûts réduits de stockage des index

  La création d’un index filtré peut réduire le stockage sur disque pour les index non cluster lorsqu’un index de table complète n’est pas nécessaire. Vous pouvez remplacer un index non cluster de table entière par plusieurs index filtrés sans augmenter considérablement le stockage nécessaire.

Les index filtrés sont utiles lorsque les colonnes contiennent des sous-ensembles bien définis de données qui sont référencés par des requêtes dans des instructions SELECT. Voici quelques exemples :

• Colonnes éparses qui contiennent uniquement quelques valeurs non NULL.

• Colonnes hétérogènes qui contiennent des catégories de données.

• Colonnes qui contiennent des plages de valeurs, telles que des montants en devise, des heures et des dates.

• Partitions de table définies par une logique de comparaison simple pour les valeurs de colonne.

La réduction des coûts de maintenance pour les index filtrés est plus particulièrement notable lorsque le nombre de lignes de l'index est petit comparé à un index de table entière. Si l'index filtré inclut la plupart des lignes de la table, son coût de maintenance risque d'être plus élevé que celui d'un index de table entière. Dans ce cas, vous devez utiliser un index de table entière à la place d'un index filtré.

Les index filtrés sont définis sur une seule table et ne prennent en charge que les opérateurs de comparaison simples. Si vous avez besoin d'une expression de filtre qui référence plusieurs tables ou présente une logique complexe, vous devez créer une vue.

Remarques relatives à la conception

Pour concevoir des index filtrés efficaces, il est important de comprendre les requêtes utilisées par votre application et leurs relations avec les sous-ensembles de données. Les colonnes contenant principalement des valeurs NULL, les colonnes contenant des catégories hétérogènes de valeurs et les colonnes contenant des plages de valeurs distinctes sont autant d'exemples de données avec des sous-ensembles bien définis. Les considérations suivantes relatives à la conception présentent divers scénarios dans lesquels un index filtré peut présenter des avantages par rapport à des index de table entière.

Astuce

Pour définir des index columnstore non-cluster, vous pouvez utiliser une condition filtrée. Pour minimiser l’impact sur les performances de l’ajout d’un index columnstore sur une table OLTP, utilisez une condition filtrée pour créer un index columnstore non cluster uniquement sur les données brutes de votre charge de travail opérationnelle.

Index filtrés pour des sous-ensembles de données

Lorsqu’une colonne comporte uniquement quelques valeurs pertinentes pour les requêtes, vous pouvez créer un index filtré sur le sous-ensemble de valeurs. Ainsi, lorsque les valeurs d'une colonne sont principalement NULL et que la requête effectue uniquement des sélections dans les valeurs non NULL, vous pouvez créer un index filtré pour les lignes de données non NULL. L’index résultant est plus petit et coûte moins de maintenance qu’un index non cluster complet défini sur les mêmes colonnes clés.

Par exemple, l’exemple de base de données AdventureWorks contient une table Production.BillOfMaterials de 2 679 lignes. Seules 199 lignes de la colonne EndDate contiennent une valeur non NULL ; les 2480 autres contiennent des valeurs NULL. L'index filtré suivant couvre les requêtes qui retournent les colonnes définies dans l'index et qui sélectionnent uniquement les lignes avec une valeur non NULL pour EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

L'index filtré FIBillOfMaterialsWithEndDate est valide pour la requête suivante. Vous pouvez afficher le plan d’exécution estimé pour déterminer si l’optimiseur de requête a utilisé l’index filtré.

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '20080101';
GO

Pour plus d'informations sur la création d'index filtrés et la définition de l'expression de prédicat d'index filtré, consultez Create Filtered Indexes.

Index filtrés pour des données hétérogènes

Lorsqu'une table contient des lignes des données hétérogènes, vous pouvez créer un index filtré pour une ou plusieurs catégories de données.

Par exemple, chacun des produits répertoriés dans la table Production.Product est affecté à un ProductSubcategoryID, qui est à son tour associé à une catégorie de produits (Bikes, Components, Clothing ou Accessories). Ces catégories sont hétérogènes, car leurs valeurs de colonne dans la Production.Product table ne sont pas étroitement corrélées. Par exemple, les colonnes Color, ReorderPoint, ListPrice, Weight, Classet Style ont des caractéristiques uniques pour chaque catégorie de produit. Supposons qu’il existe des requêtes fréquentes pour les accessoires, qui ont des sous-catégories comprises entre 27 et 36 inclus. Améliorez les performances des requêtes portant sur Accessories en créant un index filtré sur les sous-catégories de la catégorie Accessories, tel que l'illustre l'exemple suivant.

CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice)
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

L’index FIProductAccessories filtré couvre la requête suivante, car les résultats de la requête sont contenus dans l’index et le plan de requête n’inclut pas de recherche de table de base. Par exemple, l'expression de prédicat de requête ProductSubcategoryID = 33 est un sous-ensemble du prédicat d'index filtré ProductSubcategoryID >= 27 et ProductSubcategoryID <= 36, les colonnes ProductSubcategoryID et ListPrice dans le prédicat de requête sont toutes deux des colonnes clés dans l'index et le nom est stocké au niveau feuille de l'index en tant que colonne incluse.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00;
GO

Colonnes clés

Il est recommandé d'inclure un petit nombre de colonnes clés ou incluses dans une définition d'index filtré, et d'incorporer uniquement les colonnes qui sont nécessaires à l'optimiseur de requête pour choisir l'index filtré pour le plan d'exécution de la requête. L’optimiseur de requête peut choisir un index filtré pour la requête, qu’il le fasse ou ne couvre pas la requête. Toutefois, l'optimiseur de requête choisira plus probablement un index filtré s'il couvre la requête.

Dans certains cas, un index filtré couvre la requête sans inclure les colonnes de l'expression d'index filtré en tant que colonnes clés ou incluses dans la définition de l'index filtré. Les règles suivantes expliquent dans quels cas une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré. Les exemples font référence à l'index filtré FIBillOfMaterialsWithEndDate qui a été créé précédemment.

Une colonne de l’expression d’index filtrée n’a pas besoin d’être une clé ou une colonne incluse dans la définition d’index filtrée si l’expression d’index filtrée équivaut au prédicat de requête et que la requête ne retourne pas la colonne dans l’expression d’index filtrée avec les résultats de la requête. Par exemple, FIBillOfMaterialsWithEndDate couvre la requête suivante, car le prédicat de requête est équivalent à l’expression de filtre et EndDate n’est pas retourné avec les résultats de la requête. FIBillOfMaterialsWithEndDate n’a pas besoin EndDate d’une clé ou d’une colonne incluse dans la définition d’index filtrée.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Une colonne dans l’expression d’index filtrée doit être une clé ou une colonne incluse dans la définition d’index filtrée si le prédicat de requête utilise la colonne dans une comparaison qui n’est pas équivalente à l’expression d’index filtrée. Par exemple, l'index filtré FIBillOfMaterialsWithEndDate est valide pour la requête suivante car il sélectionne un sous-ensemble de lignes dans l'index filtré. Toutefois, elle ne couvre pas la requête suivante, car EndDate elle est utilisée dans la comparaison EndDate > '20040101', ce qui n’est pas équivalent à l’expression d’index filtrée. Le processeur de requêtes ne peut pas exécuter cette requête sans rechercher les valeurs de EndDate. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré si la colonne se trouve dans le jeu de résultats de la requête. Par exemple, FIBillOfMaterialsWithEndDate ne couvre pas la requête suivante, car elle retourne la EndDate colonne dans les résultats de la requête. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

La clé d’index cluster de la table n’a pas besoin d’être une clé ou une colonne incluse dans la définition d’index filtrée. La clé de l'index cluster est automatiquement incluse dans tous les index non cluster, y compris les index filtrés.

Pour supprimer les index FIBillOfMaterialsWithEndDate et FIProductAccessories, exécutez les instructions suivantes :

DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
DROP INDEX FIProductAccessories
    ON Production.Product;
GO

Opérateurs de conversion de données dans le prédicat du filtre

Si l’opérateur de comparaison spécifié dans l’expression d’index filtré de l’index filtré entraîne une conversion implicite ou explicite de données, une erreur se produit si la conversion se produit sur le côté gauche d’un opérateur de comparaison. Une solution consiste à écrire l'expression d'index filtré avec l'opérateur de conversion de données (CAST ou CONVERT) à droite de l'opérateur de comparaison.

L'exemple suivant crée une table avec différents types de données.

CREATE TABLE dbo.TestTable (a int, b varbinary(4));
GO

Dans la définition d'index filtré suivante, la colonne b est implicitement convertie en type de données integer afin de la comparer à la constante 1. Le message d’erreur 10611 est alors généré car la conversion se produit à gauche de l’opérateur dans le prédicat filtré.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

La solution consiste à convertir la constante qui se trouve à droite de manière à ce que son type soit identique à celui de la colonne b, comme dans l’exemple suivant :

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Le fait de déplacer la conversion de données de la gauche vers la droite d'un opérateur de comparaison peut modifier la signification de la conversion. Dans l'exemple ci-dessus, lorsque l'opérateur CONVERT a été ajouté à droite, la comparaison de type integer est devenue une comparaison de type varbinary .

Supprimez les objets créés dans cet exemple en exécutant l’instruction suivante :

DROP TABLE TestTable;
GO

Architecture des index columnstore

Un index columnstore est une technologie permettant de stocker, de récupérer et de gérer les données suivant un format de données en colonnes, appelé columnstore. Pour plus d’informations, consultez la vue d’ensemble des index columnstore.

Pour plus d’informations sur la version et pour découvrir les nouveautés, visitez les index Columnstore - Nouveautés.

Connaître ces principes de base facilite la compréhension d’autres articles columnstore qui expliquent comment les utiliser efficacement.

Le stockage de données utilise la compression de columnstore et de rowstore

Quand nous parlons des index columnstore, nous utilisons les termes rowstore et columnstore pour indiquer clairement le format du stockage de données. Les index columnstore utilisent les deux types de stockage.

• Un columnstore représente des données qui sont organisées logiquement sous la forme d’une table avec des lignes et des colonnes, et stockées physiquement dans un format de données selon les colonnes.

  Un index columnstore stocke physiquement la plupart des données au format columnstore. Au format columnstore, les données sont compressées et décompressées sous la forme de colonnes. Il n’est pas nécessaire de décompresser d’autres valeurs dans chaque ligne qui ne sont pas demandées par la requête. Cela permet d’analyser rapidement une colonne entière d’une table volumineuse.

• Un rowstore représente des données qui sont organisées logiquement sous la forme d’une table avec des lignes et des colonnes, puis stockées physiquement dans un format de données selon les lignes. Il s’agit de la méthode standard de stockage des données de table relationnelles, comme un segment de mémoire ou un index cluster B+ (B+ tree).

  Un index columnstore stocke également physiquement des lignes dans un format rowstore appelé « deltastore ». Le deltastore, également appelé rowgroups delta, est une place d’attente pour les lignes qui sont trop peu nombreuses pour se qualifier pour la compression dans le columnstore. Chaque rowgroup delta est implémenté comme un index cluster B+.

• Le deltastore est un espace de stockage pour les lignes qui sont en trop petit nombre pour être compressées dans le columnstore. Le deltastore stocke les lignes au format rowstore.

Pour plus d’informations sur les termes et concepts columnstore, consultez les index Columnstore : Vue d’ensemble.

Les opérations sont effectuées sur des rowgroups et des segments de colonne

L’index columnstore regroupe des lignes en unités gérables. Chacune de ces unités est appelée rowgroup. Pour des performances optimales, le nombre de lignes dans le rowgroup doit être suffisamment important pour améliorer le taux de compression et suffisamment petit pour tirer parti des opérations en mémoire.

Par exemple, l’index columnstore effectue les opérations suivantes sur des rowgroups :

• Compresse les rowgroups dans le columnstore. La compression est effectuée sur chaque segment de colonne d’un rowgroup.
• Fusionne les rowgroups lors d’une opération ALTER INDEX ... REORGANIZE, y compris la suppression des données supprimées.
• Crée des rowgroups lors d’une opération ALTER INDEX ... REBUILD.
• Génère des rapports sur l’intégrité et la fragmentation des rowgroups dans des vues de gestion dynamique (DMV).

Le deltastore se compose d’un ou plusieurs rowgroups appelés rowgroups delta. Chaque rowgroup delta est un index cluster B+ qui stocke de petites charges en masse et effectue une insertion jusqu’à ce que le rowgroup contienne 1 048 576 lignes. À ce moment-là, un processus nommé moteur de tuple compresse automatiquement le rowgroup fermé dans le columnstore.

Pour plus d’informations sur les états de rowgroup, consultez sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Astuce

Avoir un trop grand nombre de rowgroups de petite taille réduit la qualité de l’index columnstore. Une opération de réorganisation fusionne des rowgroups plus petits, suite à une stratégie de seuil interne qui détermine comment supprimer les lignes supprimées et combiner les rowgroups compressés. Après une fusion, la qualité de l’index doit être améliorée.

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

Chaque colonne a certaines de ses valeurs dans chaque rowgroup. Ces valeurs sont appelées segments de colonne. Chaque rowgroup contient un segment de colonne pour chaque colonne dans la table. Chaque colonne a un seul segment de colonne dans chaque rowgroup.

Quand l’index columnstore compresse un rowgroup, il compresse chaque segment de colonne séparément. Pour décompresser la totalité d’une colonne, l’index columnstore doit simplement décompresser un segment de colonne dans chaque rowgroup.

Pour plus d’informations sur les termes et concepts columnstore, consultez les index Columnstore : Vue d’ensemble.

Les petits chargements et les petites insertions sont placés dans le deltastore

Un index columnstore améliore la compression columnstore et les performances en compressant au moins 102 400 lignes à la fois dans l’index columnstore. Pour compresser des lignes en masse, l’index columnstore accumule les petits chargements et les petites insertions dans le deltastore. Les opérations deltastore sont effectuées en coulisse. Pour retourner des résultats de requête corrects, l'index columnstore cluster associe les résultats de columnstore et de deltastore.

Les lignes sont placées dans le deltastore quand elles sont :

• Insérées avec l’instruction INSERT INTO ... VALUES.
• À la fin d’un chargement en masse et que leur nombre est inférieur à 102 400.
• Mises à jour. Chaque mise à jour correspond à une suppression et une insertion.

Le deltastore stocke également une liste des ID des lignes supprimées qui ont été marquées comme supprimées mais qui n’ont pas encore été supprimées physiquement de l’index columnstore.

Pour plus d’informations sur les termes et concepts columnstore, consultez les index Columnstore : Vue d’ensemble.

Quand les rowgroups delta sont pleins, ils sont compressés dans le columnstore.

Les index columnstore cluster collectent jusqu’à 1 048 576 lignes dans chaque rowgroup delta avant de compresser le rowgroup dans le columnstore. Cela améliore la compression de l’index columnstore. Lorsqu’un rowgroup delta atteint le nombre maximal de lignes, il passe de l’état OPEN à l’état CLOSED. Un processus en arrière-plan nommé moteur de tuple vérifie les groupes de lignes fermés. Lorsqu'il trouve un rowgroup fermé, il le compresse et le stocke dans le columnstore.

Quand un rowgroup delta a été compressé, le rowgroup delta existant passe à l’état TOMBSTONE pour être supprimé ultérieurement par le moteur de tuple lorsqu’il n’y a aucune référence à celui-ci. Le nouveau rowgroup compressé est marqué comme COMPRESSED.

Pour plus d’informations sur les états de rowgroup, consultez sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Vous pouvez placer de force des rowgroups delta dans le columnstore à l’aide de l’instruction ALTER INDEX pour reconstruire ou réorganiser l’index. S’il existe une pression de mémoire pendant la compression, l’index columnstore peut réduire le nombre de lignes dans le rowgroup compressé.

Pour plus d’informations sur les termes et concepts columnstore, consultez les index Columnstore : Vue d’ensemble.

Chaque partition de table a ses propres rowgroups et rowgroups delta

Le concept de partitionnement est identique dans un index cluster, un segment de mémoire et un index columnstore. Le partitionnement d’une table divise la table en groupes de lignes plus petits en fonction d’une plage de valeurs de colonne. Il est souvent utilisé pour la gestion des données. Par exemple, vous créez une partition pour chaque année de données, puis vous utilisez le basculement de partition pour archiver des données dans un stockage moins coûteux. Le basculement de partition fonctionne sur les index columnstore et facilite le déplacement d’une partition de données vers un autre emplacement.

Les rowgroups sont toujours définis dans une partition de table. Quand un index columnstore est partitionné, chaque partition possède ses propres rowgroups et rowgroups delta compressés.

Astuce

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.

Chaque partition peut posséder plusieurs rowgroups delta

Chaque partition peut posséder plusieurs rowgroups delta. Quand l’index columnstore doit ajouter des données à un rowgroup delta et que ce dernier est verrouillé, l’index columnstore tente d’obtenir un verrou sur un autre rowgroup delta. Si aucun rowgroup delta n’est disponible, l’index columnstore en crée un. Par exemple, une table comportant 10 partitions peut facilement avoir au moins 20 rowgroups delta.

Combiner des index columnstore et rowstore sur la même table

Un index non cluster contient une copie de tout ou partie des lignes et colonnes de la table sous-jacente. L’index est défini comme une ou plusieurs colonnes de la table et a une condition facultative qui filtre les lignes.

Vous pouvez créer un index columnstore non-cluster pouvant être mis à jour sur une table rowstore. L’index columnstore stocke une copie des données, afin que vous n’ayez pas besoin de stockage supplémentaire. Toutefois, les données de l’index columnstore sont compressées à une taille plus petite que ce que nécessite la table rowstore. Ce faisant, vous pouvez exécuter l’analytique sur l’index columnstore et les transactions sur l’index rowstore en même temps. La banque des colonnes (columnstore) est mise à jour lors de la modification des données de la table rowstore. Les deux index utilisent donc les mêmes données.

Vous pouvez avoir un ou plusieurs index rowstore non-cluster sur un index columnstore. Ce faisant, vous pouvez effectuer des recherches de tables efficaces sur le columnstore sous-jacent. D’autres options sont également disponibles. Par exemple, vous pouvez appliquer une contrainte de clé primaire à l’aide d’une contrainte UNIQUE sur la table rowstore. Étant donné qu’une valeur non unique ne parvient pas à être insérée dans la table rowstore, le moteur de base de données ne peut pas insérer la valeur dans le columnstore.

Considérations relatives aux performances

• Pour définir des index columnstore non-cluster, vous pouvez utiliser une condition filtrée. Pour minimiser l’impact sur les performances de l’ajout d’un index columnstore sur une table OLTP, utilisez une condition filtrée pour créer un index columnstore non cluster uniquement sur les données brutes de votre charge de travail opérationnelle.

• Une table en mémoire peut avoir un index columnstore. Vous pouvez le créer lorsque la table est créée ou l’ajouter ultérieurement avec ALTER TABLE (Transact-SQL). Avant SQL Server 2016 (13.x), seule une table sur disque peut avoir un index columnstore.

Pour plus d’informations, consultez Index columnstore - Performances des requêtes.

Guide de conception

• Une table rowstore peut avoir un index columnstore non cluster actualisable. Avant SQL Server 2014 (12.x), l’index columnstore non cluster était en lecture seule.

Pour plus d’informations, consultez Index columnstore - Guide de conception.

Recommandations en matière de conception d’index de hachage

Chaque table à mémoire optimisée doit avoir au moins un index, car l’index permet de lier les lignes de la table entre elles. Sur une table optimisée en mémoire, chaque index est également optimisé en mémoire. Les index de hachage sont l’un des types d’index possibles dans une table optimisée en mémoire. Pour plus d’informations, consultez Index pour les tables à mémoire optimisée.

S’applique à : SQL Server, Azure SQL Database et Azure SQL Managed Instance.

Architecture des index de hachage

Un index de hachage se compose d’un tableau de pointeurs, chaque élément du tableau constituant un compartiment de hachage.

• Chaque compartiment comprend 8 octets, qui sont utilisés pour stocker l’adresse mémoire d’une liste de liens d’entrées d’index.
• Chaque entrée correspond à une valeur pour une clé d’index, plus l’adresse de la ligne associée dans la table mémoire optimisée sous-jacente.
• Chaque entrée pointe vers l’entrée suivante dans une liste de liens d’entrées, toutes chaînées au compartiment actuel.

Le nombre de compartiments doit être spécifié au moment de la définition de l’index :

• Plus le rapport entre les compartiments et les lignes de table ou les valeurs distinctes est faible, plus la liste moyenne de liens de compartiments sera longue.
• Les listes de liens courtes sont plus rapides que les listes de liens longues.
• Un index de hachage peut contenir 1 073 741 824 compartiments au maximum.

Astuce

Pour déterminer le droit BUCKET_COUNT de vos données, consultez Configurer le nombre de compartiments d’index de hachage.

La fonction de hachage est appliquée aux colonnes de clés d’index. Son résultat détermine le compartiment auquel ces clés sont mappées. Chaque compartiment a un pointeur vers les lignes dont les valeurs de clés hachées sont mappées à ce compartiment.

La fonction de hachage utilisée pour les index de hachage présente les caractéristiques suivantes :

• Le moteur de base de données a une fonction de hachage utilisée pour tous les index de hachage.
• La fonction de hachage est déterministe. La même valeur de clé d’entrée est toujours mappée au même compartiment dans l’index de hachage.
• Plusieurs clés d'index peuvent être mappées au même compartiment de hachage.
• La fonction de hachage est équilibrée, ce qui signifie que la distribution des valeurs de clé d’index sur les compartiments de hachage est généralement une distribution de type Poisson ou courbe en cloche, et pas une distribution linéaire plate.
• La distribution de Poisson n’est pas une distribution même. Les valeurs de clé d’index ne sont pas distribuées uniformément dans les compartiments de hachage.
• Si deux clés d’index sont mappées au même compartiment de hachage, il y a une collision de hachage. Un grand nombre de collisions de hachage peut avoir un impact sur les performances des opérations de lecture. Un objectif réaliste est de 30 % des compartiments contenant deux valeurs clés différentes.

L’interaction entre l’index de hachage et les compartiments est résumée dans l’image ci-dessous.

Configurer le nombre de compartiments d’index de hachage

Le nombre de compartiments d’index de hachage est spécifié au moment de la création de l’index et peut être modifié à l’aide de la syntaxe ALTER TABLE...ALTER INDEX REBUILD.

Dans la plupart des cas, le nombre de compartiments doit être compris entre 1 et 2 fois le nombre de valeurs distinctes dans la clé d’index.
Vous ne pouvez pas toujours prédire le nombre exact de valeurs qu’une clé d’index donnée a ou aura. Les performances sont généralement bonnes si la BUCKET_COUNT valeur est comprise dans un délai de 10 fois le nombre réel de valeurs de clé et si la surestimation est généralement meilleure que la sous-estimation.

Un trop petit nombre de compartiments présente les inconvénients suivants :

• Davantage de collisions de hachage de valeurs de clés distinctes.
• Chaque valeur distincte est forcée de partager le même compartiment avec une autre valeur distincte.
• La longueur de chaîne moyenne par compartiment augmente.
• Plus la chaîne de compartiment est longue, plus les recherches d’égalité sont lentes dans l’index.

Un trop grand nombre de compartiments présente les inconvénients suivants :

• Un nombre de compartiments trop élevé peut entraîner plus de compartiments vides.
• Des compartiments vides affectent les performances des analyses d’index complètes. Si celles-ci sont effectuées régulièrement, envisagez de choisir un nombre de compartiments proche du nombre de valeurs de clés distinctes.
• Des compartiments vides utilisent de la mémoire, même si chaque compartiment utilise seulement 8 octets.

Note

L’ajout de nouveaux compartiments ne fait rien pour réduire le chaînage groupé des entrées qui partagent une valeur en double. Le taux de duplication de valeurs est utilisé pour déterminer si un hachage constitue le type d’index approprié, et non pour calculer le nombre de compartiments.

Considérations relatives aux performances

Les performances d’un index de hachage sont :

• excellentes quand le prédicat dans la clause WHERE spécifie une valeur exacte pour chaque colonne dans la clé d’index de hachage ; Un index de hachage rétablit une analyse en fonction d'un prédicat d'inégalité.
• médiocres quand le prédicat dans la clause WHERE recherche une plage de valeurs dans la clé d’index ;
• Médiocre lorsque le prédicat de la WHERE clause stipule une valeur spécifique pour la première colonne d’une clé d’index de hachage de deux colonnes, mais ne spécifie pas de valeur pour d’autres colonnes de la clé.

Astuce

Le prédicat doit inclure toutes les colonnes dans la clé d'index de hachage. L'index de hachage nécessite une clé (pour hacher) pour rechercher dans l'index.
Si une clé d’index se compose de deux colonnes et que la WHERE clause fournit uniquement la première colonne, le moteur de base de données n’a pas de clé complète pour le hachage. Cela génère un plan de requête d'analyse d'index.

Si l’index de hachage utilisé a un nombre de clés d’index uniques 100 fois (ou plus) supérieur au nombre de lignes, vous devez augmenter le nombre de compartiments pour éviter la création de chaînes de lignes trop longues ou bien utiliser un index non-cluster à la place.

Considérations relatives à la déclaration

Un index de hachage peut exister uniquement sur une table optimisée en mémoire. Il n’existe pas sur une table sur disque.

Un index de hachage peut être déclaré comme :

• UNIQUE, ou être défini comme non unique par défaut.
• NONCLUSTERED, qui est la valeur par défaut.

Le code suivant est un exemple de la syntaxe appropriée pour créer un index de hachage, en dehors de l’instruction CREATE TABLE :

ALTER TABLE MyTable_memop
ADD INDEX ix_hash_Column2 UNIQUE
HASH (Column2) WITH (BUCKET_COUNT = 64);

Versions de lignes et garbage collection

Dans une table à mémoire optimisée, quand une ligne est modifiée par une instruction UPDATE, la table crée une version mise à jour de la ligne. Lors de la transaction de mise à jour, d’autres sessions peuvent être en mesure de lire l’ancienne version de la ligne et d’éviter ainsi le ralentissement des performances associé à un verrou de ligne.

L’index de hachage peut également avoir différentes versions de ses entrées pour prendre en charge la mise à jour.

Par la suite, quand les anciennes versions ne sont plus nécessaires, un thread garbage collection (GC) traverse les compartiments et leurs listes de liens pour nettoyer les anciennes entrées. Le thread GC fonctionne mieux si les longueurs de chaîne des listes de liens sont courtes. Pour plus d’informations, consultez Garbage collection de l’OLTP en mémoire.

Recommandations en matière de conception d’index non cluster optimisées en mémoire

Les index non-cluster sont l’un des types d’index possibles dans une table à mémoire optimisée. Pour plus d’informations, consultez Index pour les tables à mémoire optimisée.

S’applique à : SQL Server, Azure SQL Database et Azure SQL Managed Instance.

Architecture des index non-cluster en mémoire

Les index non cluster en mémoire sont implémentés à l’aide d’une structure de données appelée arbre Bw (Bw-tree). Cette structure a initialement été conçue et décrite par Microsoft Research en 2011. Un arbre Bw (Bw-tree) est une variante sans verrous d’un arbre B (B-tree). Pour plus d’informations, consultez l’article Arbre Bw (Bw-tree) : arbre B (B-tree) pour les nouvelles plateformes matérielles.

Sur un plan très général, un arbre Bw (Bw-tree) peut être considéré comme un mappage de pages organisées par ID de page (PidMap), un moyen d’allouer et de réutiliser des ID de page (PidAlloc) et un ensemble de pages liées entre elles dans le mappage de pages. Ces trois sous-composants principaux constituent la structure interne de base d’un arbre Bw (Bw-tree).

La structure est similaire à un arbre B (B-tree) standard, car chaque page a un ensemble de valeurs de clés ordonnées, l’index a plusieurs niveaux qui pointent vers un niveau inférieur et les niveaux feuille pointent vers une ligne de données. Il y a toutefois quelques différences.

À l’instar des index de hachage, plusieurs lignes de données peuvent être liées entre elles (versions). Les pointeurs de page entre les niveaux sont des ID de page logiques qui indiquent une position dans une table de mappage de pages, laquelle contient l’adresse physique de chaque page.

Il n’y a aucune mise à jour sur place des pages d’index. De nouvelles pages delta sont introduites dans ce but.

• Aucun verrouillage n’est nécessaire pour les mises à jour de pages.
• Les pages d’index ne sont pas de taille fixe.

La valeur de clé dans chaque page de niveau non-sourd représentée est la valeur la plus élevée que l’enfant qu’il pointe vers contient et chaque ligne contient également cet ID de page logique de page. Dans les pages de niveau feuille, avec la valeur de clé, elle contient l’adresse physique de la ligne de données.

Les recherches de points sont similaires aux arborescences B, sauf que, étant donné que les pages sont liées dans une seule direction, le moteur de base de données SQL Server suit les pointeurs de page droit, où chaque page non-sourde a la valeur la plus élevée de son enfant, plutôt que la valeur la plus basse comme dans une arborescence B.

Si une page de niveau feuille doit changer, le moteur de base de données SQL Server ne modifie pas la page elle-même. Au lieu de cela, le moteur de base de données SQL Server crée un enregistrement delta qui décrit la modification et l’ajoute à la page précédente. Ensuite, il change également l’adresse de cette page précédente dans la table de mappage des pages par l’adresse de l’enregistrement delta qui devient alors l’adresse physique de cette page.

Il existe trois opérations différentes qui peuvent être nécessaires pour gérer la structure d’une arborescence Bw : consolidation, fractionnement et fusion.

Consolidation des enregistrements delta

La présence d’une longue chaîne d’enregistrements delta peut dégrader les performances de recherche, car cela peut signifier que de longues chaînes doivent être parcourues lors de la recherche à l’aide d’un index. Si un nouvel enregistrement delta est ajouté à une chaîne qui contient déjà 16 éléments, les modifications effectuées dans les enregistrements delta sont consolidées dans la page d’index référencée. La page est ensuite regénérée pour inclure les modifications indiquées par le nouvel enregistrement delta ayant déclenché la consolidation. La nouvelle page regénérée a le même ID de page, mais elle a une nouvelle adresse mémoire.

Fractionnement de page

Une page d’index dans Bw-tree augmente en fonction des besoins, à partir du stockage d’une seule ligne à un maximum de 8 Ko. Quand la page d’index atteint une taille de 8 Ko, l’insertion d’une nouvelle ligne entraîne le fractionnement de la page d’index. Pour une page interne, cela signifie qu’il n’y a plus de place pour ajouter une autre valeur de clé et pointeur, et pour une page feuille, cela signifie que la ligne serait trop grande pour s’adapter à la page une fois que tous les enregistrements delta sont incorporés. Les informations de statistiques dans l’en-tête de page d’une page feuille effectuent le suivi de la quantité d’espace nécessaire pour consolider les enregistrements delta. Ces informations sont ajustées à mesure que chaque nouvel enregistrement delta est ajouté.

Une opération de fractionnement est effectuée en deux étapes atomiques. Dans le diagramme suivant, supposons qu’une page feuille force un fractionnement, car une clé avec la valeur 5 est insérée et qu’une page non-feuille pointe vers la fin de la page de niveau feuille actuelle (valeur de clé 4).

Étape 1 : Allouez deux nouvelles pages P1 et P2, puis fractionnez les lignes de la page P1 précédente sur ces nouvelles pages, y compris la ligne venant d’être insérée. Un nouvel emplacement dans la table de mappage de pages est utilisé pour stocker l’adresse physique de la page P2. Ces pages, P1 et P2 ne sont pas encore accessibles aux opérations simultanées. En outre, le pointeur logique entre P1 et P2 est défini. Ensuite, dans une étape atomique, mettez à jour la table de mappage de page pour changer le pointeur de l’ancien P1 vers le nouveau P1.

Étape 2 : La page non-sourde pointe vers P1, mais il n’y a pas de pointeur direct d’une page non-sourde vers P2. P2 est uniquement accessible via P1. Pour créer un pointeur à partir d’une page non-sourde vers P2, allouez une nouvelle page non-sourde (page d’index interne), copiez toutes les lignes de l’ancienne page non-sourde et ajoutez une nouvelle ligne pour pointer vers P2. Une fois cette opération effectuée, dans une étape atomique, mettez à jour la table de mappage de page pour modifier le pointeur de l’ancienne page non-sourde vers la nouvelle page non-feuille.

Fusion de page

Lorsqu’une DELETE opération aboutit à une page ayant moins de 10 % de la taille maximale de la page (actuellement 8 Ko) ou avec une seule ligne sur celle-ci, cette page est fusionnée avec une page contiguë.

Quand une ligne est supprimée d’une page, un enregistrement delta correspondant à la suppression est ajouté. En outre, une vérification est effectuée pour déterminer si la page d’index (page non-en-feuille) est éligible à La fusion. Cette vérification vérifie si l’espace restant après la suppression de la ligne sera inférieur à 10 % de la taille maximale de la page. Si la fusion est possible, l’opération de fusion est effectuée en trois étapes atomiques.

Dans l’image ci-dessous, supposons qu’une DELETE opération supprime la valeur de clé 10.

Étape 1 : une page delta représentant la valeur de clé 10 (triangle bleu) est créée et son pointeur dans la page non-sourde Pp1 est défini sur la nouvelle page delta. De plus, une page delta spécifique pour la fusion (triangle vert) est créée, et est liée pour pointer vers la page delta. À ce stade, les deux pages (page delta et page delta de fusion) ne sont pas visibles pour une transaction simultanée. Dans une étape atomique, le pointeur vers la page de niveau feuille P1 de la table de mappage de page est mis à jour pour pointer vers la page delta de fusion. Après cette étape, l’entrée de la valeur de clé 10 dans la page Pp1 pointe maintenant vers la page delta de la fusion.

Étape 2 : La ligne représentant la valeur de clé 7 dans la page non-sourde Pp1 doit être supprimée, et l’entrée de la valeur de clé 10 mise à jour pour pointer vers P1. Pour ce faire, une nouvelle page non-sourde Pp2 est allouée et toutes les lignes de Pp1 sont copiées à l’exception de la ligne représentant la valeur de clé 7 ; ensuite, la ligne de la valeur de clé 10 est mise à jour pour pointer vers la page P1. Une fois cette opération effectuée, dans une étape atomique, l’entrée de la table de mappage de page pointant vers Pp1 est mise à jour pour pointer vers Pp2. Pp1 n’est plus accessible.

Étape 3 : Les pages de niveau feuille P2 et P1 sont fusionnées et les pages delta supprimées. Pour ce faire, une nouvelle page P3 est allouée, les lignes des pages P2 et P1 sont fusionnées, et les modifications des pages delta sont ajoutées à la nouvelle page P3. Ensuite, dans une étape atomique, l’entrée de la table de mappage de pages pointant vers la page P1 est mise à jour pour pointer vers la page P3.

Considérations relatives aux performances

Les performances d’un index non-cluster sont meilleures que celles d’un index de hachage non-cluster lors de l’interrogation d’une table à mémoire optimisée avec des prédicats d’inégalité.

Une colonne dans une table optimisée en mémoire peut faire partie d'un index de hachage et d'un index non cluster.

Lorsqu’une colonne clé d’un index non cluster a de nombreuses valeurs en double, les performances peuvent se dégrader pour les mises à jour, les insertions et les suppressions. Une façon d’améliorer les performances dans cette situation consiste à ajouter une colonne qui a une meilleure sélectivité dans la clé d’index.

Étapes suivantes

• CREATE INDEX (Transact-SQL)
• Réorganiser et reconstruire des index
• Tables et index partitionnés
• Index pour les tables optimisées en mémoire
• Index columnstore - Présentation
• Index sur les colonnes calculées
• Paramétrer les index non-cluster avec les suggestions d’index manquants