Intermittent Inventory On Hand Performance Issues
A few customers have reported intermittent performance issues involving inventory on hand related queries on the InventSum, InventDim and/or WHSInventReserve tables, due to bad query plans regarding the table joins. This not only applies to on hand inquiries necessarily, but anything else dependent on it, e.g. inventory related posting processes, e.g. purchase invoicing. So based on this I am posting some generic solutions you can implement and review which may enable you to provide some relatively quick wins.
This assumes you already implemented all of the best practice recommendations for AX performance. You can find a good summary of articles regarding best practices and what to check here:
My colleague Claes Norell has also written a couple of blog posts focusing on some key settings, including a SQL script to check them:
Consider implementing the following hotfixes (the first two being dependent on your inventory model):
KB 2951209 PORT request DAXSE 1014828: Standard Cost Revaluation reopens closed InventSum records.
KB 3007375 Over layer issue on menu Inventory and Warehouse Management caused by DAXSE 3577412 – Performance – Standard Cost Rounding checks are causing significant performance issues.
KB 3063513 Large number of records in WHSINVENTRESERVE that have all Qty fields = 0 – A new clean up job called “Warehouse management on-hand entries cleanup” has been added under the Inventory management module under Periodic > Clean up. The clean-up job will delete the records in the InventSum and WHSInventReserve table for closed on-hand entries. The impact of running this clean up on a regular basis is significant performance improvements of On-hand related inquiries.*
KB 3112873 This fix optimizes the sp_WHSOnHand stored procedure to use better query plans and make sure it is only called when absolutely necessary.
After implementing the above hotfixes (if relevant), we advise to run the following purging jobs for the InventSum and InventDim tables, especially if the volume records in one or both of those tables is high.
- Inventory And Warehouse Management/Periodic/Clean up/On-hand entries aggregation by financial dimensions (for InventSum)
- Inventory And Warehouse Management/Periodic/Clean up/On-hand entries cleanup (for InventSum)
- Inventory And Warehouse Management/Periodic/Clean up/Inventory dimensions cleanup (for InventDim)
Please see the following blog post for additional information regarding these jobs:
*If you’re using the new warehouse management module, the following blog post is worth reading:
Indexing
Previously the following index suggestion has helped other customers (which should be created in the AOT in AX, however the SQL script is given here to illustrate).
Create Index [Index name] On INVENTSUM (ITEMID, CLOSED, DATAAREAID, PARTITION)
Index Maintenance: It is recommended to ensure a plan is in place for regular index maintenance. You may wish to consider the suggestions in the blog post below.
Database Maintenance Strategies for Dynamics AX
ALTER INDEX (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188388.aspx
Possible workarounds to consider for specific scenarios if there are still issues with on hand related queries:
**Disclaimer:
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This blog post assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.
Plan Guides / Query Hints
Plan guides provide the option to add query hints to the queries from AX without needing to make any changes in the application itself, such as OPTION (RECOMPILE) and OPTIMIZE FOR. However the statement must exactly match the query being executed for the plan guide to be used. For example:
sp_create_plan_guide
@name = N’InventSumPlan’,
@stmt = <enter SQL statement here>
@type = N’SQL’,
@module_or_batch = NULL,
@params = <enter SQL statement parameter,
@hints = N’OPTION (RECOMPILE)’;
Of course in stored procedures like sp_WHSOnHand (used by the new warehouse management module), it’s possible to do this by modifying the procedure itself. At least one customer was known to benefit from using (OPTIMIZE FOR (@ItemId = ‘’’’)) as a query hint in this case. A couple of others benefited from replacing the joins between WHSInventReserve/InventDim in the same stored procedure with INNER LOOP JOIN, which directs the query optimizer in SQL to use nested loop joins.
Further details:
Plan Guides
https://msdn.microsoft.com/en-us/library/ms190417.aspx
Query hints
https://msdn.microsoft.com/en-us/library/ms181714.aspx
Nested Loop Joins (in relation to the INNER LOOP JOIN mentioned above)
https://technet.microsoft.com/en-us/library/ms191318(v=sql.105).aspx
Code workarounds
If you are able to identify the call stacks (e.g. using Trace Parser, the SQL statement trace log or the Performance Analyzer (”DynamicsPerf”)) and the above solutions don’t help in specific scenarios, there are some keywords you could consider using in the code (instead of using plan guides), e.g. forceNestedLoop, forceSelectOrder and as a last resort, forceLiterals. Further details:
Select Statement Syntax [AX 2012]
https://msdn.microsoft.com/en-us/library/aa656402.aspx
I am providing below an example with the InventSum::findSum method of how force literals could be used as a code workaround in individual call stacks to generate a specific plan for each set of query parameters, thereby avoiding parameter sniffing and related poor query plan issues.
\Data Dictionary\Tables\InventSum\Methods\findSum
/****Please note above disclaimer****
…
else
{
switch (_sumFields)
{
case InventSumFields::Financial:
select forceLiterals sum(PostedQty),sum(PostedValue)
// <GEERU>
, sum(PostedValueSecCur_RU)
// </GEERU>
, sum(PdsCWPostedQty)
from inventSum
where inventSum.ItemId == _itemId &&
inventSum.Closed == NoYes::No
#inventDimExistsJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);
break;
case InventSumFields::Physical:
select forceLiterals sum(PostedQty),sum(PostedValue),sum(Received),sum(Deducted),sum(Picked),sum(Registered)
// <GEERU>
, sum(PostedValueSecCur_RU)
// </GEERU>
, sum(PdsCWPostedQty)
, sum(PdsCWReceived)
, sum(PdsCWDeducted)
, sum(PdsCWPicked)
, sum(PdsCWRegistered)
from inventSum
where inventSum.ItemId == _itemId &&
inventSum.Closed == NoYes::No
#inventDimExistsJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);
break;
default:
select forceliterals
#inventSumFields
from inventSum
where inventSum.ItemId == _itemId &&
inventSum.Closed == NoYes::No
#inventDimExistsJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);
…
Generally speaking, there are a few ways of implementing force literals, depending on how the query is implemented, including:
- As a keyword in a select statement, e.g.:
…select forceLiterals sum(PostedQty),sum(PostedValue)
// <GEERU>
, sum(PostedValueSecCur_RU)
// </GEERU>
, sum(PdsCWPostedQty)
from inventSum…
- After instantiating QueryRun, e.g.:
…
QueryRun queryRun = new QueryRun (query);
QueryRun.Literals(1);
…
- After instantiating the Query, e.g.:
…
Query query = new Query();
Query.Literals(1);
…
- In the properties of a static query, e.g.:
