Skip to main content
Post a question

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id : bu0Asm9ySs50zflLALHoSg
Finance | Project Operations, Human Resources, ...
Unanswered

TempDB column with no statistics causes purchase invoice posting is slow

Like (2) ShareShare
ReportReport
Posted on 17 Mar 2025 10:43:55 by 10
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.
Categories:
  • CU13031351-0 Profile Picture
    10 on 18 Mar 2025 at 10:11:25
    TempDB column with no statistics causes purchase invoice posting is slow
    Hi AndrĂ©
     
    Thanks for responding
     
    1.You mentioned that invoices gets updated using a batch job. Is this invoice by invoice or is the job running for multiple invoices in one run?

    Invoice by invoice,only one invoice is posted at the same time.

    2.You mentioned: "when posting is stuck". What do you mean by that? Do you get time-out errors somewhere? Do you see a head blocker in the activity monitor? 
    What other tasks might run at the same time?
     
    I mean that t-sql mentioned above will run for a long time,and it is not blocked by other processes,no time-out errors,no head blocker,finally, it will be completed.The reason why it runs for that long is because the warning of the query plan(column with no statistics on DEPENDENTSOURCEDOCUMENTLINE column ) causes it to generate a wrong query plan, but I just don't know why that warning is generated.
  • AndrĂ© Arnaud de Calavon Profile Picture
    292,997 Super User 2025 Season 1 on 17 Mar 2025 at 18:18:07
    TempDB column with no statistics causes purchase invoice posting is slow
    Hi,
     
    You mentioned that invoices gets updated using a batch job. Is this invoice by invoice or is the job running for multiple invoices in one run?
    You mentioned: "when posting is stuck". What do you mean by that? Do you get time-out errors somewhere? Do you see a head blocker in the activity monitor? 
    What other tasks might run at the same time? Have you also checked some other SQL performance counters?

     

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... 292,997 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,827 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Product updates

Dynamics 365 release plans
Loading complete