Sql-server – Improve performance of sys.dm_db_index_physical_stats

dmvindexmaintenanceperformancequery-performancesql server

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys.dm_db_index_physical_stats.

Is there any way to speed up the following query:

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS TableIndexName
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat 
    INNER JOIN sys.indexes i 
        ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id 
WHERE 
    phystat.avg_fragmentation_in_percent > 20 
    AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC

I'm not a DBA and could be making an obvious mistake in the query above, or maybe there are some indexes or statistics that would help? Maybe it's just the size of the database (around 20Gb with about 140 tables).

The reason I ask is that we only have a very small window for maintenance during the night and this is taking up most of the time.

Best Answer

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by:

  • how big your database is
  • how fast your IO subsytem is to read the entire database
  • aditional concurent load competing for the IO throughput

Basically, if all you have is a straw (your IO throughput) it takes 30 minutes to drink a bucket (your database size). Buy faster IO, reduce the size of your data, or use SAMPLED scans.

That being said... 20Gb is quite small. 30 minutes to read 20Gb is a lot of time. Is you IO subsystem that slow? Did you deploy on 7200 RPM consumer 1TB drives?

Related Question