Overview
Incorrectly configuring number sequences within finance and operations apps remains a performance pain point for some customers. The purpose of this blog is to provide guidance on how a customer can determine that number sequence configuration is causing a performance issue and provide guidance on how that can be mitigated by correctly configuring the number sequence.
Incorrectly configured number sequences can cause performance issues during day-to-day processes, this is often seen during large file import processes where the number sequences have been configured for general usage and have not been configured to take into account the large volume of data imported on a regular basis.
Number sequence contention is often seen during data migration as number sequences are often configured for day-to-day usage and changing the configuration for data migration is often overlooked. While number sequences can be set to manual for data migration purposes, in this blog we will focus on preallocation configuration.
Process to identify number sequence contention
To identify if number sequence configuration is causing an issue use Lifecycle Services (LCS) or Query Store to identify the most expensive queries during the period of time you are investigating.
If a lot of time is being spent on the following query, there is a high probability that the number sequence configuration is causing a performance issue.
SELECT TOP 1 T1.ALLOWCHANGEDOWN,T1.ALLOWCHANGEUP,T1.ANNOTATEDFORMAT,T1.BLOCKED,T1.CLEANATACCESS,T1.CLEANINTERVAL,T1.CONTINUOUS, T1.CYCLIC,T1.FETCHAHEAD,T1.FETCHAHEADQTY,T1.FORMAT,T1.HIGHEST,T1.INUSE,T1.LATESTCLEANDATETIME,T1.LATESTCLEANDATETIMETZID, T1.LOWEST,T1.MANUAL,T1.NEXTREC,T1.NOINCREMENT,T1.NUMBERSEQUENCE,T1.NUMBERSEQUENCESCOPE,T1.TXT,T1.DEFAULTRANGESIZEPERDEPLOYMENT, T1.HIGHEST64,T1.LOWEST64,T1.NEXTREC64,T1.EXTENDED,T1.MODIFIEDTRANSACTIONID,T1.RECVERSION,T1.PARTITION,T1.RECID FROM NUMBERSEQUENCETABLE T1 WITH ( UPDLOCK) WHERE ((PARTITION=5637144576) AND (RECID=?))
Alternatively, another way to detect high usage of a number sequence is to compare the “Total Numbers Consumed” (see T-SQL below) before and after a test run, for example, after a data migration test run.
Sometimes internal number sequences, for example, Lot ID, are simply overlooked and configured without preallocation, however some of these internal number sequences can consume a significate volume of numbers.
Process to identify specific number sequence(s) causing contention
Once it has been identified that number sequences are a bottleneck it is necessary to identify which number sequence(s) are causing the issue, this can be done by running the following SQL statements in a sandbox environment that has been refreshed with the database where the contention was identified.
These queries will list the number sequences in descending order based upon the usage of numbers within the sequence that have been consumed. It is arranged in this manner because those sequences that are used most frequently are usually the ones that cause the contention, especially if they have not been configured correctly.
You can use the following query in the AXDB to get an overview of continuous number sequences consumption:
SELECT NST.PARTITION AS [Partition], NSS.DATAAREA AS [DataAreaID], NST.TXT AS [Text], NST.NUMBERSEQUENCE AS [Number Sequence], NST.ANNOTATEDFORMAT AS [Annotated Format], (NST.NEXTREC - NST.LOWEST) AS [Total Numbers Consumed], (NST.HIGHEST - NST.NEXTREC) AS [Remaining Numbers] FROM NUMBERSEQUENCETABLE NST LEFT OUTER JOIN NUMBERSEQUENCESCOPE NSS ON ((NST.PARTITION = NSS.PARTITION) AND (NST.NUMBERSEQUENCESCOPE = NSS.RECID) AND (NSS.DATAAREA != ''))
WHERE NST.CONTINUOUS = 1 -- Only displaying continuous number sequences
AND (NST.NEXTREC - NST.LOWEST) > 0 -- Number sequences that have been used
AND NSS.DATAAREA IS NOT NULL
ORDER BY 6 DESC; -- [Total Numbers Consumed]
You can use the following query in the AXDB to get an overview of non-continuous number sequences consumption:
-- If preallocation = 0 it means that while the number sequence has been setup as non-continuous, preallocation has not been enabled, which indicates potential for optimization (as it is still working similar to the continuous option as that system still has to execute an SQL statement each and every time a number is required, rather than caching a batch of numbers on an AOS for use)
-- If Quantity of numbers = 0 it means that the number sequence won't preallocate any numbers, which indicates potential for optimization (as it is still working similar to the continuous option as that system still has to execute an SQL statement each and every time a number is required, rather than caching a batch of numbers on an AOS for use)
SELECT NST.PARTITION AS [Partition], NSS.DATAAREA AS [DataAreaID], NST.TXT AS [Text], NST.NUMBERSEQUENCE AS [Number Sequence], NST.ANNOTATEDFORMAT AS [Annotated Format], (NST.NEXTREC - NST.LOWEST) AS [Total Numbers Consumed], (NST.HIGHEST - NST.NEXTREC) AS [Remaining Numbers], NST.FETCHAHEAD AS [Preallocation], NST.FETCHAHEADQTY AS [Quantity of numbers] FROM NUMBERSEQUENCETABLE NST LEFT OUTER JOIN NUMBERSEQUENCESCOPE NSS ON ((NST.PARTITION = NSS.PARTITION) AND (NST.NUMBERSEQUENCESCOPE = NSS.RECID) AND (NSS.DATAAREA != ''))
WHERE NST.CONTINUOUS = 0 -- Only displaying noncontinuous number sequences
AND (NST.NEXTREC - NST.LOWEST) > 0 -- Number sequences that have been used
AND NSS.DATAAREA IS NOT NULL
ORDER BY 6 DESC; -- [Total Numbers Consumed]
Configuration of non-continuous number sequences
If the number sequences are continuous, the performance cannot be optimized, and it is recommended to review the underlying business requirement that drove the decision to use continuous number sequences. Number sequences overview - Finance & Operations | Dynamics 365 | Microsoft Learn
A common scenario we see is where users have enabled preallocation however have failed to specify the Quantity of numbers value.
While there are no hard and fast rules for what value should be used for Quantity of numbers, customers can use this table as initial settings for testing purposes.
| Daily number sequence usage |
Quantity of numbers |
| <10,000 |
5 |
| 10,000 < 25,000 |
10 |
| 25,000 < 75,000 |
50 |
| 75,000+ |
100 |
The above guidance is based on daily usage numbers, as data migration and large file imports occur within hours, and often involve a significantly higher number of records, a higher number may be required for Quantity of numbers, if a high value for Quantity of numbers is required for data migration purposes, ensure that you have added a step in your cutover plan to reset this value once data migration has completed.
Summary
In this blog we have outlined how a customer or partner can identify if number sequences are causing a performance issue on their system and steps they can take to configure non-continuous number sequences so as to mitigate the issue.
