I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. When I do a select from sys.dm_db_index_physical_stats (Limited) on this table it takes ages (> 4-5 hours). Is there a faster and better way to check the fragmentation levels on the partitions of this table, the current time this takes is completely inacceptable.


Dont run the DMV for whole database instead run it for particular table or index . With database being huge in size it is bound to take time.

You must read Paul Randal's Explanation about why this DMV could take more time.

The idea of the DMV is to display physical attributes of indexes (and the special case of a heap) - to do this it has to scan the pages comprising the index, calculating statistics as it goes. Many DMVs support what's called predicate pushdown, which means if you specify a WHERE clause, the DMV takes that into account as it prepares the information. This DMV doesn't. If you ask it for only the indexes in the database that have logical fragmentation > 30%, it will scan all the indexes, and then just tell you about those meeting your criteria. It has to do this because it has no way of knowing which ones meet your criteria until it analyzes them – so can't support predicate pushdown.

Did you tried Ola Hallengren solution for index rebuild.

There are various modes you can try as mentioned in his article. But as you said limited also took 4-5 hours I guess this is what DMV is. Its actually slow and even MS believes the same about it.

Index rebuild is considered as maintenance activity and should be done when load on server is relatively less or during maintenance window.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.