patternsqlMinor
sys.dm_db_index_physical_stats is extremly slow
Viewed 0 times
dm_db_index_physical_statssysslowextremly
Problem
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#76374, answer score: 9
Revisions (0)
No revisions yet.