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:
SELECT OBJECT_NAME(ips.OBJECT_ID), i.NAME, ips.index_id, index_type_desc, avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count 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
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count 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