Blog > Avril 2018
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