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.

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.

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.

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.

Except that DMV is not a view into memory (as most of them are), but a way to go and read the non-leaf levels of every table in the DB and calculate results based off that. Hence, the larger the DB and the slower the IO subsystem, the slower that query will run