dm query taking long time

I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.

use MyDatabase

GO

select db_name(database_id) as 'Database', o.name as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

Alright Chap check this out.

Your dev server has 18 user databases and the

sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED')

checks all the databases.

replace the first NULL with the DBID of the database mydatabase and try running it again.

Jag

|||Thanks. So, I can consider this to be normal behavior?

I reduced the number of user databases from 18 to 9. Now it runs in about 1 second. (Same query, I haven't yet made the change you suggested.) I think its safe to say that this query does not scale!
|||In 'DETAILED mode the dmv will read all pages that are used in a database. In the query that you specified, you indicated that you wanted to walk though all the pages in all the databases that were available on the system. If you have one big database with a lot of data, this command will take a long time because of all the I/O.

If you want faster (but less detailed) results, you can use 'LIMITED' mode.

Thanks,