Dear experts,
For past few weeks , client experience AX 2009 application slowness ,freezes and not allow to login to users in some intervals only . Post some analysis, it has been noticed that always SQL database had dead blocks as follows ,which is related to number sequence table.
SELECT TOP 1 A.NUMBERSEQUENCE, A.TXT, A.LATESTCLEANDATETIME, A.LATESTCLEANDATETIMETZID, A.LOWEST, A.HIGHEST, A.NEXTREC, A.BLOCKED, A.FORMAT, A.CONTINUOUS, A.CYCLIC, A.CLEANATACCESS, A.INUSE, A.NOINCREMENT, A.CLEANINTERVAL, A.DEL_LATESTCLEANDATE, A.DEL_LATESTCLEANTIME, A.ALLOWCHANGEUP, A.ALLOWCHANGEDOWN, A.MANUAL, A.FETCHAHEADQTY, A.FETCHAHEAD, A.MODIFIEDTRANSACTIONID, A.RECVERSION, A.RECID
FROM NUMBERSEQUENCETABLE A WITH ( UPDLOCK) WHERE ((DATAAREAID=@P1) AND (NUMBERSEQUENCE=@P2)) )
https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/102526/number-sequence-database-blocking/195823
In this blog , it quoted as Continuous number sequences might cause this issue and suggested to disable Continuous .It is strongly recommend going through the list of numbersequences and disable "Continuous" on all that does not have this strict business requirement. More often, users think the number sequences MUST be Continuous, while it isn't necessarily so. Any continuous numbersequence can cause degraded performance if not used correctly.
But client has continuous number sequence for proforma Invoice, Sales Invoice , Sales delivery note number sequence and as per local regulatory requirements these codes should be continuous only ,but it causes deadlock .
Could any one of guide how to overcome this situation ? Thanks in advance.
