question

Mandar Alawani avatar image
Mandar Alawani asked

Find index fragmentation for only 1 index

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???
sql-server-2008indexingindexesfragmentation
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani avatar image Mandar Alawani commented ·
Thank you very much!!...I dont think so , still one question, is there a way to do the LIMITED setting in Management Studio (Indexname --right click --- )
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Which command are you running that shows the physical stats from Management Studio? I don't see one.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.