SQL Server 2008 script pour reconstruire tous les index pour toutes les tables sur toutes les bases de données
J'ai régulièrement les mêmes questions autour des index et leur fragmentation sur SQL2008. J'ai donc décidé d'ajouter un post de plus sur Internet pour discuter de la reconstruction et la réorganisation de l'index et comment détecter la fragmentation des indexes.
Entrée
Comme la fragmentation peut avoir un impact négatif sur l'efficacité de l'accès aux données, l'une des principales tâches d'un DBA est de maintenir les index de bases de données.
Dans le cycle de vie d'une base de données, la fragmentation est un comportement attendu et naturel. Si la base est fréquemment mise à jour via un INSERT, UPDATE ou DELETE nous pouvons nous attendre à sa fragmentation dans le temps.
Plat principal
Depuis la version SQL Server 2005, la fonction de gestion dynamique sys.dm_db_index_physical_stats retourne les informations de taille et de fragmentation pour les données et les index de la table ou la vue spécifiée.
| NB: Bien que SQL Server 2008 prend toujours en charge la commande DBCC SHOWCONTING de SQL Server 2000, cette fonctionnalité sera supprimée dans une version future de SQL Server. Alors, je vous invite à le supprimer dans vos nouveaux développements. |
1. Il existe 3 modes dans la syntaxe sys.dm_db_index_physical_stats:
| LIMITED (defaut): | Ce mode est le plus rapide car il scanne un plus petit nombre de pages. Il analyse toutes les pages d'une Heap, mais ne scanne les pages de niveau parent, ce qui signifie, les pages au-dessus du niveau feuille, pour un index. |
| SAMPLED | Ce mode retourne des statistiques de base sur un échantillon d'un pourcentage de toutes les pages dans l'index ou de la heap. Si l'index ou la heap possede moins de 10 000 pages, le mode DETAILED est utilisé au lieu du SAMPLED. |
| DETAILED | Ce mode scanne toutes les pages et retourne toutes les statistiques. Soyez vigilant, de LIMITED à SAMPLED à DETAILED, les modes sont progressivement plus lent, car plus travail effectué. Dans mon script j'utilise celui-ci |
2. Il y a 2 types de fragmentation sur les indexes.
| Logical fragmentation (indexes) | c'est le pourcentage d'index qui correspond aux pages out-of-order dans les pages feuilles. Une page non ordonnée est une page pour laquelle la page physique suivante allouée dans un IAM n'est pas la page désignée par le pointeur de page suivante dans la page feuille actuelle. |
| Extent fragmentation (heap) | c'est le pourcentage dans une heap d'extents out-of-order dans les pages de la feuille. Un extent est non ordonné lorsque l'extent de la page en cours pour une heap n'est pas, physiquement, l'extent suivant l'extent contenant la page précédente. |
Afin de réduire la fragmentation, vous devez réorganiser ou reconstruire l'index. Choisir entre la réorganisation et la reconstruction dépend de la valeur de fragmentation. Le niveau de fragmentation d'un index ou d'une heap est indiqué dans la colonne avg_fragmentation_in_percent. La valeur doit être aussi proche que possible de zéro. Une valeur comprise entre 5-30% indique une fragmentation modérée, tandis que toute valeur de plus de 30% indique une fragmentation élevée.
Le champs avg_page_space_used_in_percent est une autre valeur qu'il vaut la peine d'examiner de près. Cette valeur représente la quantité d'espacement utilisés dans les indexes. Une valeur inférieure à 75% est habituellement associé à la fragmentation interne (plus de pages vierges sur notre livre que recommandé).
Dessert
Le script fonctionnera sur les versions SQL Server 2008 et supérieur. Le but est de réduire la fragmentation des index en les recréant, réorganisant ou en reconstruisant les indexes :
TELECHARGER LE SCRIPT D'EXEMPLE ICI
Pour lancer la procédure stockée sans exécuter réellement le rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1
Pour lancer la procédure stockée et défragmenter les indexes : EXECUTE handdleFragmentationIndexes
Pour lancer la procédure stockée et défragmenter les indexes uniquement sur un base de données spécifique : EXECUTE handdleFragmentationIndexes @databaseName = 'myDatabaseName'
Le script est extrait en partie de la documentation suivante BOL : sys.dm_db_index_physical_stats (Transact-SQL)
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |