Issues that affect performance when you run the Adjust Cost - Item Entries batch job in Microsoft Dynamics NAV 5.0

The first part of this article contains frequently asked questions about setup parameters. The second part of this article contains known performance issues and resolutions when you run the Adjust Cost - Item Entries batch job. Additionally, looping issues may occur if you use the "Average" costing method, and then you run the Adjust Cost – Item Entries batch job to update items that are frequently used in transfer orders. The looping issues may also occur when you consume products that are produced on the same order (carry-over production), or when you frequently use transfer orders. The third part of the article contains information that you should collect when Microsoft SQL Server is the cause of your problem.

Setup parameters

Should I set the "Automatic Cost Adjustment" to "Always"?

Cost adjustments that occur during each incoming posting could affect database performance. The Automatic Cost Adjustment field includes time options so that you can define when an incoming transaction triggers an adjustment of related outgoing value entries.

When you set the automatic cost adjustment time option, you should select an option that balances your cost accuracy requirements with the performance level of your database. When you shorten the time interval, the cost information is less accurate but the database performance during posting is increased.


However, you may not be able to use the automatic cost adjustment time option because system environments are different. Additionally, using the following features may affect database performance when you use the automatic cost adjustment time option:

• Automatic cost posting
• Expected cost posting
• Dimensions
• Analysis views
• Certain costing methods
• Item tracking
• Reservations

Certain tables that are used in the posting process of a shipment are locked for a long time when you run the cost adjustment job. Additionally, some activities of the cost adjustment process may also lock certain tables for a long time. Therefore, you can set the Automatic Cost Adjustment setting to Always.

Which method type should I select for the "Average Cost Calc. Type" setting?

If you change the value in the Average Cost Calc. Type field, all entries concerning items with the "Average" costing method will have to be readjusted.

If the Average Cost Calc. Type setting is changed in Microsoft Dynamics NAV 4.0 or in earlier versions, all entries that use the Average costing method will be included in the Adjust Cost - Item Entries batch job.

If the
Average Cost Calc. Type setting is changed in Microsoft Dynamics NAV 5.0 or in later versions, all entries in the open fiscal year that correspond to items that use the Average costing method will be included in the Adjust Cost - Item Entries batch job.

Note The process to readjust entries may take several hours if your database is large.

When should I run the Adjust Cost - Item Entries batch job?

We recommend that you run the Adjust Cost - Item Entries batch job as frequently as possible during non-working hours. You can run this batch job for specific item or groups. However, we recommend that you run the batch job for all items. Use the filter fields to limit the runtime of this batch job.

What records are in the Avg. Cost Adjmt. Entry Point table (5804) in Microsoft Dynamics NAV 5.0 or in a later version that are included in the Adjust Cost - Item Entries batch job?

Records in the Avg. Cost Adjmt. Entry Point table (5804) that contain the expression "Cost is Adjusted = False" need to be adjusted. Before the cost is adjusted, these records indicate that Microsoft Dynamics NAV needs to update the average cost for items in the period ending that contain the valuation date in the entry. Additionally, items that use costing methods other than the "Average" costing method may need updating.

Before the cost is adjusted, an entry in this table indicates that Microsoft Dynamics NAV must update the average cost for entries for the item in the period ending that contain the valuation date in the entry.

What keys may be set incorrectly in the Value Entry Table (5802)?

If additional keys are enabled or if existing keys are disabled in the Value Entry Table (5802), you may have performance issues when you run the Adjust Cost-Item Entries batch job. You can evaluate if the key settings affect the performance issue by comparing the key settings in the Value Entry Table (5802) in your database with the key settings in the Value Entry Table (5802) in the default Cronus database.

What is the effect on performance for certain tables when I increase the number of users and the volume of data in the Item Ledger Entry table (32) or Value Entry table (5802)?

Increasing the number of users and the volume of data may affect the performance of the Item Ledger Entry Table (32) or the Value Entry Table (5802).

What changes in the database environment would affect performance?

• Upgrades to the database.
• Customizations to the database.
• Add-in installed to the database.

Resolutions for performance problems and looping problems when you run the Adjust Cost – Item Entries batch job

You may experience performance issues when you run the Adjust Cost - Item Entries batch job for an item that uses the "Average" costing method and that has been used in transfer orders

Each outgoing item ledger entry has to be adjusted using the average cost of the period. This adjustment takes a long time if each entry invokes the method that performs the calculation. After you apply the appropriate hotfix, the method that calculates the average cost for the outgoing ledger entry is invoked only if it is necessary.

To resolve these issues, apply one of the following hotfixes:

• For Microsoft Dynamics NAV 4.0 Service Pack 3 (SP3), apply hotfix 978606.
  
  For more information about hotfix 978606, click the following article number to view the article in the Microsoft Knowledge Base:
  978606 You experience slow performance when you run the "Adjust Cost - Item Entries" batch job to adjust average cost items that were used in transfer orders in Microsoft Dynamics NAV 4.0 with Service Pack 3
  
  
  
  
  Prerequisites:
  
  • 925862
  • 978603
  • 978605
• For Microsoft Dynamics NAV 5.0, apply hotfix 975240.
  
  For more information about hotfix 975240, click the following article number to view the article in the Microsoft Knowledge Base:
  975240 The value in the Adjmt. Level field keeps increasing and the entry number in the Entry No. field loops when you run the Adjust Cost - Item Entries batch job in Microsoft Dynamics NAV 5.
  
  
