We have a batch job to invoice purchase order one by one, most invoices are posted without problem, but sometimes post one invoice will take more than 10 or 20 minutes(just only less than 20 lines in that invoice ).
When the posting is stuck , sql server show it is caused by the following T-SQL
UPDATE T1 SET ACCOUNTINGEVENT=T2.ACCOUNTINGEVENT,ACCOUNTINGDATE=T2.ACCOUNTINGDATE,RECVERSION=1498852052 FROM tempdb."DBO".t104750_DCB0C6D4D15B47298255F46F02E2B8AC T1 CROSS JOIN ACCOUNTINGDISTRIBUTION T2 WHERE (T1.PARTITION=5637144576) AND ((T2.PARTITION=5637144576) AND ((T2.SOURCEDOCUMENTLINE=T1.DEPENDENTSOURCEDOCUMENTLINE) AND (T2.REFERENCEROLE<>1))) AND NOT (EXISTS (SELECT 'x' FROM ACCOUNTINGDISTRIBUTION T3 WHERE ((T3.PARTITION=5637144576) AND ((T3.REFERENCEDISTRIBUTION=T2.RECID) AND (T3.SOURCEDOCUMENTLINE=T1.DEPENDENTSOURCEDOCUMENTLINE)))))
Click on query plan , i saw one warning ,column with no statistics on DEPENDENTSOURCEDOCUMENTLINE column
because of this, seek predicates of T2 table only have partition field ,this will read all of the data from ACCOUNTINGDISTRIBUTION ,it's a wrong plan.
then ,use DBCC show_statistics('t104750_DCB0C6D4D15B47298255F46F02E2B8AC','DependentSourceDocumentLine'), to check statistics, only one row in statistics.
try update statistics manually, update statistics tempdb."DBO".t104750_DCB0C6D4D15B47298255F46F02E2B8AC with fullscan , this time result was updated to 11 rows.
Seems that statistics has not been updated correctly, but if i kill process from sql server and repost that invoice again , it would be able to post without problem.
I also found this piece of T-SQL in ax. /Class/SubledgerJournalRelievingMatchingList/updateAccountingInformationForEntries, don't know if anyone experienced the same problem.
We are using AX2012 R3 CU12 ,SQL server 2016 sp1.
