Dynamics AX in the Field

Microsoft Dynamics AX from the Premier Field Engineering team at Microsoft.

How Preallocated Number Sequences are Cached in AX

How Preallocated Number Sequences are Cached in AX

  • Comments 3

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:

  1. Sales orders are set to preallocate by 10 and the next number is SO-00010
  2. User A launches a client session and attempts to create Sales orders
  3. The system preallocates 10 numbers in memory (SO-00010-SO-00019)
  4. User A creates three Sales orders and the system creates them as SO-00010, SO-00011, SO-00012
  5. User B launches a client session and attempts to create Sales orders
  6. The system preallocates 10 numbers in memory (SO-00020-SO-00029)
  7. User B creates one Sales order and the system creates this as SO-00020
  8. User A creates another Sales order and the system creates as SO-00013
  9. User A logs out of its client session
  10. User A launches a new client session and attempts to create Sales orders
  11. The system preallocates 10 numbers in memory (SO-00030-SO-00039)
  12. User A creates one Sales order and the sytem creates it as SO-00030

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.

    RUN_NAME

    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:

    SELECT RUN2.[DATABASE_NAME],

    RUN2.[DATAAREAID],

    RUN2.[NUMBERSEQUENCE],

    RUN2.[TXT],

    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,

    RUN2.[FORMAT],

    RUN2.[CONTINUOUS],

    RUN2.[FETCHAHEAD],

    RUN2.[FETCHAHEADQTY]

    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

    DATABASE_NAME

    DATAAREAID

    NUMBERSEQUENCE

    TXT

    ELAPSED_HOURS

    TOTAL_NUMBERS_CONSUMED

    HOURLY_CONSUMPTION_RATE

    FORMAT

    CONTINUOUS

    FETCHAHEAD

    FETCHAHEADQTY

    Production

    03

    Inv_114

    Inventory Order

    24

    250

    10

    IO########

    0

    0

    0

    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

Page 1 of 1 (3 items)