asked
Fragmentation Script takes long time to execute
I am running below script to find out the fragmentation percentage but its taking very long time for output to come. I have mentioned WITH NOLOCK as well. Database size is around 2 TB. Can someone please help me on this to get the result quickly. select TableName=object_name(dm.object_id) ,IndexName=i.name ,IndexType=dm.index_type_desc ,[%Fragmented]=avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm join sys.indexes i WITH (NOLOCK) on dm.object_id=i.object_id and dm.index_id=i.index_id where avg_fragmentation_in_percent > 30
It's slow because it's examining all the indexes in that database, resulting in a lot of I/O. There's a good explanation by the guy who wrote it here:
http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/
18 People are following this question.
