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)) |
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.
Hi Vihar,
I'm not sure if it helps, but you can try to run number sequence clean-up operation
Hi Sergei,
We have around 1.3 lakh records in NUMBERSEQUENCELIST table
Hi Vihar,
How much records do you have in NUMBERSEQUENCELIST?
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 ?
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,900 Super User 2024 Season 2
Martin Dráb 229,297 Most Valuable Professional
nmaenpaa 101,156