Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Some users is very slo...
Finance | Project Operations, Human Resources, ...
Answered

Some users is very slow on InventOnhandItem screen

(2) ShareShare
ReportReport
Posted on by Faruk Durak 109

We are using Ax 2012 R3, SQL Server 2016 SP2 is installed.
When users constantly using the InventOnhandItem screen add the InventLocationId field from the dimensions view, the screen runs too slow to be usable. When I try with different users at the same time, it works normally for some users.

Even though I reset all the usage data of the problematic user, I cannot fix the problem. AOS restart does not solve the problem either, I have to restart the complete SQL server. We encounter this problem every 3-4 days. Anyone have a suggestion for a solution?

  • ØB-06020855-0 Profile Picture
    ØB-06020855-0 6 on at
    Some users is very slow on InventOnhandItem screen
    This is probably due to parameter sniffing issue in SQL. Check how the dataskew is in the INVENTDIM table with TSQL:
    /* check num of rows in INVENTDIM for each dataareaid with INVENTLOCATIONID to determine dataskew*/
    SELECT dataareaid, inventlocationid, COUNT(*) AS numof_rows
    FROM dbo.inventdim
    GROUP BY inventlocationid, DATAAREAID
    ORDER BY numof_rows desc;
     
    If the top results show f x millions of rows for one INVENTLOCATIONID and you have just a couple of hundred in others, you're going to have bad query plans cached in SQL when a user queries for INVENTLOCATIONID with low number of rows. This plan is cached starting starting with INVENTDIM as the first table before it joins with INVENTSUM. If the next users searchcriteria is for a location with many million rows, this will take awhile. Re-indexing INVENTDIM and INVENTSUM tables will throw the plan out of cache, same as restart SQL service, or even run DBCC FreeProcCache (dont' do this in production!). If an user now is the first to search for locationid with millions of rows, SQL will create a new plan starting with INVENTSUM before it joins with INVENTDIM and it will work fine for everyone.
    Either create a planguide for this or use LITERALS in X++.
  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    I think I have fixed this problem for sure. On the SQL side, automatic Reorganize and Rebuild Index work for all DBs, but I also manually performed Reorganize and Rebuild Index operations on InventSum, InventTrans and InventDim tables. I don't know what the difference is between automatic and manual action, but this worked.

  • Suggested answer
    Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hello,


    I partially solved the problem. I squashed our screen with a standard InventOnhandItem form in the R2 version and the problem was solved.

    It uses other display methods inside the reservOrdered, availPhysical, reservPhysical display methods in the InventSum table and does a cache operation related to WHS. Our main problem is related to these, but I haven't been able to solve that problem yet. This is an issue with Ax's standard. Maybe CU updates will work, but I'm thinking of going with this solution as I'm worried it will cause different problems.

    If there is a different situation, I will write here again, maybe it will be useful to someone. I wasted a lot of time getting to this stage. I hope that will be useful.

  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi jannawangD365,

    After cleaning, it started again about a month later and is increasing gradually. The problem causing the slowness seems to be something different. Do you have a different suggestion?

  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi jannawangD365,

    That is works for us. Thank you for support.

  • jannawangD365 Profile Picture
    jannawangD365 on at
    RE: Some users is very slow on InventOnhandItem screen

    good to know it's working, Faruk. Cheers.

  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi jannawangD365,

    we got a complaint again in 06 Mar 2021 then I ran first Inventory journal cleanup in the our LIVE Ax. The job complited quicly then I checked the screen but It was still slow. Then I ran On-hand entries cleanup job, I gave 0 as a paremeter. It was finsh about 15 hours and deleted about 800K record, our database decreased by 2 GB, we have ran first time and we have data the total of 4 years. I checked the screen when it was complated, the screen was still slow then I restart the all servers. if we don't get a complaint about slowness in the week, I think it was working.

    pastedimage1615789077334v1.png

  • Verified answer
    jannawangD365 Profile Picture
    jannawangD365 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi Faruk,

    how is the performance now? Is it improved?

  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi jannawangD365,

    Thank you for interest. That is good Idea. I will try it.

  • Faruk Durak Profile Picture
    Faruk Durak 109 on at
    RE: Some users is very slow on InventOnhandItem screen

    Hi Ludwig,

    Thank you for interest. Our Ax Version is R3 CU11. There is a lot of localization development in the General Ledger module. That's why we hesitate to install new CU versions. However, I want to install and try the Fix packages you sent in Test environment.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,245 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,925 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans