Sql-server – Join on dm_db_index_physical_stats very slow

fragmentationmicrosoft-dynamicsperformancesql serversql-server-2012

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and on SQL Server 2012 SP1.

SELECT TOP 1 A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A 
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C 
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D 
ON B.object_id = D.object_id AND A.index_id = D.index_id

I checked resource monitor in the server for disk activity and the Read(B/sec) shoots up from an initial 23,000 to over 13,000,000 while the query is running and goes back down after the query is cancelled.

Any ideas what may be causing this?

Best Answer

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker.

Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main function with all those NULLs.

Related Question