Partager via

Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

Cet article vous aide à déterminer quand et comment effectuer la maintenance des index. Il aborde des concepts tels que la fragmentation d’index et la densité de page, ainsi que leur impact sur les performances des requêtes et la consommation des ressources. Il décrit les méthodes de maintenance d’index, la réorganisation d’un index et la reconstruction d’un index, et suggère une stratégie de maintenance d’index qui équilibre les améliorations potentielles des performances par rapport à la consommation de ressources requise pour la maintenance.

Remarque

Cet article ne s’applique pas non plus à un pool SQL dédié dans Azure Synapse Analytics. Pour plus d’informations sur la maintenance des index dans un pool SQL dédié dans Azure Synapse Analytics, consultez Indexer les tables d’un pool SQL dédié dans Azure Synapse Analytics.

Concepts : fragmentation d’index et densité de page

Présentation de la fragmentation d’index et de son impact sur les performances :

  • Dans les index de type Arbre B (rowstore), la fragmentation intervient lorsque des index possèdent des pages dans lesquelles l’organisation logique au sein de l’index (reposant sur les valeurs de clés de celui-ci) ne correspond pas à l’organisation physique des pages d’index.

    Remarque

    De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

  • Le moteur de base de données modifie automatiquement des index quand des opérations d’insertion, de mise à jour ou de suppression sont effectuées sur les données sous-jacentes. Par exemple, l’ajout de lignes dans une table peut entraîner le fractionnement des pages existantes dans les index rowstore afin de libérer de l’espace pour l’insertion de nouvelles lignes. Au fil des modifications, les données figurant dans l'index sont éparpillées dans la base de données (fragmentée).

  • Pour les requêtes qui lisent de nombreuses pages à l’aide d’analyses d’index complètes ou de plages, les index fortement fragmentés peuvent dégrader les performances des requêtes quand des demandes d’E/S supplémentaires sont nécessaires pour lire les données. Au lieu d’un petit nombre de demandes d’E/S volumineuses, la requête requiert un plus grand nombre de demandes d’E/S peu volumineuses pour lire la même quantité de données.

  • Lorsque le sous-système de stockage offre de meilleures performances d’E/S séquentielles que les performances d’E/S aléatoires, la fragmentation d’index peut dégrader les performances, car des E/S aléatoires sont nécessaires pour lire les index fragmentés.

Qu’est-ce que la densité de page (également appelée remplissage de la page) et comment influe-t-elle sur les performances ?

  • Chaque page de la base de données peut contenir un nombre variable de lignes. Si les lignes prennent tout l’espace sur une page, la densité de page est de 100 %. Si une page est vide, la densité de page est de 0 %. Si une page avec une densité de 100 % est fractionnée en deux pages pour s’adapter à une nouvelle ligne, la densité des deux nouvelles pages est d’environ 50 %.
  • Lorsque la densité de page est faible, davantage de pages sont nécessaires pour stocker la même quantité de données. Cela signifie qu’il y a plus d’E/S nécessaires pour lire et écrire ces données, ce qui requiert davantage de mémoire pour mettre en cache ces données. Lorsque la mémoire est limitée, moins de pages requises par une requête sont mises en cache, ce qui entraîne encore plus d’E/S sur le disque. Par conséquent, une faible densité de page a un impact négatif sur les performances.
  • Lorsque Moteur de base de données ajoute des lignes à une page pendant la création, la reconstruction ou la réorganisation de l’index, elle ne remplira pas complètement la page si le facteur de remplissage de l’index est défini sur une valeur autre que 100 (ou 0, ce qui est équivalent dans ce contexte). Cela entraîne une densité de page inférieure et entraîne de manière similaire une surcharge d’E/S et un impact négatif sur les performances.
  • Une faible densité de page peut augmenter le nombre de niveaux d’arbres B intermédiaires. Cela augmente modérément les coûts des ressources processeur et d’E/S liés à la recherche de pages de niveau feuille dans les analyses et les recherches d’index.
  • Lorsque l’optimiseur de requête compile un plan de requête, il prend en compte le coût des E/S nécessaires pour lire les données requises par la requête. Avec une faible densité de page, il y a plus de pages à lire, donc le coût des E/S est plus élevé. Cela peut avoir un impact sur le choix du plan de requête. Par exemple, étant donné que la densité de page diminue au fil du temps en raison des fractionnements de pages, l’optimiseur peut compiler un plan différent pour la même requête, avec un profil de performances et de consommation de ressources différent.

Conseil

Dans de nombreuses charges de travail, l’augmentation de la densité de page entraîne un impact positif sur les performances par rapport à la réduction de la fragmentation.

Pour éviter de réduire la densité de page inutilement, Microsoft déconseille de définir un facteur de remplissage sur des valeurs autres que 100 ou 0, sauf dans certains cas où les index présentent un grand nombre de fractionnements de pages, par exemple les index fréquemment modifiés avec des colonnes de début contenant des valeurs GUID non séquentielles.

