I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. When I do a select from sys.dm_db_index_physical_stats (Limited) on this table it takes ages (> 4-5 hours). Is there a faster and better way to check the fragmentation levels on the partitions of this table, the current time this takes is completely inacceptable.


Dont run the DMV for whole database instead run it for particular table or index . With database being huge in size it is bound to take time.

You must read Paul Randal's Explanation about why this DMV could take more time.

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.

Did you tried Ola Hallengren solution for index rebuild.

There are various modes you can try as mentioned in his article. But as you said limited also took 4-5 hours I guess this is what DMV is. Its actually slow and even MS believes the same about it.

Index rebuild is considered as maintenance activity and should be done when load on server is relatively less or during maintenance window.


I made a test today after reading this post. I have a very fragmented table on SQL Server 2019.

The Table itself is 3GB and the index is 6GB. I inspected 1 table only with the query:

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
     INNER JOIN sys.indexes i ON(ips.object_id = i.object_id)
                                AND (ips.index_id = i.index_id)
    WHERE OBJECT_NAME(ips.OBJECT_ID) = 'MyTableName'
ORDER BY avg_fragmentation_in_percent DESC;

It took 01:18h on a test database which was not under stress.

So, yes, sometimes it can be extremely slow


Try this:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Credit: https://www.se.com/ww/en/faqs/FA234246/

  • This takes 1 second for my DB. Disclaimer: I'm not sure what you mean about 'partitions' but this gives a single average fragmentation for each table with number of pages. – Simon_Weaver Dec 4 '20 at 3:15
  • Adding fill_factor, is_padded, is_disabled to the columns may help too (or * to see everything) – Simon_Weaver Dec 4 '20 at 3:16
  • WARNING: This ended up getting much slower! It has its good days and bad days for sure but right now it's timing out every day (Azure relatively cheap database) – Simon_Weaver Mar 4 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.