Finance | Project Operations, Human Resources, ...

Number sequence long running queries in AX 2009

(0) Share

Report

Posted on by Community Member 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))

All responses (6)

Answers (0)

Suggested answer

André Arnaud de Cal... 291,431 Super User 2024 Season 2 on at

Like (0)

Report

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 23,089 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

RE: Number sequence long running queries in AX 2009

Hi Sergei,

We have around 1.3 lakh records in NUMBERSEQUENCELIST table
Sergei Minozhenko 23,089 on at

Like (0)

Report

RE: Number sequence long running queries in AX 2009

Hi Vihar,

How much records do you have in NUMBERSEQUENCELIST?
Community Member Microsoft Employee on at

Like (0)

Report

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 23,089 on at

Like (0)

Report

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.