Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Number sequence long running queries in AX 2009

Posted on by Microsoft Employee

Hi ,

we are using AX 2009 application, and randomly we are getting number sequence related long running queries in SQL level which is effecting the performance of the application. Can anyone have idea on why it’s causing and how to find root cause that which operation or which number sequence causes this blocks ?. below are the few example queries which we are having problem.

Session_id

Duration(sec)

Status

CurrentQuery

CompleteQuery

51

2101

suspended

SELECT A.NUM,A.SESSIONLOGINDATETIME,A.SESSIONLOGINDATETIMETZID,A.USERID,A.NUMBERSEQUENCE,A.STATUS,A.SESSIONID,A.TRANSID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.RECVERSION,A.RECID FROM NUMBERSEQUENCELIST A WITH( UPDLOCK) WHERE ((DATAAREAID=@P1) AND (TRANSID=@P2))

(@P1 nvarchar(5),@P2 bigint)SELECT A.NUM,A.SESSIONLOGINDATETIME,A.SESSIONLOGINDATETIMETZID,A.USERID,A.NUMBERSEQUENCE,A.STATUS,A.SESSIONID,A.TRANSID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.RECVERSION,A.RECID FROM NUMBERSEQUENCELIST A WITH( UPDLOCK) WHERE ((DATAAREAID=@P1) AND (TRANSID=@P2))

52

1503

suspended

SELECT A.NUM,A.SESSIONLOGINDATETIME,A.SESSIONLOGINDATETIMETZID,A.USERID,A.NUMBERSEQUENCE,A.STATUS,A.SESSIONID,A.TRANSID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.RECVERSION,A.RECID FROM NUMBERSEQUENCELIST A WITH( UPDLOCK) WHERE ((DATAAREAID=@P1) AND (TRANSID=@P2))

(@P1 nvarchar(5),@P2 bigint)SELECT A.NUM,A.SESSIONLOGINDATETIME,A.SESSIONLOGINDATETIMETZID,A.USERID,A.NUMBERSEQUENCE,A.STATUS,A.SESSIONID,A.TRANSID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.RECVERSION,A.RECID FROM NUMBERSEQUENCELIST A WITH( UPDLOCK) WHERE ((DATAAREAID=@P1) AND (TRANSID=@P2))

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 290,900 Super User 2024 Season 2 on at
    RE: Number sequence long running queries in AX 2009

    Hi Vihar,

    I do agree with Sergei to see if you can cleanup the status list of the number sequences. There were a few issues in AX2009 which caused not cleaning up this table. Also, some documents do not support continuous number sequences. Another cause can be a wrong implementation in customizations.

    The number sequence status list should only have a few records for those numbers which should be re-used. When you perform the clean-up check if records remaining in the list are indeed records for which the number should be used a next time before drawing a new number. All used records should be deleted as well.

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Number sequence long running queries in AX 2009

    Hi Vihar,

    I'm not sure if it helps, but you can try to run number sequence clean-up operation

    docs.microsoft.com/.../number-sequence-overview

    blog.mohamedaamer.net/.../

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Number sequence long running queries in AX 2009

    Hi Sergei,

    We have around 1.3 lakh records in NUMBERSEQUENCELIST table

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Number sequence long running queries in AX 2009

    Hi Vihar,

    How much records do you have in NUMBERSEQUENCELIST?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Number sequence long running queries in AX 2009

    Hi Sergei,

    Thanks for your response and suggestions but we have already implemented jobs to rebuild all indexes and update statistics every weekday and its working fine and as per the business requirement we need Continuous number sequences. So, do we have any other possible ways to overcome the above mentioned issue ?

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Number sequence long running queries in AX 2009

    Hi Vihar,

    NUMBERSEQUENCELIST table is related to a continuous number sequences and I suggest first to understand what kind of process is running in this session and do you really need to use continuous number sequence there. Continuos number sequences are recommended to use only in cases where it's needed by legal requirements because they decrease performance.

    If it's really needed, in this case, could you, please, check how many records do you have in this table? Try to rebuild all indexes in offline mode for this table and update statistics.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans