jeudi 1 janvier 2015

SQL Select top 1 record on dm_db_index_physical_stats with a join on sys.partitions very slow


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 canceled.


Any ideas what may be causing this?





Aucun commentaire:

Enregistrer un commentaire