Mesurer la fragmentation des index et la densité des pages

La fragmentation et la densité de page font partie des facteurs à prendre en compte pour décider s’il faut effectuer la maintenance d’index, ainsi que la méthode de maintenance à utiliser.

La fragmentation est définie différemment pour les index rowstore et columnstore. Pour les index rowstore, sys.dm_db_index_physical_stats vous permet de déterminer la fragmentation et la densité de page dans un index spécifique, dans tous les index d’une table ou d’une vue indexée, dans tous les index d’une base de données, ou dans tous les index de l’ensemble des bases de données. Pour les index partitionnés, sys.dm_db_index_physical_stats() fournit aussi ces informations pour chaque partition.

Le jeu de résultats renvoyé par sys.dm_db_index_physical_stats inclut les colonnes suivantes :

Colonne Description
avg_fragmentation_in_percent Fragmentation logique (pages non ordonnées dans un index).
avg_page_space_used_in_percent Densité de page moyenne.

Pour les groupes de lignes compressés dans les index de columnstore, la fragmentation est définie comme le rapport entre les lignes supprimées et le nombre total de lignes, exprimé en pourcentage. sys.dm_db_column_store_row_group_physical_stats vous permet de déterminer le nombre de lignes totales et supprimées par groupe de lignes dans un index spécifique, tous les index d’une table, ou tous les index d’une base de données.

Le jeu de résultats renvoyé par sys.dm_db_column_store_row_group_physical_stats inclut les colonnes suivantes :

Colonne Description
total_rows Nombre de lignes stockées physiquement dans le groupe de lignes. Pour les groupes de lignes compressés, cela comprend les lignes qui sont marquées comme supprimées.
deleted_rows Nombre de lignes physiquement stockées dans un groupe de lignes compressé marqué pour suppression. 0 pour les groupes de lignes qui se trouvent dans le delta store.

La fragmentation des groupes de lignes compressés dans un index columnstore peut être calculée à l’aide de la formule suivante :

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Conseil

Pour les index rowstore et columnstore, examinez la fragmentation de l’index ou du segment de mémoire et la densité des pages après la suppression ou la mise à jour d’un grand nombre de lignes. Pour les segments de mémoire, en cas de mises à jour fréquentes, examinez régulièrement la fragmentation pour éviter la prolifération des enregistrements de transfert. Pour plus d’informations sur les segments de mémoire, consultez Segments de mémoire (tables sans index cluster).

Consultez des exemples de requêtes pour déterminer la fragmentation et la densité de page.

Méthodes de maintenance d’index : réorganiser et reconstruire

Vous pouvez réduire la fragmentation de l’index et augmenter la densité de page à l’aide de l’une des méthodes suivantes :

  • Réorganiser un index
  • Regénérer un index

Remarque

Dans le cas d’index partitionnés, vous pouvez utiliser les méthodes suivantes sur la totalité des partitions ou sur une partition unique d’un index.

Réorganiser un index

La réorganisation d’un index est moins gourmande en ressources que la reconstruction d’un index. Pour cette raison, ce doit être votre méthode de maintenance d’index par défaut, à moins qu’il y ait une raison spécifique d’utiliser la reconstruction d’index. La réorganisation est toujours une opération en ligne. En d’autres termes, les verrous à long terme au niveau de l’objet ne sont pas conservés, ce qui permet aux requêtes et aux mises à jour de la table sous-jacente de se poursuivre pendant l’opération ALTER INDEX ... REORGANIZE.

  • Pour les index rowstore, le moteur de base de données défragmente le niveau feuille des index cluster et non cluster sur les tables et les vues en réorganisant physiquement les pages de niveau feuille afin de suivre l’ordre logique des nœuds terminaux (de gauche à droite). La réorganisation compresse également les pages d’index pour rendre la densité de page égale au facteur de remplissage de l’index. Pour afficher le paramètre du facteur de remplissage, utilisez sys.indexes. Pour obtenir des exemples de syntaxe, consultez Exemples : réorganisation d’index rowstore.
  • Lors de l’utilisation des index columnstore, le delta store peut se retrouver avec plusieurs petits groupes de lignes après l’insertion, la mise à jour et la suppression des données au fil du temps. La réorganisation d’un index columnstore force les groupes de lignes du delta store en groupes de lignes compressés dans columnstore, et combine des groupes de lignes compressés plus petits dans des groupes de lignes plus grands. L’opération de réorganisation supprime également physiquement les lignes qui ont marquées comme supprimées dans columnstore. La réorganisation d’un index columnstore peut nécessiter des ressources processeur supplémentaires pour la compression des données. Pendant l’exécution de l’opération, les performances se dégrader. Toutefois, une fois les données compressées, les performances des requêtes s’améliorent. Pour obtenir des exemples de syntaxe, consultez Exemples : réorganisation d’index columnstore.

Remarque

