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.