Blog > Avril 2018 > Afficher la fragmentation des index d'une base de données
Afficher la fragmentation des index d'une base de données


Requête permettant d'afficher la fragmentation des index d'une base de données.
SELECT 
	S.[name]					AS 'Schema',
	T.[name]					AS 'Table',
	I.[name]					AS 'Index',
	ROUND(P.[avg_fragmentation_in_percent],0)	AS '%',
	P.[page_count]					AS 'Nombre de pages'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS P
INNER JOIN sys.tables	AS T	ON T.[object_id] = P.[object_id]
INNER JOIN sys.schemas	AS S	ON T.[schema_id] = S.[schema_id]
INNER JOIN sys.indexes	AS I	ON I.[object_id] = P.[object_id] AND P.[index_id] = I.[index_id]
WHERE P.[database_id] = DB_ID()
ORDER BY P.[avg_fragmentation_in_percent] desc