SQL Server : contrôler la fragmentation des index
Deux requêtes pour lister la fragmentation des index de la base courante, avec une lecture rapide puis une lecture par échantillon.
Deux requêtes pour contrôler la fragmentation des index de la base courante, à partir de sys.dm_db_index_physical_stats. La première liste la fragmentation de tous les index. La seconde ne lit que le niveau feuille des tables utilisateur, à partir d'un échantillon, et ajoute l'espace utilisé par page, le facteur de remplissage et la taille de l'index.
Fragmentation de tous les index
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
Niveau feuille, par échantillon
SELECT Coalesce(Object_Schema_Name(indexes.object_id) + '.', '')
+ Coalesce(Object_Name(indexes.object_id) + '/', '')
+ Coalesce(indexes.name, 'Heap'),
Str(avg_fragmentation_in_percent, 10,1) AS [avg_fragmentation_%],
Str(avg_page_space_used_in_percent, 10,1) AS [avg_page_space_used_%],
fill_factor,
Str((avg_record_size_in_bytes * record_count) / (1024.0 * 1024), 10,2) AS [IndexSize_(MB)]
FROM sys.dm_db_index_physical_stats(Db_Id(), NULL, NULL, NULL, 'Sampled') AS IPS
INNER JOIN sys.indexes
ON indexes.index_id = IPS.index_id AND indexes.object_id = IPS.object_id
WHERE ObjectProperty(indexes.object_id, 'IsUserTable') = 1 AND index_level=0 --leaf level
ORDER BY [avg_fragmentation_%] DESC;
Remarques
- Avec le paramètre
NULLpour le mode, la première requête utilise le mode par défaut (LIMITED), plus rapide mais moins précis queSampled, utilisé dans la seconde. - La seconde requête affiche
Heappour les tables sans index clustered.