Tâches hebdomadaire du DBA : Fragmentation des index

Après un premier billet au sujet d’une des tâches quotidienne du DBA au sujet de l’espace libre dans une base de donnée voici le post inaugural des tâches hebdomadaire : la fragmentation des index …

Vos chers amis développeurs ont probablement pensé à mettre des index sur les champs appropriés (clés étrangères, champs pour des conditions where, order by et autre Joins …) et ne manquent pas de vous rappeler qu’ils ont fait leur part de travail…

Et vous chers confrères DBA, avez-vous l’esprit tranquille ? Yes, of course, vous avez planifié des réorganisation d’index et des reconstructions d’index à intervalles réguliers. J’en suis sûr. Mais il ne sert à rien non plus de réindexer tous les jours, ni même toutes les semaines. Tout dépend de l’activité de votre base de données.

Et oui, un index est un être vivant génétiquement non modifié qui évolue dans le temps. Au fur et à mesure, les opérations INSERT, UPDATE, DELETE vont mettre à mal le beau classement de votre index. Index cluster ou index non cluster, même combat, à quelques particularités près. On va se retrouver avec des emplacements libres dans nos chères pages d’index, quelques pages splits sont intervenus … Bref, est-ce que mon index ressemble à un vrai morceau de gruyère et a besoin d’une cure de jouvence ou bien peut-on se satisfaire de sa condition actuelle et repousser à plus tard l’opération de maintenance.

Car c’est bien là que se situe de nerf de la guerre. L’index est fort utile afin d’accélérer bon nombre de requêtes, mais lorsque l’on veut intervenir dessus, l’impact en terme de performance sur le serveur est vraiment non négligeable. Sans compter avec les verrous posés pour sa reconstruction … Même si de gros progrès ont été fait avec l’option ONLINE sur la version Enterprise de SQL Server, cela reste néanmoins assez pénalisant.

La question est donc : dois-je réindexer maintenant ?
Pour ma part, je me suis construit une petite requête TSQL qui comme d’habitude satisfait à mon besoin. Libre à vous de l’amender comme bon vous semble.

WITH ReallyUsedIndexes AS
(
    SELECT top 100 i.id,i.name,i.indid,CAST((8* i.reserved)/1024 AS VARCHAR) + ‘ Mo’ AS Taille,i.rows,
          [user_seeks] ,[user_scans] ,[last_user_seek] ,[last_user_scan]
    FROM sysindexes i
    INNER JOIN sys.dm_db_index_usage_stats ius ON i.id = ius.object_id AND i.indid = ius.index_id
    WHERE i.dpages > 0 AND
          ( ius.user_seeks > 2 OR
            ius.user_scans > 2 )
    ORDER BY i.dpages DESC
)
SELECT
       OBJECT_SCHEMA_NAME(SDDIPS.[object_id]) + ‘.’ + OBJECT_NAME(SDDIPS.[object_id]) AS [object_name],
       SI.[name] AS index_name,
       SDDIPS.[index_type_desc], SDDIPS.[avg_fragmentation_in_percent],
       ReallyUsedIndexes.Taille, ReallyUsedIndexes.rows,
       ReallyUsedIndexes.[user_seeks] ,ReallyUsedIndexes.[user_scans] ,
       ReallyUsedIndexes.[last_user_seek] ,ReallyUsedIndexes.[last_user_scan],
       ‘ALTER INDEX ‘ + SI.[name] + ‘ ON ‘ + OBJECT_SCHEMA_NAME(SDDIPS.[object_id]) + ‘.’ + OBJECT_NAME(SDDIPS.[object_id]) + ‘ REBUILD WITH (ONLINE =  ON) ‘ as Alter_Index
    FROM sys.[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, ‘Detailed’) SDDIPS
       INNER JOIN sys.[indexes] SI ON SDDIPS.[object_id] = SI.[object_id]AND SDDIPS.[index_id] = SI.[index_id]
       INNER JOIN ReallyUsedIndexes ON ReallyUsedIndexes.id = SI.[object_id]AND ReallyUsedIndexes.indid = SI.[index_id]
    WHERE SDDIPS.[avg_fragmentation_in_percent] > 15
       AND SDDIPS.[page_count] > 2
       AND SDDIPS.[index_id] > 0
    ORDER BY OBJECT_SCHEMA_NAME(SDDIPS.[object_id]), OBJECT_NAME(SDDIPS.[object_id]);

Il n’y a rien de très compliqué. Je commence par chercher les 100 index les plus lourds de la base sur laquelle je suis connecté. Mais je ne sélectionne que les index qui sont réellement utilisés pour ne garder que ceux qui ont été scannées ou parcourus en totalité depuis le dernier démarrage de l’instance. Pourquoi limiter à 2 le nombre de seek ou de scan ? Je veux exclure le cas où un index sert 1 fois pour une requête ad hoc qui a peu de chance de repasser avant une nouvelle apparition de la comète de Halley (en 2061 pour ceux que ça intéresse). A vous donc de modifier ce chiffre pour obtenir ce que vous voulez.
Je vais ensuite chercher la fragmentation de chaque index, son nom et je créé l’ordre SQL de REBUILD. Pourquoi le REBUILD et non pas le REORGANIZE, tout simplement parce que j’ai filtré pour une fragmentation supérieure à 15% Si elle avait été inférieure à 15% une réorganisation aurait suffi. Ca vous donne des idées pour faire une V2 de cette requête.

Voici à quoi ressemble le résultat :

image

Je vous épargne les copies d’écran Excel ou SSRS mais je suis sûr que Sébastien se fera un plaisir de proposer une belle interface en WPF qui affiche de beaux camemberts, de beaux graphes, de faire clignoter les valeurs vraiment trop élevées et un bouton pour lancer l’exécution de l’ALTER INDEX …

Maintenant il suffit de triturer ce petit jeu de données pour savoir quels sont les index fréquemment utilisés et qui nécessitent une ré indexation … A utiliser aussi lorsque l’on vient vous informer qu’une application connait des ralentissements : une requête de ce style en conjonction avec une petite Trace dans le Profiler SQL et quelques minutes plus tard, tout est revenu à la normale.

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

Un commentaire pour Tâches hebdomadaire du DBA : Fragmentation des index

  1. Denis Bernard dit :

    Pas mal du tout, seulement, il faut ajouter un filtrer sur le DB_ID() dans le with pour éviter les doublons (1 Object ID peut être utilisé dans 2 DB diffèrent sur la même instance) . Ça donne ca :

    WITH ReallyUsedIndexes AS
    (
    SELECT TOP 100 i.id,i.name,i.indid,CAST((8* i.reserved)/1024 AS VARCHAR) + ‘ Mo’ AS Taille,i.rows,[user_seeks] ,[user_scans] ,[last_user_seek] ,[last_user_scan]
    FROM sysindexes i
    INNER JOIN sys.dm_db_index_usage_stats ius ON i.id = ius.object_id AND i.indid = ius.index_id and
    WHERE i.dpages > 0 AND
    ius.database_id = DB_ID() AND
    ( ius.user_seeks > 2 OR
    ius.user_scans > 2 )

    )

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