Dans cet article, nous allons apprendre à identifier et à résoudre la fragmentation d’index dans SQL Server. L’identification de la fragmentation de l’index et la maintenance de l’index sont des parties importantes de la tâche de maintenance de la base de données. Microsoft SQL Server met constamment à jour les statistiques de l’index avec l’activité d’insertion, de mise à jour ou de suppression sur la table. La fragmentation de l’index est la valeur de performance de l’index en pourcentage, qui peut être récupérée par SQL Server DMV. Selon la valeur de performance de l’index, les utilisateurs peuvent prendre les index en maintenance en révisant le pourcentage de fragmentation à l’aide de l’opération Rebuild ou Reorganize.
Pourquoi le pourcentage de fragmentation de l’index varie-t-il ?
Le pourcentage de fragmentation de l’index varie lorsque les ordres logiques des pages ne se coordonnent pas avec l’ordre physique des pages dans l’allocation des pages d’un index. Avec la modification des données dans la table, les informations peuvent être redimensionnées sur la page de données. La page était pleine avant l’opération de mise à jour de la table. Cependant, de l’espace libre a pu être trouvé sur la page de données avec une opération de mise à jour de la table. Les utilisateurs peuvent observer l’ordre perturbé de la page avec l’opération de suppression massive sur la table. Avec les opérations de mise à jour et de suppression, la page de données ne sera pas une page pleine ou vide. Par conséquent, l’espace libre non utilisé augmente le décalage d’ordre entre la page logique et la page physique avec l’augmentation de la fragmentation, et cela peut causer la pire performance de la requête et consomme plus de ressources du serveur aussi.
Plus essentiel de préciser que la fragmentation de l’index affecte la performance de la requête seulement avec le balayage de la page. Dans de tels cas, il augmente les chances de la mauvaise performance d’autres requêtes SQL ainsi, parce que la requête avec l’index fragmenté élevé sur la table prend plus de temps à exécuter et consomme plus de ressources telles que le Cache, le CPU, et IO. Par conséquent, les autres requêtes SQL ont des difficultés à terminer l’opération avec des ressources serveur incohérentes. Même le blocage peut se produire par l’opération Update et Delete parce que l’optimiseur ne recueillera pas les informations de la fragmentation de l’index tout en générant le plan d’exécution de la requête.
Il peut y avoir un certain nombre d’index créés sur une seule table avec la combinaison de diverses colonnes, et chaque index peut avoir un pourcentage de fragmentation différent. Maintenant, avant de le rendre approprié ou de prendre un index en maintenance, les utilisateurs doivent trouver cette valeur seuil à partir de la base de données. L’instruction T-SQL ci-dessous est un moyen efficace de la trouver avec les détails de l’objet.
Recherche de l’état de fragmentation de l’index en utilisant l’instruction T-.SQL statement
|
1
2
3
4
5
6
. 7
8
9
10
11
12
13
14
|
SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Ici, nous pouvons voir que le pourcentage moyen maximum de fragmentation est perceptible comme 99%, ce qui doit être engagé avec une action pour réduire la fragmentation avec les choix de soit REBUILD ou REORGANIZE. REBUILD ou REORGANIZE est la commande de maintenance de l’index qui peut être exécutée avec l’instruction ALTER INDEX. Les utilisateurs peuvent également exécuter cette commande à l’aide de SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Rechercher et développer la table dans l’Explorateur d’objets >> Ouvrir les index >> Clic droit sur l’index cible >> Reconstruire ou réorganiser.
Comme visible dans l’image ci-dessus, REBUILD et REORGANIZE sont les deux choix disponibles pour jouer l’opération de rognage sur la page. Idéalement, cette opération doit être effectuée en dehors des heures de pointe pour éviter son impact sur les autres transactions et utilisateurs. Microsoft SQL Server Enterprise Edition prend en charge les fonctionnalités d’index en ligne et hors ligne avec l’index REBUILD.
REBUILD INDEX
INDEX REBUILD abandonne toujours l’index et le reproduit avec de nouvelles pages d’index. Cette activité peut être exécutée en parallèle à l’aide d’une option en ligne (Enterprise Edition) avec la commande ALTER INDEX, ce qui n’affecte pas les requêtes et les tâches en cours d’exécution d’une table similaire.
REBUILD L’index peut être défini en ligne ou hors ligne à l’aide des commandes SQL ci-dessous :
|
1
2
3
4
5
|
–Commande de reconstruction de base
ALTER INDEX Index_Nom sur la table_Nom REBUILD
-.-REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Si un utilisateur effectue le REBUILD INDEX hors ligne, alors la ressource objet (Table) de l’index ne sera pas accessible jusqu’à la fin du processus REBUILD. Cela affecte également de nombreuses autres transactions, qui sont associées à cet objet. L’opération Rebuild index recrée l’index. Par conséquent, elle génère de nouvelles statistiques et ajoute également les enregistrements de l’index dans le fichier journal des transactions de la base de données.
Par exemple, avant de reconstruire l’index, prenons l’allocation actuelle de pages pour l’index de la base de données AdventureWorks, la table Sales.OrderTracking et l’index nommé IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_id, previous_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Ici, 1961 pages existent dans le fichier de base de données pour cet index, et les 5 premières pages sont les 861, 862, 1627, 1628, et 1904 dans l’ordre du numéro de page. Maintenant, reconstruisons l’index en utilisant SSMS.
L’opération Index REBUILD est terminée avec succès et prenez à nouveau des références d’allocation de pages pour le même index à l’aide de la même requête T-SQL.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_id,
previous_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Après avoir reconstruit l’index, le nombre de pages rafraîchies est de 1457, ce qui était 1961 auparavant. Si vous vérifiez les 5 premières pages allouées du même index, il a été modifié avec les nouvelles références de page. Cela suppose que l’index a été abandonné et reconstruit une nouvelle fois. Nous devrions vérifier le pourcentage de fragmentation rafraîchi pour le même index, et comme on peut le voir ci-dessous, il est maintenant de 0,1%.
Le REBUILD de l’index clusterisé sur la table affecte également les autres index de la table car le REBUILD de l’index clusterisé reconstruit également l’index non clusterisé de la table. Effectuer l’opération de reconstruction sur tous les index de la table ou de la base de données ensemble ; un utilisateur peut utiliser la commande DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’) ;
|
REORGANIZED INDEX
La commande REORGANIZE INDEX réordonne la page d’index en expulsant l’espace libre ou inutilisé sur la page. Idéalement, les pages d’index sont réordonnées physiquement dans le fichier de données. REORGANIZE ne dépose pas et ne crée pas l’index mais restructure simplement l’information sur la page. REORGANIZE n’a pas de choix hors ligne, et REORGANIZE n’affecte pas les statistiques par rapport à l’option REBUILD. REORGANIZE effectue toujours en ligne.
Par exemple, avant d’effectuer le REORGANIZE sur l’index, prenons la lecture de fragmentation pour la base de données ‘AdventureWorks’, la table ‘Sales.OrderTracking’ et l’index nommé ‘IX_OrderTracking_SalesOrderID’.
Ici, le pourcentage de fragmentation de l’index est de 98,39 avant REORGANIZE. La liste ci-dessous dans l’image est les pages d’allocation à l’index.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_id,
previous_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Ici, un total de 459 pages est listé dans l’image ci-dessus, et les cinq premières pages sont 1065, 1068, 1069, 1944, et 1945. Maintenant, exécutons la commande REORGANIZE sur l’index en utilisant l’instruction T-SQL ci-dessous et regardons à nouveau l’allocation des pages.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Ici, le nombre total de pages est diminué à 331, alors qu’il était de 459 auparavant. De plus, nous ne voyons pas de nouvelles pages dans la liste des cinq premières pages, ce qui implique que les données sont juste restructurées – pas rechargées à nouveau. Même vous pourriez voir de nouvelles pages aussi, cela se produit dans la situation où le grand index est fortement fragmenté, et le remaniement sur les données utilisent une nouvelle page.
Pour effectuer l’opération d’index REORGANIZE sur tous les index de la table ou de la base de données ensemble, l’utilisateur peut utiliser la commande DBCC INDEXDEFRAG() :
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’) ;
|
Comme on le voit, il existe une différence substantielle entre le REBUILD et le REORGANIZE d’index. Ici, les utilisateurs ont le choix de choisir l’une des alternatives en fonction du pourcentage de fragmentation de l’index. Nous pouvons comprendre qu’il n’y a pas de normes documentées ; cependant, l’administrateur de base de données suit l’équation standard selon l’exigence de la taille de l’Index et le type d’information.
Détermination habituelle de l’utilisation de l’équation :
- Lorsque le pourcentage de fragmentation est entre 15-30 : REORGANIZE
- Lorsque la Fragmentation est supérieure à 30 : REBUILD
L’option REBUILD est plus utile avec l’option ONLINE lorsque la base de données n’est pas disponible pour prendre la maintenance de l’index dans les heures creuses.
Conclusion
La Fragmentation de l’index est une fragmentation interne dans le fichier de données. Les paramètres fondamentaux de la performance rapide de votre base de données sont l’architecture de la base de données, la conception de la base de données et l’écriture des requêtes. Une bonne conception d’index avec une maintenance booste toujours les performances des requêtes dans le moteur de la base de données.
- Auteur
- Postes récents
Voir tous les messages de Jignesh Raiyani
- Espérance de vie des pages (PLE) dans SQL Server – 17 juillet, 2020
- Comment automatiser le partitionnement des tables dans SQL Server – 7 juillet 2020
- Configuration des groupes de disponibilité Always On de SQL Server sur AWS EC2 – 6 juillet 2020
.