À partir de SQL Server 2019 (15.x), Azure SQL Database, Azure SQL Managed Instance et le moteur de tuple est aidé par une tâche de fusion en arrière-plan qui compresse automatiquement les rowgroups delta OPEN plus petits qui existent depuis un certain temps, tel que déterminé par un seuil interne, ou qui fusionne les rowgroups compressés à partir desquels un grand nombre de lignes a été supprimé. Cela améliore la qualité de l’index columnstore dans le temps. Dans la plupart des cas, cela permet d’éviter l’exécution des commandes ALTER INDEX ... REORGANIZE.

Conseil

Si vous annulez une opération de réorganisation ou si elle est interrompue, la progression atteinte à ce point est conservée dans la base de données. Pour réorganiser des index volumineux, l’opération peut être démarrée et arrêtée plusieurs fois jusqu’à ce qu’elle se termine.

Regénérer un index

La reconstruction d'un index entraîne sa suppression puis sa recréation. En fonction du type d’index et de la version de Moteur de base de données, une opération de reconstruction peut être effectuée hors connexion ou en ligne. Une reconstruction hors connexion prend généralement moins de temps qu’une reconstruction en ligne, mais elle détient des verrous au niveau de l’objet pendant toute la durée de l’opération de reconstruction, bloquant ainsi les requêtes d’accès à la table ou à la vue

Une reconstruction d’index en ligne ne nécessite pas de verrous au niveau de l’objet jusqu’à la fin de l’opération, moment où un verrou doit être brièvement maintenu pour terminer la reconstruction. Selon la version du Moteur de base de données, une régénération d’index en ligne peut être démarrée en tant qu’opération pouvant être reprise. Une reconstruction d’index pouvant être reprise peut être suspendue, ce qui conserve la progression atteinte à ce point. Une opération de reconstruction peut être reprise après avoir été suspendue ou interrompue, ou abandonnée si l’exécution de la reconstruction devient inutile.

Pour la syntaxe Transact-SQL, consultez ALTER INDEX REBUILD. Pour plus d’informations sur les reconstructions d’index en ligne, consultez Exécuter des opérations en ligne sur les index.

Remarque

Pendant la reconstruction d’un index en ligne, chaque modification de données dans des colonnes indexées doit mettre à jour une copie supplémentaire de l’index. Cela peut entraîner une dégradation mineure des performances des instructions de modification de données pendant la reconstruction en ligne.

Si une opération d’index en ligne pouvant être reprise est suspendue, cet impact sur les performances persiste jusqu’à ce que l’opération pouvant être reprise soit terminée ou abandonnée. Si vous n’envisagez pas de terminer une opération d’index pouvant être reprise, abandonnez-la au lieu de la suspendre.

Conseil

En fonction des ressources disponibles et des modèles de charge de travail, spécifier une valeur supérieure à la valeur par défaut MAXDOP dans l’instruction ALTER INDEX REBUILD permet d’accélérer la reconstruction, mais au détriment d’une augmentation des ressources processeur.

  • Pour les index rowstore, la reconstruction supprime la fragmentation à tous les niveaux de l’index et compacte les pages en fonction du facteur de remplissage spécifié ou actuel. Si ALL est précisé, tous les index sur la table sont supprimés puis reconstruits en une seule opération. Lorsque de la reconstruction d’index contenant au moins 128 étendues, le moteur de base de données diffère les désallocations de pages et l’acquisition des verrous qui y sont associés jusqu’à ce que la reconstruction soit terminée. Pour obtenir des exemples de syntaxe, consultez Exemples : reconstruction d’index rowstore.

  • Pour les index columnstore, la reconstruction supprime la fragmentation, déplace toutes les lignes du delta store dans columnstore, et supprime physiquement les lignes qui ont été marquées pour suppression. Pour obtenir des exemples de syntaxe, consultez Exemples : reconstruction d’index columnstore.

    Conseil

    À partir de SQL Server 2016 (13.x), la régénération de l’index columnstore n’est généralement pas nécessaire, car REORGANIZE effectue l’essentiel de la régénération sous la forme d’une opération en ligne.

Utiliser la reconstruction d’index pour récupérer des données altérées

Avant SQL Server 2008 (10.0.x), vous aviez parfois la possibilité de reconstruire un index non cluster de lignes afin de corriger les incohérences dues à des données corrompues dans l’index.

Vous pouvez toujours réparer de telles incohérences dans l’index non cluster en reconstruisant un index non cluster hors connexion. Toutefois, vous ne pouvez pas réparer les incohérences d’un index non cluster en reconstruisant l’index en ligne. En effet, le mécanisme de reconstruction en ligne utilise l’index non cluster existant comme base pour la reconstruction et propage de ce fait l’incohérence. La reconstruction de l’index hors connexion peut parfois forcer une analyse de l’index cluster (ou du segment de mémoire). Par conséquent, remplacez les données incohérentes dans l’index non cluster par les données de l’index cluster ou du segment de mémoire.

Pour vous assurer que l’index cluster ou le segment de mémoire est utilisé comme source de données, supprimez et recréez l’index non cluster au lieu de le reconstruire. Comme pour les versions antérieures, vous pouvez récupérer à partir d’incohérences en restaurant les données affectées à l’aide d’une sauvegarde. Toutefois, vous pourrez peut-être réparer les incohérences d’un index non cluster en le générant à nouveau hors connexion ou en le recréant. Pour plus d’informations, consultez DBCC CHECKDB (Transact-SQL).

Gestion automatique des index et des statistiques

Tirez parti de solutions comme Adaptive Index Defrag pour gérer automatiquement la fragmentation des index et les mises à jour des statistiques pour une ou plusieurs bases de données. Cette procédure choisit automatiquement s’il faut reconstruire ou réorganiser un index en fonction de son niveau de fragmentation, entre autres, et mettre à jour les statistiques avec un seuil linéaire.

Considérations spécifiques à la reconstruction et à la réorganisation d’index rowstore

Les scénarios suivants entraînent la reconstruction automatique de tous les index non cluster rowstore sur une table :

  • Création d’un index cluster sur une table, y compris la recréation de l’index cluster avec une clé différente à l’aide de CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Suppression d’un index cluster, ce qui aboutit au stockage de la table en tant que segment de mémoire

Les scénarios suivants ne reconstruisent pas automatiquement tous les index non cluster rowstore sur la même table :

  • Reconstruction d’un index cluster
  • Modification du stockage d’index en cluster, telle que l’application d’un schéma de partitionnement ou le déplacement de l’index cluster vers un autre groupe de fichiers

Important

Un index ne peut pas être réorganisé ou régénéré si le groupe de fichiers dans lequel il se trouve est hors connexion ou en lecture seule. Si le mot clé ALL est spécifié et que des index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule, l'instruction échoue.

En cas de régénération d’un index, le média physique doit avoir suffisamment d’espace pour stocker deux copies de l’index. Lorsque la regénération est terminée, le moteur de base de données supprime l’index d’origine.

Si ALL est spécifié avec l’instruction ALTER INDEX ... REORGANIZE, les index cluster, non cluster et XML de la table sont réorganisés.

Généralement, la régénération ou la réorganisation de petits index rowstore ne réduit pas la fragmentation. Jusqu’à SQL Server 2014 (12.x) compris, le moteur de base de données SQL Server alloue de l’espace à l’aide d’étendues mixtes. Par conséquent, les pages de petits index sont parfois stockées sur des extensions mixtes, ce qui fragmente implicitement ces index. Les extensions mixtes sont partagées par huit objets maximum ; par conséquent, la fragmentation dans un petit index peut ne pas être réduite après sa réorganisation ou sa reconstruction.

Considérations spécifiques à la régénération d’un index columnstore

Lors de la regénération d’un index columnstore, le moteur de base de données lit toutes les données de l’index columnstore d’origine, dont le delta store. Il combine les données dans de nouveaux groupes de lignes et compresse tous les groupes de lignes dans columnstore. Le moteur de base de données défragmente le columnstore en supprimant physiquement les lignes qui ont été marquées comme supprimées.

Remarque

À partir de SQL Server 2019 (15.x), le moteur de tuple est aidé par une tâche de fusion en arrière-plan qui compresse automatiquement les groupes storerow ouverts plus petits du delta store qui existent depuis un certain temps, tel que déterminé par un seuil interne, ou qui fusionne les groupes de lignes compressés où un grand nombre de lignes a été supprimé. Cela améliore la qualité de l’index columnstore dans le temps. Pour plus d'informations sur les termes et les concepts columnstore, consultez Index Columnstore : vue d'ensemble.

Regénérer une partition au lieu de la table entière

Reconstruire un index columnstore cluster entier prend beaucoup de temps si l'index est volumineux, et cela nécessite suffisamment d'espace disque pour stocker une copie supplémentaire de la totalité de l’index pendant la reconstruction.

Pour les tables partitionnées, vous n’avez pas besoin de reconstruire tout l’index columnstore si la fragmentation est présente uniquement dans certaines partitions, par exemple dans les partitions où les instructions UPDATE, DELETE ou MERGE ont affecté un grand nombre de lignes.

La reconstruction d’une partition après le chargement ou la modification des données garantit que toutes les données sont stockées dans des groupes de lignes compressés dans columnstore. Lorsque le processus de chargement des données insère des données dans une partition en utilisant des lots inférieurs à 102 400 lignes, la partition peut se retrouver avec plusieurs groupes de lignes ouverts dans le delta store. La reconstruction déplace toutes les lignes du delta store dans des groupes de lignes compressés dans columnstore.

Considérations spécifiques à la réorganisation d’un index columnstore

Lors de la réorganisation d’un index columnstore, le moteur de base de données compresse chaque groupe de ligne du delta store dans le columnstore en tant que groupe de lignes compressé. À partir de SQL Server 2016 (13.x) et dans Azure SQL Database, la commande REORGANIZE effectue les optimisations de défragmentation supplémentaires suivantes en ligne :

  • Supprime physiquement les lignes d’un groupe de lignes quand au moins 10 % des lignes ont été supprimées de façon logique. Par exemple, si un groupe de lignes compressé de 1 million de lignes comporte 100 000 lignes supprimées, le moteur de base de données efface les lignes supprimées et recompresse le groupe de lignes avec 900 000 lignes, réduisant ainsi l’encombrement du stockage.
  • Associe un ou plusieurs groupes de lignes compressés pour augmenter les lignes par groupe de lignes, jusqu’à la valeur maximale de 1 048 576 lignes. Par exemple, si vous insérez en bloc cinq lots de 102 400 lignes chacun, vous obtiendrez cinq groupes de lignes compressés. Si vous exécutez l’instruction REORGANIZE, ces groupes de lignes seront fusionnés dans un groupe de lignes compressé contenant 512 000 lignes. Cela suppose qu’il n’existe aucune limitation de mémoire ni de taille de dictionnaire.
  • Le moteur de base de données tente d’associer des groupes de lignes dans lesquels 10 % ou plus des lignes ont été marquées comme supprimées avec d’autres groupes de lignes. Par exemple, le groupe de lignes 1 est compressé et contient 500 000 lignes, tandis que le groupe de lignes 21 est compressé et contient 1 048 576 lignes. Pour le groupe de lignes 21, 60 % des lignes sont marquées comme supprimées, ce qui laisse 409 830 lignes. Le moteur de base de données favorise la combinaison de ces deux groupes de lignes pour compresser un nouveau groupe de lignes qui contient 909 830 lignes.

Après avoir exécuté des charges de données, vous pouvez avoir plusieurs petits groupes de lignes dans le delta store. Vous pouvez utiliser ALTER INDEX REORGANIZE pour forcer ces groupes de lignes dans columnstore, puis combiner des groupes de lignes compressés plus petits dans des groupes de lignes compressés plus volumineux. L’opération de réorganisation supprimera également les lignes qui ont été marquées comme supprimées du columnstore.

Remarque

La réorganisation d’un index columnstore à l’aide de Management Studio combine les groupes de lignes compressés ensemble, mais ne force pas tous les groupes de lignes à être compressés dans le columnstore. Les groupes de lignes fermés sont compressés, mais les groupes de lignes ouverts ne sont pas compressés dans le columnstore. Pour compresser de force tous les groupes de lignes, utilisez l’exemple Transact-SQL qui inclut COMPRESS_ALL_ROW_GROUPS = ON.

Éléments à prendre en compte avant de procéder à la maintenance d’un index

La maintenance d’un index, effectuée par la réorganisation ou la reconstruction d’un index, est gourmande en ressources. Cela entraîne une augmentation significative des ressources processeur, de la mémoire utilisée et des E/S de stockage. Toutefois, en fonction de la charge de travail de la base de données et d’autres facteurs, les avantages qu’elle apporte vont d’une importance cruciale à un impact minime.

Pour éviter une utilisation inutile des ressources, évitez d’effectuer des maintenances d’index sans discernement. Au lieu de cela, les avantages de la maintenance d’index doivent être déterminés de façon empirique pour chaque charge de travail à l’aide de la stratégie recommandée, et doivent être pondérés par rapport aux coûts des ressources et l’impact sur la charge de travail nécessaire pour bénéficier de tels avantages.

