Je vous propose dans cet article une méthode pour analyser l’état de fragmentation des bases de données SQL Server.

En effet, dans le cadre de la maintenance des index, la défragmentation est un point sensible, surtout avec les bases de données pour lesquelles le “Recovery Model” est paramétré comme complet.

Exécuter cette requête sur la base de données que vous souhaitez analyser :

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()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Voici un exemple de résultat avec en surbrillance des index très fragmentés :

interface hl7 radiologie

Voici comment interpréter le résultat :

Il faut tenir compte de la colonne « avg_fragmentation_in_percent » mais aussi de la colonne « page_count ».
Les « best practice » recommandé par Microsoft :
•    Si page_count < 1000 : Pas besion de faire quoi que ce soit car l’index est tellement petit que ce n’est pas nécessaire de faire une action de maintenance dessus.
•    Si page_count > 1000 ET

  • Si avg_fragmentation_in_percent < 30% = REORG index suffisant
  • Si avg_fragmentation_in_percent > 30% = REBUILD index nécessaire