• For Microsoft Dynamics NAV 5.0 SP1, apply hotfix 975251.
  
  For more information about hotfix 975251, click the following article number to view the article in the Microsoft Knowledge Base:
  975251 You experience slow performance when you perform the Adjust Cost batch job for the average cost in Microsoft Dynamics NAV 5.0
  
  

Note These resolutions are already included in Microsoft Dynamics NAV 2009 SP1.

Looping issues that are caused by a "carry-over production" scenario when you run the Adjust Cost - Item Entries batch job

The value in the Adjmt. Level field continues to increase, and then the entry number in the Entry No. field switches between two entry numbers or a group of entry numbers when you run the Adjust Cost – Item Entries batch job.

If this problem occurs, apply one of the following hotfixes:

• For Microsoft Dynamics NAV 4.0 Service Pack 3 (SP3), apply hotfix 978702.
  
  For more information about hotfix 978702, click the following article number to view the article in the Microsoft Knowledge Base:
  978702 The "Adjust Cost – Item Entries" batch job goes into an endless loop on a finished production order whose production process consumes an average cost item and ends up with an output in the same day in Microsoft Dynamics NAV 4.0 with S978702
  
  
  
  
  Prerequisites:
  978606
• For Microsoft Dynamics NAV 5.0, apply hotfix 975240.
  
  For more information about hotfix 975240, click the following article number to view the article in the Microsoft Knowledge Base:
  975240 The value in the Adjmt. Level field keeps increasing and the entry number in the Entry No. field loops when you run the Adjust Cost - Item Entries batch job in Microsoft Dynamics NAV 5.
  
  
• For Microsoft Dynamics NAV 5.0 SP1, apply hotfix 976339.
  
  For more information about hotfix 976339, click the following article number to view the article in the Microsoft Knowledge Base:
  976339 The value in the "Adjmt. Level" field keeps increasing and the entry number in the "Entry No." field switches between two or a group of entries when you run the "Adjust Cost - Item Entries" batch job in Microsoft Dynamics NAV 5.0 with Service Pack
  
  
  
  
  Prerequisites:
  975251

Note These resolutions are already included in Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2009 SP1.

What information should I collect when Microsoft SQL Server is causing performance problems when you run the Adjust Cost – Item Entries batch job?

Microsoft SQL Server may cause performance problems when you run the Adjust Cost – Item Entries batch job. If this problem occurs, collect the following information before submitting a support incident.

• Specify the settings for "Lock Timeout" and "Always Rowlock."
  
  To verify these settings in Microsoft Dynamics NAV Application Server (NAS), point to Database on the
  File menu, click Alter, click the
  Advanced tab, and then capture a screenshot.
• The size of the Microsoft SQL Server database file.
• Your table names and other related information.
  
  To collect this information, follow these steps:
  
  1. Start the Microsoft Dynamics NAV Classic client.
     
  2. On the File menu, point to
     Database, and then click Information.
     
  3. Click Tables, and then copy and paste the contents of the form to a spreadsheet.
• The build number of the version of Microsoft Dynamics NAV that you are running.
  
  Note This information determines whether you have the latest Microsoft SQL Server performance-related hotfixes.
• The version number of Microsoft SQL Server that you are running.
  
  To find this information, execute a command. To do this, follow these steps:
  
  1. Start the Support Administrator Console, Microsoft SQL Query Analyzer, or SQL Server Management Studio. To do this, use one of the following methods depending on the program that you are using.

     Method 1: For SQL Server Desktop Engine

     If you are using SQL Server Desktop Engine (also known as MSDE 2000), start the Support Administrator Console. To do this, click
     Start, point to All Programs, point to
     Microsoft Administrator Console, and then click
     Support Administrator Console.

     Method 2: For SQL Server 2000

     If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.

     Method 3: For SQL Server 2005

     If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

     Method 4: For SQL Server 2008

     If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
     
  2. Run the following command: SELECT @@VERSION
• Specify that you use the "Lock Timeout" setting or the "Always Rowlock" setting.
  
  To verify these settings in Microsoft Dynamics NAV Application Server (NAS), point to Database on the File menu, click Alter, and then click the Advanced tab.
• Your table names together with other related information.
  
  To do this, follow these steps:
  
  1. Start the Microsoft Dynamics NAV Classic client.
  2. On the File menu, point to
     Database, and then click
     Information.
  3. Click Tables and then copy and paste the contents of the form to a spreadsheet.
• User workloads when users run the Adjust Cost – Item Entries batch job.
  
  To do this, run the following statement:
  exec sp_who2
• Processes that are running when users run the Adjust Cost – Item Entries batch job.
  
  To do this, run the following statement:
  select * from master..sysprocesses
• The Microsoft SQL Server error log files. These text files are usually stored in the following location: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG.
  
  Note The location may be different depending on which installation method you previously selected.
• Diagnostic information.
  
  To do this, follow these steps:
  
  1. Use an account that is a member of the Administrator group to open a command prompt on the computer that is running Microsoft SQL Server.
  2. Run the following command: sqldiag /I sd_general.xml /X
  3. Note the folder path that follows the "SQL Diag Output path:" string.
  4. In the folder that you noted in step 3, compress only the files that were generated in step 2.