Report 795 Adjust Cost - Item Entries performance issues

We have been recently seeing some performance issues with the notorious Adjust Cost - Item Entries batch job. Currently we have a customer who has NAV 2015 with  a serial number tracking set to a couple of items, which is normally no issue at all. Upgrade to later version, probably Business Central is already scheduled, but this issue had to be resolved ASAP.

The problem is that the customer can have up to 80.000 serial numbers on one batch of arriving items.

The items Costing Method is set to Standard to reduce the need for adjustments on item costs. Sometimes the customer anyways wants to assign costs to these items, and that causes records in table 5804 Avg. Cost Adjmt. Entry Point which is the table for Report 795 when it searches for non-applied costs for item entries. Adjust Item Costs batch job is segmented to run one item Product Group at a time. The night only has like 6 or 7 hours efficient time frame for Adjust Item Cost batch job, and now this time has been too short for the job to run. Luckily they have very efficient hardware, SQL has loads of RAM and processors are the best ones I have seen in my NAV career. They also have like 6 load balancing servers for NAV services to even up the load from different stores and integrations.

The customer has now millions of entries in table 32 Item Ledger Entry. Also, the customer requires that SQL server is set to "Always Row-Lock".

Well, this is not good. Millions of item ledger entries and tens of thousands of ledger entries for one item, and one assignment of item costs. This causes a lot of rowlocks wen you adjust the item costs. We monitored the queries, and found out that after some 10.000 handled item ledger entries the filtering to find the correct Item Ledger entry could take up to 4 seconds, even though the search used optimal key and only one row was returned.

The solution was easy enough; Create a scheduled SQL job that turns off the "Always Rowlock" for the duration of the nightly Adjust batch job, and another scheduled SQL job to turn it back on after, say, 5 AM or so.

Problem solved!

Comment List