Verifier la fragmentation des indexes sur une base de données.

Très souvent, lorsqu’on rencontre des problèmes de performances sur SQL Server, et sur d’autre SGBD, le premier réflexe à avoir est la vérification de la fragmentation des indexes. (on peut aussi regarder qui consomme !)

Le but ici n’est pas de refaire l’explication du fonctionnement des indexes, mais juste de fournir quelques requêtes indispensables aux vérifications.

SELECT dbschemas.[name] as 'Schema',
   dbtables.[name] as 'Table',
   dbindexes.[name] as 'Index',
   indexstats.avg_fragmentation_in_percent,
   indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
   INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
   INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
   INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
   --dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc

Cette requête liste tous les indexes de la base de données en cours, trier par pourcentage de fragmentation. Pour l’exécuter sur une table, dé-commenter la ligne dbtables.[name] like ‘%%’ et ajouter votre table entre les %.

Pour réduire la fragmentation, il faudra ensuite ré-organiser ou reconstruire vos indexes selon les valeurs ci-dessous.

% FragmentationActionCommande SQL
>5 et <30Ré-organiser l’indexALTER INDEX REORGANIZE
>30Re-construire l’indexALTER INDEX REBUILD

Tout ceci peut bien entendu être automatisé via un plan de maintenance. Depuis les dernières versions de SQL, les paramètres de fragmentation, de nombre de page ou de dernière utilisation, permettent de spécifier les critères afin de gagner en efficacité et en rapidité lors de l’exécution de ces derniers. Fini les ré-indexation qui durent des week-end entier ou qui explosent vos journaux de transactions !

En attendant un article sur la réalisation d’un plan de maintenance, voir : ApexSQL.

Comments are closed.