InventDim and InventSum causes slow when browsing SO List Form

(0) Share

Report

Posted on by Dave.KWOK

Hi there,

I have a scenario as below since last monday.

1. There is a heavy wait hours on this query
SELECT SUM(T1.POSTEDQTY),
   SUM(T1.POSTEDVALUE),
   SUM(T1.PHYSICALVALUE),
   SUM(T1.DEDUCTED),
   SUM(T1.RECEIVED),
   SUM(T1.RESERVPHYSICAL),
   SUM(T1.RESERVORDERED),
   SUM(T1.REGISTERED),
   SUM(T1.PICKED),
   SUM(T1.ONORDER),
   SUM(T1.ORDERED),
   SUM(T1.ARRIVED),
   SUM(T1.QUOTATIONRECEIPT),
   SUM(T1.QUOTATIONISSUE),
   SUM(T1.AVAILPHYSICAL),
   SUM(T1.AVAILORDERED),
   SUM(T1.PHYSICALINVENT),
   SUM(T1.POSTEDVALUESECCUR_RU),
   SUM(T1.PHYSICALVALUESECCUR_RU),
   SUM(T1.PDSCWPOSTEDQTY),
   SUM(T1.PDSCWDEDUCTED),
   SUM(T1.PDSCWRECEIVED),
   SUM(T1.PDSCWRESERVPHYSICAL),
   SUM(T1.PDSCWRESERVORDERED),
   SUM(T1.PDSCWREGISTERED),
   SUM(T1.PDSCWPICKED),
   SUM(T1.PDSCWONORDER),
   SUM(T1.PDSCWORDERED),
   SUM(T1.PDSCWARRIVED),
   SUM(T1.PDSCWQUOTATIONRECEIPT),
   SUM(T1.PDSCWQUOTATIONISSUE),
   SUM(T1.PDSCWAVAILPHYSICAL),
   SUM(T1.PDSCWAVAILORDERED),
   SUM(T1.PDSCWAVAILPHYSICAL),
   SUM(T1.PDSCWAVAILORDERED),
   SUM(T1.PDSCWPHYSICALINVENT)
FROM INVENTSUM T1
WHERE (((T1.PARTITION=5637144576)
AND (T1.DATAAREAID=N'123'))
AND ((T1.ITEMID=@P1)
AND (T1.CLOSED=@P2)))
AND EXISTS
   (
   SELECT 'x'
   FROM INVENTDIM T2
   WHERE (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID=N'123'))
AND ((((T2.INVENTDIMID=T1.INVENTDIMID)
AND (T2.INVENTSITEID=@P3))
AND (T2.INVENTLOCATIONID=@P4))
AND (T2.INVENTBATCHID=@P5)))
   )

2. The impact was on the SO module with following synptoms: -

a. Slow while browsing the SO list from All SO/Open SO
b. Add line still ok but when user confirm order, will hang for a while
c. changing view from line to header also will be slow

3. Upon investigation, what we did to improve the performance was to reindex Inventdim and Inventsum table from System admin--> SQL administration

4. After reindex, it works for a while. Performance back to normal.

5. However after overnight, in the morning, the issue came back and i need to do another round of reindex on these 2 tables.

6. Is it normal that i need to do reindex in a 12 hours period? I found this strange as we have never had this issue before.

Would appreciate someone can share some ideas.

Thanks,

Dave

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Mea_ 60,284 on at

Like (0)

Report

Hi Dave.KWOK,

First of all you should have maintenance plan for AX that includes reindex and statistics update, please read for details

blogs.msdn.microsoft.com/.../database-maintenance-strategies-for-dynamics-ax

It does not sound right for me that you have to update it each 12 h, but what is the level of changes to this 2 tables during that period? Maybe you created or deleted several millions of rows...

Also do you have any details about locks or resources that this query is waiting ?

Was this reply helpful? Yes No
dontran 130 on at

Like (0)

Report

I think you should add index hint into the select query.

Was this reply helpful? Yes No
Welsi Tuesta 85 on at

Like (0)

Report

Hello Steffen Laursen, I have the same problem, how did you solve it?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have same problem also. Does anyone have some solution for this?

Im tired to reindex 2-3 times a day.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

Try and UPDATE STATISTICS INVENTSUM WITH FULLSCAN and same with INVENTDIM;

Index hints no longer work in AX, so don't bother with that.

Are you actually using the InventBatchId inventory dimension?

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report

If you need to reindex too often then you should think about fill factor, it specifies the % of fullness of the leaf level pages of an index, by default sql sets it at 100%, in ax2012 r2 cu7 you can now specify the fill factor of the index, I have myself modify it in some tables that I had to reindex too often (I use a maintenance plan for AX), I suggest you to start at 90% in a test environment so you can check the performance impact, and of course, read about it

To modify the fill factor from AX you have to go to SQL Administration:

Was this reply helpful? Yes No