Number sequences handle automatic allocation of voucher numbers, invoice numbers, and journal numbers used by Microsoft Dynamics AX. You can set the allocation to be Continuous, or Preallocate a block of numbers. When using Continuous, gaps are not allowed, so a trip to the database is needed to get the next number. When preallocating, a block of numbers are stored in memory, thus reducing the number of trips. This is important for the overall performance of the system; however, it is important to understand how these numbers are cached in a multi-user system.
Number sequences are stored on the client and not shared among user sessions, thus, each client/user session draws its own set of numbers. If the client/user session logs out, then a new set of numbers are pulled, but if they stayed logged in they will continue to use the first set of numbers that were allocated to that client/user session. For example:
In most cases, using preallocated number sequences is good practice of less trips to the database, but if the risk of using too many number sequences is not a good trade off for increased performance then you should reconsider.
Thanks for the nice post...
Yeah would be a point to optimize..but using this pre-allocation for number sequence like LotId, ParmId would be more meaningful. Since using it for SO and PO may be confusing for the numbers.
In the article, How Preallocated Number Sequences are cached in AX ; we discuss setting up pre-allocation of number sequences in Dynamics AX. One of the difficulties in configuring pre-allocation is determining what to configure the value to. The Performance Analyzer for Microsoft Dynamics tool is capable of determining how many numbers are being consumed per Number Sequence.
Once Performance Analyzer 1.0 for Microsoft Dynamics is setup, you can determine the consumption rate per number sequence; this requires that a minimum of two data captures be completed. To review all the data captures that have been collected, run the following in the DYNAMICSPERF database:
--To find run_name run the following query
SELECT * FROM STATS_COLLECTION_SUMMARY
Once you have reviewed the collections, pick 2 RUN_NAME’s that have a time difference between them that is significant enough to account for differences in day to day operations.
May 18 2011 5:00PM
May 18 2011 5:34PM
May 18 2011 9:04AM
May 18 2011 9:34AM
May 18 2011 12:31PM
May 19 2011 5:00PM
In this example, May 18 2011 5:00PM to May 19 2011 5:00PM would be 24 hours of activity in the database. Once you have determined the values you can run the following query replacing the RUN_NAME value with the 2 from your previous query:
Datediff(hh, RUN1.STATS_TIME, RUN2.STATS_TIME) AS ELAPSED_HOURS,
RUN2.NEXTREC - RUN1.NEXTREC AS TOTAL_NUMBERS_CONSUMED,
( RUN2.NEXTREC - RUN1.NEXTREC ) / ( Datediff(hh, RUN1.STATS_TIME, RUN2.STATS_TIME) ) AS HOURLY_CONSUMPTION_RATE,
FROM AX_NUM_SEQUENCES_VW RUN1
INNER JOIN AX_NUM_SEQUENCES_VW RUN2
ON RUN1.NUMBERSEQUENCE = RUN2.NUMBERSEQUENCE
AND RUN1.DATAAREAID = RUN2.DATAAREAID
WHERE RUN1.RUN_NAME = 'Jan 9 2011 5:00PM'
AND RUN2.RUN_NAME = 'Jan 10 2011 5:00PM'
ORDER BY 6 DESC
The column HOURLY_CONSUMPTION_RATE is the value that will help you determine which Number Sequences to pre-allocate and how large of a value to configure it for.
Both of these queries can be found in the Performance Analyzer for Microsoft solution. In the solution file, go to Scripts Dynamics AX | Queries | DynamicsAX Performance Queries.sql.
code.msdn.com/dynamicsperf Downloadable article
In the article, How Preallocated Number Sequences are cached in AX ; we discuss setting up pre-allocation