Steve Malley (10/17/2012)
Since I am only accessing the system catalog and a DMV, I would expect that performance should be consistent and fast. I find that it is not. It may just be the volume of records, but it is a bit weird that these objects are responsive in some environments, but not responsive in others.
http://www.sqlskills.com/blogs/paul/post/Inside-sysdm_db_index_physical_stats.aspx
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.
