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.

http://dynamicsperf.codeplex.com.  Downloadable article