Effect of force update stat on tables used by query when db auto update stat on

  • ebooklub

    Hall of Fame

    Points: 3873

    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

     

     

     

     

     

     

     

     

  • Sue_H

    SSC Guru

    Points: 90260

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply