Indexation de SQL Server : vérifier et corriger la fragmentation des index, puis générer des rapports

La fragmentation d’index peut se produire tout au long de la durée de vie d’un index, en général au fur et à mesure que les données d’un index sont modifiées. Que ces modifications soient des insertions, des suppressions ou des mises à jour, elles peuvent entraîner une fragmentation de l’index.

Pour comprendre la fragmentation d’index, vous devez tout d’abord comprendre l’index même. Les index sont créés sur les colonnes dans les vues ou les tables. Leur structure ordonnée est conçue pour que vous puissiez rechercher et trouver plus facilement les données dont vous avez besoin en fonction des valeurs de ces colonnes. 

Lorsqu’un index est fragmenté, l’extraction des données, qui est critique pour le fonctionnement de la base de données, peut être considérablement ralentie et bien moins efficace. La dégradation des performances entraînée par la fragmentation d’index peut également avoir un impact significatif sur les utilisateurs, qu’ils soient employés ou clients.

Une structure en arbre B permet de stocker les index sur le disque. Les index sont des structures sur disque. Ils sont constitués de clés qui pointent vers l’une des nombreuses colonnes de la vue ou la table. Après avoir créé un index sur une colonne, vous pouvez rechercher la ligne dont vous avez besoin en fonction des chiffres de l’index. Le serveur effectue la recherche à votre place, trouve tout d’abord la valeur nécessaire dans l’index, puis utilise l’index même pour trouver les données de ligne qu’il recherche dans la base de données. Si vous n’aviez pas d’index, ce processus serait nettement plus lent, car le serveur devrait effectuer une analyse complète des tables pour trouver les données de ligne nécessaires. Ce processus serait bien plus lent et nécessiterait beaucoup plus d’E/S.

En général, lorsqu’on fait référence à la fragmentation d’index, on désigne en fait la fragmentation qui se produit dans SQL Server.

La fragmentation d’index SQL Server se décompose généralement en deux principaux types :

1. Fragmentation d’ordre logique/fragmentation externe : Ce type de fragmentation d’index SQL est provoqué par des pages obsolètes résultant de l’insertion, de la suppression et de la modification de données au fil du temps. Par exemple, cela peut se produire en raison d’une mise à jour ou d’une insertion qui allonge un enregistrement existant. Si la page de feuille de l’index est saturée et que davantage d’espace est requis, SQL Server peut diviser une page en plaçant la moitié des lignes de l’ancienne page sur une nouvelle page, qui n’est généralement pas physiquement contiguë à celle d’origine.
2. Fragmentation de densité de page/fragmentation interne : Alors que la fragmentation externe se produit lorsqu’il n’y a plus suffisamment d’espace, la fragmentation interne se produit lorsque des pages présentent trop d’espace libre. 

La vérification et la réduction de la fragmentation d’index peuvent vous aider à régler plus efficacement les index afin d’améliorer les performances de SQL Server. Sans informations sur la fragmentation d’index, les utilisateurs peuvent remarquer un ralentissement de leur base de données. La surveillance d’autres problèmes en lien avec la fragmentation d’index peut être utile, notamment :

• Ralentissements de requêtes : Même une fragmentation mineure peut s’accumuler au fil du temps et ralentir les requêtes en provoquant des goulots d’étranglement de base de données.
• Informations manquantes : Sans analyse approfondie des index SQL Server, il est difficile d’identifier précisément les causes premières des goulots d’étranglement et des temps d’attente élevés.
• Réglage limité : Lorsque le réglage n’est pas vraiment ciblé, il peut être tentant d’ajouter du matériel plutôt que de résoudre les problèmes de fragmentation, mais cela peut entraîner des coûts plus élevés.

Malheureusement, il est impossible d’éviter complètement la fragmentation d’index, mais vous pouvez prendre les mesures nécessaires pour optimiser l’indexation. Pour optimiser l’indexation, vous pouvez, entre autres, adopter des mesures proactives de résolution des problèmes de fragmentation d’index SQL Server et des autres problèmes que présente votre base de données.

Deux méthodes principales permettent de gérer la fragmentation d’index SQL Server : la reconstruction et la réorganisation de l’index. Les opérations de base nécessaires pour chaque processus sont les suivantes :

1. Reconstruction : Lors de la reconstruction d’un index, vous devez abandonner l’ancien index et le reproduire avec de nouvelles pages d’index. Vous pouvez l’exécuter hors ligne ou en ligne, mais lorsqu’un index est en cours de reconstruction, la table de l’index est inaccessible jusqu’à la fin du processus.
2. Réorganisation : Lors de la réorganisation de l’index, vos pages d’index sont réorganisées en supprimant l’espace inutilisé ou libre de chaque page.

Lorsque l’un de ces deux processus de défragmentation est terminé, l’index devrait être à nouveau contigu, offrant ainsi un accès plus efficace et plus rapide.

L’une des façons d’optimiser plus facilement votre index consiste à utiliser un outil tel que SolarWinds^® Database Performance Analyzer (DPA). DPA est conçu pour surveiller en permanence votre base de données, pour que vous puissiez identifier plus facilement les index fragmentés. En outre, DPA peut vous prévenir en cas d’anomalies et fournit des conseils exploitables pour résoudre les problèmes, comme le réglage de vos index. 

DPA est un outil de fragmentation d’index conçu pour vérifier la fragmentation des bases de données SQL Server, entre autres.

En tant que solution d’optimisation des performances des bases de données, l’outil permet de résoudre les problèmes de fragmentation d’index SQL Server, de régler les requêtes et d’appliquer d’autres pratiques recommandées en matière d’optimisation des bases de données. DPA offre une solution puissante de surveillance des problèmes de performance dans votre index grâce aux rapports de fragmentation d’index SQL, aux alertes personnalisables et aux conseils exploitables qui facilitent le réglage des requêtes de fragmentation d’index SQL Server.

En matière de fragmentation d’index SQL Server, Database Performance Analyzer peut vous aider à :

• Comprendre les ralentissements : La fonctionnalité d’analyse des temps de réponse de DPA permet de rapidement évaluer les performances des requêtes et identifier les problèmes de fragmentation.
• Régler les performances : DPA fournit des données précises sur les performances des requêtes SQL pour vous aider à régler les requêtes et les index et à accélérer les opérations de l’utilisateur final.
• Accéder aux conseils de réglage des tables : La page du conseiller de réglage des tables de DPA affiche l’analyse de la fragmentation et attribue des priorités aux requêtes inefficaces pour vous permettre de mieux cibler vos interventions en matière de dépannage et d’optimisation. Vous pouvez facilement accéder aux données détaillées du problème spécifique que vous essayez de résoudre directement à partir de la page et obtenir les informations dont vous avez besoin pour résoudre les problèmes de fragmentation d’index en commençant par la priorité la plus élevée.
• Personnaliser les alertes automatiques : DPA propose des alertes robustes de fragmentation d’index qui vous permettent de choisir des seuils spécifiques à chaque base de données pour recevoir 24h/24, 7j/7 des notifications sur la fragmentation.
• Afficher un tableau de bord et des rapports : Surveillez la fragmentation de SQL Server dans un tableau de bord intuitif et recevez des rapports personnalisables. Ces rapports de fragmentation de SQL Server vous permettent de rester au courant des principales données historiques et des problèmes actuels de votre index. Ces rapports facilitent également la résolution proactive des contraintes de capacité et des ralentissements résultant de la fragmentation d’index.