October 9, 2019 at 9:34 pm
Hi, Developer complain that in dynamic AX production ,application query execution time degrade
runs 3 minutes or more or " never finish "
SELECT
SUM(T2.POSTEDQTY),
SUM(T2.DEDUCTED),
SUM(T2.RECEIVED),
SUM(T2.RESERVPHYSICAL),
T2.ITEMID,
T3.INVENTSITEID,
T3.INVENTLOCATIONID
FROM INVENTTABLE ~ 59987 row
T1 CROSS JOIN INVENTSUM ~ 1411479 rows
T2 CROSS JOIN INVENTDIM ~ 9628399 rows
T3 CROSS JOIN PDSBATCHATTRIBUTES T4 ~15243327
WHERE
(((T1.PARTITION=8888888) AND (T1.DATAAREAID=N'myCompany')) AND ((((((T1.PMFPRODUCTTYPE=5) AND (T2.AVAILPHYSICAL>0)) AND (T3.INVENTSTATUSID='aval01')) AND NOT ((T3.INVENTLOCATIONID='RD01'))) AND (T4.PDSBATCHATTRIBID='Ressource')) AND (T4.PDSBATCHATTRIBVALUE='D001'))) AND (((T2.PARTITION=8888888) AND (T2.DATAAREAID=N'myCompany')) AND (T1.ITEMID=T2.ITEMID)) AND (((T3.PARTITION=8888888) AND (T3.DATAAREAID=N'myCompany')) AND (T2.INVENTDIMID=T3.INVENTDIMID)) AND (((T4.PARTITION=8888888) AND (T4.DATAAREAID=N'myCompany')) AND ((T2.ITEMID=T4.ITEMID) AND (T3.INVENTBATCHID=T4.INVENTBATCHID)))
GROUP
BY T2.ITEMID,T3.INVENTSITEID,T3.INVENTLOCATIONID
ORDER BY T2.ITEMID,T3.INVENTSITEID,T3.INVENTLOCATIONID
Query return 303 rows
1.Database set with auto update stat ON
2. REORG all db done daily at at 3 am (m. plan)
3. stat update all db done daily at 4 am (m. plan)
When I executed query at 4 pm , it runs for 3.5 minutes (query plan show only index seeks)
but if run from SSMS
UPDATE STATISTICS dbo.PDSBATCHATTRIBUTES
UPDATE STATISTICS dbo.INVENTTABLE
UPDATE STATISTICS dbo.INVENTSUM
UPDATE STATISTICS dbo.INVENTDIM
and re execute query again it return 303 rows in less then 1 second!
1.Why with auto update stats on and daily update stat job statistic is not up to date ?
2.Why if force update stat on particular tables I get fast result ?
3.Would be wise to run update stat on those tables from SQL jobs every n minutes ?
Nobody of cause want to modify code or change batch processing schedule 🙂
Thank you
October 10, 2019 at 12:19 am
Auto update stats may not be executing often enough due to the size of the tables. There is a trace flag that could help your situation on SQL Server 2014. It's the new default behavior in SQL Server 2016. It's something you want to test in a lower environment first. The following article explains this change with the statistics update threshold:
Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
Also read the link to the other article in that post. It explains things further and consider the issue with asynchronous statistics update -
Default auto statistics update threshold change for SQL Server 2016
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply