SELECT db_name(ps.database_id)AS 'Database Name', object_name(ps.OBJECT_ID)AS 'Database Object', ps.index_id, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id WHERE ps.database_id = DB_ID() alttaki koşullari ekleyince sorgulama suresi uzayabilir. ps.index_id > 0 AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30 ORDER BY ps.avg_fragmentation_in_percent DESC
SELECT db.name AS databaseName , object_name(ps.OBJECT_ID, ps.database_id) AS objectName , ps.index_id AS indexID , ps.partition_number AS partitionNumber , ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count FROM sys.databases db INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps ON db.database_id = ps.database_id WHERE ps.index_id > 0 AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30 OPTION (MaxDop 1);
