[SQL Server] Index/DMV-DMF : détermination du niveau de fragmentation des indexes

Ce billet présente la DMF sys.dm_index_physical_stats (qui remplace DBCC SHOWCONTIG) et en quoi elle peut être utile pour la détermination du niveau de fragmentation des indexes.

Vous pouvez jeter un coup d’œil ici, pour en savoir plus sur l’architecture d’un index (niveaux, pointeurs,…) et la fragmentation.

Rappel sur la fragmentation d’un index

Pour paraphraser une partie du billet relatif aux indexes, tout index peut être fragmenté, que ce soit faiblement, moyennement ou fortement. En effet, plus il est utilisé, plus son taux de fragmentation logique augmente.

Il existe 2 types de fragmentation :

  • La fragmentation externe (ou logique) : intervient quand, au sein d’un index, une page au niveau feuille n’est pas dans un ordre logique. En d’autres termes : la fragmentation externe se manifeste quand l’ordre logique d’un index (i.e., tri des pages) ne correspond pas à son ordre physique (sur le disque). Cela pousse SQL Server a multiplier les opérations I/O pour retourner un résultat ordonné. De manière générale, ce type de fragmentation n’est pas un problème pour les requêtes qui tournent peu de résultats, et ne nécessitant pas un résultat ordonné. C’est pour cette raison que les index en cluster sont plus efficaces que ceux non-clusters pour retourner un très grand nombre de résultats.
  • La fragmentation interne : intervient quand il y a trop d’espace disponible au sein des pages d’un index. Bien que cela puisse être un avantage pour les requêtes DML (Data Modification Language), cela prolongerait la durée d’exécution des requêtes de lecture (étant donné les lectures supplémentaires que doit réaliser SQL Server pour retrouver un ensemble de données correspondant au résultat recherché), sans parler du fait qu’il y a risque potentiel d’augmentation… de la volumétrie de l’index. Par contre, s’il n’y a pas assez d’espace (ou plus du tout), les requêtes DML causeront des splits de pages ce qui monopoliserait également des ressources systèmes additionnelles. C’est pour cette raison que le fill factor joue un rôle important pour tout index.

La réorganisation d’indexes, moins consommatrice en ressources, est plus adaptée dans le cas d’un index faiblement (5%) ou moyennement (=<30-40%) fragmenté tandis que la reconstruction d’indexes, plus consommatrice en ressources, est plus adaptée dans le cas d’un index fortement fragmenté (>30-40%).

Du point de vue des bonnes pratiques, lancer une opération de défragmentation n’est pertinent que si, en plus de tenir compte des recommandations du paragraphe précédent, il y a au moins 1000 pages fragmentées par index traité. En fait, la fragmentation des petits indexes peut, souvent, ne pas être corrigées. En effet, les pages des petites indexes sont stockées sur des extents mixtes pouvant être partagés par, jusqu’à, 8 objets différents. C’est également pour cette raison qu’il est plus pertinent de se concentrer sur les indexes avec au moins 1000 pages (vous pouvez jeter un coup d’oeil ici : http://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/).

Utilisation de la DMF sys.dm_db_index_physical_stats

Présentation rapide de la DMF

La DMF sys.dm_index_physical_stats a pour but de récupérer les informations d’un index (et – exceptionnellement – d’une table en heap). Il fonctionne ainsi en scannant les pages d’un index, suivant 3 méthodes :

  • LIMITED à Mode de scan le plus rapide, il permet à SQL Server de récolter les informations relatives à la fragmentation logique au niveau feuille, ainsi que le nombre de pages. Dans les faits, il se focalise sur le scan des pages d’index situées au-dessus du niveau feuille et, pour calculer le niveau de fragmentation logique au niveau feuille, utilise les pointeurs (ou références de clés de pages) idoines à partir des pages de niveau supérieur afin de déterminer s’ils sont ordonnés ou non. A noter qu’en ce qui concerne une table en heap :
    • Sous SQL Server 2005, toutes les pages de ladite table sont scannées dans le mode LIMITED.
    • A partir de SQL Server 2008, seules les pages PFS et IAM de ladite table sont traitées.
  • SAMPLED à Mode de scan permettant à SQL Server de récupérer les statistiques d’un index ou d’un heap basé sur un échantillon d’1% de toutes leurs pages. A noter que si l’index (ou heap)
    traité possède moins de 10 000 pages, SQL Server optera automatiquement pour le mode DETAILED.
  • DETAILED àMode de scan le plus lent, il permet de scanner toutes les pages d’un index ou d’un heap, de façon à pouvoir récupérer toutes leurs statistiques.

La DMF supporte 4 valeurs de paramètres, dont voici leur présentation :

Paramètre Description Remarque
database_id Permet de spécifier l’identifiant de la base de données à traiter. Par défaut, la valeur est à 0 (ou NULL), ce qui signifie que la DMF traitera toutes les bases de données de l’instance.
object_id Permet de spécifier l’identifiant d’une table ou d’une vue à traiter. Par défaut, la valeur est à 0 (ou NULL), ce qui signifie que la DMF traitera toutes les tables et vues de database_id. Si database_id est à 0 (ou NULL), il doit en être de même pour object_id.
index_id Permet de spécifier l’identifiant d’un index à traiter. Par défaut, la valeur est à 0 (ou NULL), ce qui signifie que la DMF traitera tous les indexes d’object_id de database_id. Si database_id est à 0 (ou NULL), il doit en être de même pour object_id et index_id.
partition_number Permet de spécifier l’identifiant de la partition d’un index ou heap à traiter. Par défaut, la valeur est à 0 (ou NULL), ce qui signifie que la DMF traitera toutes les partitions d’index_id d’object_id de database_id. Si database_id est à 0 (ou NULL), il doit en être de même pour object_id, index_id et partition_id.
mode Permet de spécifier le mode de scan de pages d’indexes (et heap) souhaité. Par défaut, la valeur est à NULL, ce qui signifie que le mode de scan choisi sera LIMITED. La valeur DEFAULT est équivalente à NULL, et donc à LIMITED.

Exemple d’implémentation de la requête DMF de détection des indexes fragmentés

La requête ci-dessous liste (de manière détaillée) les indexes de la base de données exemple AdventureWorks, leur type, leur table d’origine, leur nombre de pages et leur niveau de fragmentation.

SELECT DB_NAME(dps.database_id) AS database_name ,
 OBJECT_NAME(dps.object_id) AS table_name ,
 si.name AS index_name ,
 dps.index_type_desc AS index_type ,
 dps.avg_fragmentation_in_percent ,
 dps.page_count ,
 dps.fragment_count ,
 dps.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'), NULL,
 NULL, NULL, 'DETAILED') dps
 INNER
JOIN sysindexes si ON dps.object_id = si.id
 AND dps.index_id = si.indid
WHERE dps.avg_fragmentation_in_percent >= 5
ORDER BY dps.avg_fragmentation_in_percent DESC
GO

Exemple de résultat :


Interprétons maintenant l’utilité de chaque colonne relative à la fragmentation :

  • avg_page_fragmentation_in_percent à Fournit, en pourcentage, le niveau de fragmentation logique d’un index et, dans le cas d’un heap, le niveau de fragmentation des extents. Moins la valeur est proche de 100, mieux c’est en termes d’ordonnancement des pages.
  • avg_page_space_used à Fournit, en pourcentage, le niveau de fragmentation interne, et donc la densité des pages d’un index (ou heap). Il s’agit de déterminer jusqu’à quel point chaque page d’un index (ou heap) est plein. Plus la valeur est élevée, mieux c’est en termes des performances en lecture, mais moins au niveau des écritures (insertions,…, ce qui peut paradoxalement augmenter le niveau d’avg_page_fragmentation). L’idéal serait de ne pas avoir une valeur trop proche de 100 (afin de mieux favoriser les performances en écriture), mais suffisamment élevée (pour les lectures).
  • page_count à Fournit le nombre de pages traitées d’un index (ou heap). La fragmentation d’un index (ou heap)
    n’est, là, souvent significative que s’il y a plus de 1000 pages.
  • fragment_count à Fournit le nombre de pages fragmentées d’un index (ou heap).
    Notons que le nombre maximal de pages fragmentées d’un index est équivalent à son nombre de pages au niveau feuille.
  • avg_fragment_size_in_pages à Fournit la volumétrie des pages fragmentées. Plus la valeur est élevée, moins d’activités I/O sont nécessaires pour la lecture du même nombre de pages.
Vous pouvez également, avec la DMF, récupérer d’autres informations subsidiaires comme le nombre total d’enregistrements d’un index ou heap (record_count), le nombre d’enregistrements fantômes prêts à être supprimés par SQL Server dans l’unité d’allocation (ghost_record_count), le niveau actuel de l’index (index_level, avec 0 pour le niveau feuille et supérieur à 0 pour les niveaux intermédiaires ou root, seulement traités dans le mode DETAILED),…

Pour aller plus loin…

Comme évoqué
ici
, il existe 2 solutions permettant de réduire le niveau de fragmentation d’un index :

  • La réorganisation d’indexes, qui consiste à défragmenter à chaud les pages d’un index (au niveau feuille) en les réorganisant physiquement afin qu’elles puissent correspondre à l’ordre logique des nœuds feuilles, de gauche à droite. Cela compacte également les pages d’indexes en se basant sur la valeur du fill factor.
  • La reconstruction d’indexes, qui consiste à recréer l’index traité.

Dans le cas d’une fragmentation interne (i.e., densité des pages), vous pouvez, si possible, ajuster la valeur de fill factor des indexes.

Vous pouvez aller ici, pour avoir un exemple basique de mise-en-place d’un plan de maintenance incluant des optimisations d’indexes. Et également là : http://msdn.microsoft.com/en-us/library/ms189858.aspx.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s