Hi All, I need a query to find index fragmentation for ONLY one index. I know the below query for one table: **SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N' '), OBJECT_ID(N' '), NULL, NULL , 'DETAILED')** As the table is very big, when I used Management Studio to find fragmentation for the specific index, it hangs...any solution???
The third parameter there is the IndexId for the table. If you supply that it will only return values for one index. However, that's not the issue. 'Detailed' is the issue. This launches a scan of the indexes on the table. Try changing it to SAMPLED. If that's too slow, try LIMITED. You won't get as accurate numbers, but you also won't cause the system to hang.