La probabilité d’obtenir des avantages en termes de performances en réorganisant ou en reconstruisant un index est plus élevée lorsque l’index est fortement fragmenté ou lorsque sa densité de page est faible. Mais ce ne sont pas les seuls éléments à prendre en compte. Des facteurs tels que les modèles de requête (traitement des transactions par opposition à l'analyse et à la production de rapports), le comportement du sous-système de stockage, la mémoire disponible et les améliorations du moteur de base de données au fil du temps jouent tous un rôle.

Important

Les décisions relatives à la maintenance de l'indexe doivent être prises en tenant compte de multiples facteurs dans le contexte spécifique de chaque charge de travail, y compris le coût en ressources de la maintenance. Elles ne doivent pas reposer uniquement sur des seuils de fragmentation fixe ou de densité de page.

Effet secondaire positif de la reconstruction d’index

Les clients observent souvent une amélioration des performances après la reconstruction des index. Mais dans de nombreux cas, ces améliorations ne sont pas liées à la réduction de la fragmentation ni à l’amélioration de la densité de page.

Une reconstruction d’index présente un avantage important : elle met à jour les statistiques sur les colonnes clés de l’index en analysant toutes les lignes de l’index. Cela équivaut à exécuter UPDATE STATISTICS ... WITH FULLSCAN, qui actualise les statistiques et, parfois, améliore leur qualité par rapport à la mise à jour des statistiques échantillonnée par défaut. Lorsque les statistiques sont mises à jour, les plans de requête qui les référencent sont recompilés. Si le plan précédent d’une requête n’était pas optimal en raison de statistiques obsolètes, d’un taux d'échantillonnage insuffisant des statistiques, ou pour d’autres raisons, le plan recompilé sera souvent plus performant.

Les clients attribuent souvent à tort cette amélioration à la reconstruction de l’index elle-même, la considérant comme le résultat de la réduction de la fragmentation et de l'augmentation de la densité de page. En réalité, le même avantage peut souvent être obtenu à un coût de ressources beaucoup plus faible, en mettant à jour les statistiques au lieu de reconstruire les index.

Conseil

Le coût en ressources de la mise à jour des statistiques est mineur par rapport à la reconstruction de l’index, et l’opération se termine souvent en quelques minutes. Les reconstructions d’index peuvent prendre plusieurs heures.

Stratégie de maintenance d’index

Microsoft recommande aux clients de considérer et d’adopter la stratégie de maintenance d’index suivante :

  • Ne partez pas du principe que la maintenance d’index améliorera toujours sensiblement votre charge de travail.
  • Mesurez l’impact spécifique de la réorganisation ou de la reconstruction des index sur les performances des requêtes dans votre charge de travail. Le magasin de données des requêtes est un bon moyen de mesurer les performances « Avant maintenance » et « Après maintenance » à l’aide de la technique Test A/B.
  • Si vous constatez que la reconstruction d’index améliore les performances, essayez de la remplacer par une mise à jour des statistiques. Cela peut entraîner une amélioration similaire. Dans ce cas, vous n’aurez peut-être pas besoin de reconstruire les index aussi fréquemment, voire pas du tout, et vous pourrez plutôt effectuer des mises à jour périodiques des statistiques. Pour certaines statistiques, vous devrez peut-être augmenter le taux d’échantillonnage à l’aide des clauses WITH SAMPLE ... PERCENT ou WITH FULLSCAN (dans de rares cas).
  • Surveillez la fragmentation de l’index et la densité de page au fil du temps pour vérifier s’il existe une corrélation entre ces valeurs, qui ont tendance à augmenter ou à diminuer, et les performances des requêtes. Si une fragmentation supérieure ou une densité de page inférieure dégradent les performances de façon inacceptable, réorganisez ou reconstruisez les index. Il suffit souvent de réorganiser ou de reconstruire que les index spécifiques utilisés par les requêtes dont les performances sont dégradées. Cela permet d’éviter un coût de ressources plus élevé lié à la maintenance de chaque index dans la base de données.
  • Établir une corrélation entre la fragmentation/la densité de page et les performances vous permet également de déterminer la fréquence de maintenance des index. Ne partez pas du principe que la maintenance doit être effectuée selon le planification fixe. Une meilleure stratégie consiste à surveiller la fragmentation et la densité de page, puis à exécuter la maintenance de l’index si nécessaire avant que les performances ne se dégradent de manière inacceptable.
  • Si vous avez déterminé qu’une maintenance de l’index est nécessaire et que son coût en ressources est acceptable, effectuez la maintenance pendant les périodes de faible utilisation des ressources, si possible.
  • Testez régulièrement, car les modèles d’utilisation des ressources peuvent évoluer au fil du temps.

Maintenance d’index dans Azure SQL Database et Azure SQL Managed Instance

En plus des considérations et de la stratégie ci-dessus, dans Azure SQL Database et Azure SQL Managed Instance, il est particulièrement important de considérer les coûts et les avantages de la maintenance d’index. Les clients ne doivent l’effectuer qu’en cas de besoin avéré, et en tenant compte des points suivants.

  • Azure SQL Database et Azure SQL Managed Instance implémentent une gouvernance des ressources pour fixer des limites à la consommation du processeur, de la mémoire et des E/S en fonction du niveau tarifaire appliqué. Ces limites s’appliquent à toutes les charges de travail des utilisateurs, y compris la maintenance d’index. Si la consommation cumulée des ressources par toutes les charges de travail approche des limites de ressources, l’opération de reconstruction ou de réorganisation peut dégrader les performances des autres charges de travail en raison d’une contention des ressources. Par exemple, les chargements de données en bloc peuvent devenir plus lents car les E/S du journal des transactions sont à 100 % en raison d’une reconstruction simultanée de l’index. Dans Azure SQL Managed Instance, cet impact peut être minimisé en exécutant la maintenance d’index dans un groupe de charge de travail Resource Governor distinct avec une allocation de ressources limitée, au prix d’un allongement de la durée de la maintenance d’index.
  • Par souci d’économie, les clients provisionnent souvent des bases de données, des pools élastiques et des instances gérées avec une marge de manœuvre minimale en matière de ressources. Le niveau tarifaire est choisi pour répondre aux charges de travail des applications. Pour faire face à une augmentation significative de l’utilisation des ressources due à la maintenance de l’index sans dégrader les performances de l’application, les clients peuvent être amenés à fournir davantage de ressources et à augmenter les coûts, sans nécessairement améliorer les performances de l’application.
  • Dans les pools élastiques, les ressources sont partagées entre toutes les bases de données d’un pool. Même si une base de données particulière est inactive, l’exécution de la maintenance de l’index sur cette base de données peut avoir un impact sur les charges de travail des applications exécutées simultanément dans d’autres bases de données du même pool. Pour plus d’informations, consultez Gestion des ressources dans les pools élastiques denses.
  • Pour la plupart des types de stockage utilisés dans Azure SQL Database et Azure SQL Managed Instance, il n’y a aucune différence de performance entre les E/S séquentielles et les E/S aléatoires. Cela réduit l’impact de la fragmentation de l’index sur les performances des requêtes.
  • Lors de l’utilisation de réplicas Scale-out en lecture ou Géoréplication, la latence des données sur les réplicas augmente souvent pendant que la maintenance d’index est effectuée sur le réplica principal. Si un géo-réplica ne dispose pas des ressources suffisantes pour gérer une augmentation de la génération de journaux de transactions causée par une maintenance d’index, il risque de prendre beaucoup de retard par rapport au réplica principal, obligeant le système à le réamorcer. Cela rend le réplica indisponible jusqu’à ce que le réamorçage soit terminée. De plus, dans les niveaux de service Premium et Critique pour l’entreprise, les réplicas utilisés pour garantir une haute disponibilité peuvent également prendre beaucoup de retard par rapport au réplica principal pendant la maintenance de l’index. Si un basculement est nécessaire pendant ou peu après la maintenance de l’index, il peut prendre plus de temps que prévu.
  • Si une reconstruction d’index s’exécute sur le réplica principal et qu’une requête de longue durée s’exécute en même temps sur un réplica lisible, la requête peut être automatiquement interrompue pour éviter de bloquer le thread de phase de restauration sur le réplica.

Il existe des scénarios spécifiques, mais peu fréquents, dans lesquels une maintenance ponctuelle ou périodique de l’index peut être nécessaire dans Azure SQL Database et Azure SQL Managed Instance :

Conseil

Si vous avez déterminé qu’une maintenance d’index est nécessaire pour vos charges de travail Azure SQL Database et Azure SQL Managed Instance, vous devez soit réorganiser les index, soit utiliser la reconstruction d’index en ligne. Cela permet aux charges de travail de requêtes d’accéder aux tables pendant la reconstruction des index.

En outre, le fait de pouvoir reprendre l’opération vous permet d’éviter de la relancer depuis le début si elle est interrompue par un basculement planifié ou non de la base de données. L’utilisation d’opérations d’index pouvant être reprises est particulièrement importante lorsque les index sont volumineux.

Conseil

Les opérations d’index hors connexion sont généralement plus rapides que les opérations en ligne. Elles doivent être utilisées lorsque les tables ne seront pas interrogées par des requêtes pendant l’opération, par exemple après le chargement de données dans des tables de transit dans le cadre d’un processus ETL séquentiel.

Limitations et restrictions

Les index rowstore possédant plus de 128 extensions sont régénérés en deux phases distinctes : une phase logique et une phase physique. Dans la phase logique, les unités d'allocation utilisées par l'index sont signalées comme devant être désallouées, les lignes de données sont copiées et triées, puis elles sont déplacées vers les nouvelles unités d'allocation ayant été créées pour stocker l'index reconstruit. Dans la phase physique, les unités d'allocation préalablement signalées pour être désallouées sont supprimées physiquement dans des transactions courtes qui interviennent en arrière-plan et nécessitent peu de verrous. Pour plus d’informations sur les unités d'allocation, consultez Guide d’architecture des pages et des étendues.

L’instruction ALTER INDEX REORGANIZE a besoin du fichier de données contenant l’index pour disposer d’espace, car l’opération peut uniquement allouer des pages de travail temporaires sur le même fichier, et non dans un autre fichier du même groupe de fichiers. Même si le groupe de fichiers dispose d’espace libre, l’utilisateur peut rencontrer l’erreur 1105 : Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup pendant l’opération de réorganisation si un fichier de données manque d’espace.

Un index ne peut pas être réorganisé lorsque ALLOW_PAGE_LOCKS est désactivé (OFF).

Jusqu’à SQL Server 2017 (14.x), la régénération d’un index columnstore en cluster est une opération hors connexion. Le moteur de base de données doit acquérir un verrou exclusif sur la table ou la partition lorsque la regénération se produit. Les données sont hors connexion et indisponibles pendant la régénération, même si vous utilisez NOLOCK, l’isolation de capture instantanée de lecture validée (RCSI) ou l’isolation de capture instantanée. À partir de SQL Server 2019 (15.x), un index columnstore en cluster peut être régénéré à l’aide de l’option ONLINE = ON.

Avertissement

La création et la reconstruction des index non alignés sur une table contenant plus de 1 000 partitions sont possibles, mais ne sont pas prises en charge. Cela peut entraîner une dégradation des performances ou une consommation de mémoire excessive. Microsoft recommande d’utiliser uniquement des index alignés lorsque le nombre de partitions est supérieur à 1 000.

Limitations des statistiques

  • Quand un index est créé ou reconstruit, les statistiques sont créées ou mises à jour par l’analyse de toutes les lignes de la table, ce qui équivaut à utiliser la clause FULLSCAN dans CREATE STATISTICS ou UPDATE STATISTICS. En revanche, à partir de SQL Server 2012 (11.x), lorsqu’un index partitionné est créé ou reconstruit, les statistiques ne sont pas créées ou mises à jour par l’analyse de toutes les lignes de la table. C’est le taux d’échantillonnage par défaut qui est alors utilisé. Pour créer ou mettre à jour les statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez CREATE STATISTICS ou UPDATE STATISTICS avec la clause FULLSCAN.
  • De même, lorsque l’opération de création ou de reconstruction d’index peut être reprise, les statistiques sont créées ou mises à jour avec le taux d’échantillonnage par défaut. Si les statistiques ont été créées ou mises à jour pour la dernière fois avec la clause PERSIST_SAMPLE_PERCENT définie sur ON, les opérations d’index pouvant être reprises utilisent le taux d'échantillonnage persistant pour créer ou mettre à jour les statistiques.
  • Quand un index est réorganisé, les statistiques ne sont pas mises à jour.

Exemples

Vérifier la fragmentation et la densité de page d’un index rowstore avec Transact-SQL

L’exemple suivant détermine la fragmentation moyenne et la densité de page pour tous les index rowstore de la base de données actuelle. Il utilise le mode SAMPLED pour retourner des résultats utilisables rapidement. Pour obtenir des résultats plus précis, utilisez le mode DETAILED. Cela nécessite d’analyser toutes les pages d’index, ce qui peut prendre beaucoup de temps.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

L'instruction précédente retourne un jeu de résultats similaire au suivant :

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Pour plus d’informations, consultez sys.dm_db_index_physical_stats.

Vérifier la fragmentation d’un index columnstore avec Transact-SQL

L’exemple suivant détermine la fragmentation moyenne pour tous les index columnstore avec des groupes de lignes compressés dans la base de données actuelle.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

L'instruction précédente retourne un jeu de résultats similaire au suivant :

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Conserver des index avec SQL Server Management Studio

Réorganiser ou reconstruire un index

  1. Dans l’Explorateur d’objets, développez la base de données qui contient la table sur laquelle vous souhaitez réorganiser un index.
  2. Développez le dossier Tables .
  3. Développez la table sur laquelle vous souhaitez réorganiser un index.
  4. Développez le dossier Index .
  5. Cliquez avec le bouton droit sur l’index que vous souhaitez réorganiser et sélectionnez Réorganiser.
  6. Dans la boîte de dialogue Réorganiser les index, vérifiez que l’index correct figure dans la grille Index à réorganiser, puis cliquez sur OK.
  7. Cochez la case Compacter les données de la colonne d’objets volumineux pour indiquer que toutes les pages qui contiennent des données LOB seront aussi compactées.
  8. Cliquez sur OK.

Réorganiser tous les index d’une table

  1. Dans l’Explorateur d’objets, développez la base de données qui contient la table sur laquelle vous souhaitez réorganiser les index.
  2. Développez le dossier Tables .
  3. Développez la table sur laquelle vous souhaitez réorganiser les index.
  4. Cliquez avec le bouton droit sur le dossier Index , puis sélectionnez Réorganiser tout.
  5. Dans la boîte de dialogue Réorganiser les index , vérifiez que les index corrects sont dans Index à réorganiser. Pour supprimer un index de la grille Index à réorganiser , sélectionnez l'index et appuyez sur la touche SUPPR.
  6. Cochez la case Compacter les données de la colonne d’objets volumineux pour indiquer que toutes les pages qui contiennent des données LOB seront aussi compactées.
  7. Cliquez sur OK.

Conserver des index avec Transact-SQL

Remarque

Pour obtenir plus d’exemples sur l’utilisation de Transact-SQL pour reconstruire ou réorganiser des index, consultez Exemples ALTER INDEX : index rowstore et Exemples ALTER INDEX : index columnstore.

Réorganiser un index

L’exemple suivant réorganise l’index IX_Employee_OrganizationalLevel_OrganizationalNode sur la table HumanResources.Employee de la base de données AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

L’exemple suivant réorganise l’index columnstore IndFactResellerSalesXL_CCI sur la table dbo.FactResellerSalesXL_CCI de la base de données AdventureWorksDW2022. Cette commande force tous les groupes de lignes ouverts et fermés dans le columnstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Réorganiser tous les index d’une table

L’exemple suivant réorganise tous les index sur la table HumanResources.Employee de la base de données AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Regénérer un index

L'exemple suivant reconstruit un seul index portant sur la table Employee de la base de données AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Reconstruire tous les index d’une table

L’exemple suivant régénère tous les index associés à la table dans la base de données AdventureWorks2022 à l’aide du mot clé ALL. Trois options sont spécifiées.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Pour plus d’informations, consultez ALTER INDEX.

Contenu connexe