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.
http://Code.msdn.com/dynamicsperf Downloadable article