Dynamics AX in the Field

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

Performance Analyzer for Dynamics

Performance Analyzer for Dynamics

  • Comments 8

Our Dynamics Services team has been doing Performance services for customers over the past 10 years.  We've learned a lot in that time - what information to collect, what key pieces of data to focus on and how to analyze all this data.   Over the evolution of SQL Server and our products, we've used a number of different sources to gather data.  SQL Profiler was and still is a true method of finding out what's going on in SQL, but we also moved on to incorporate system information through HTA scripts as well.  With the release of SQL 2005, we started gathering information from the Data Management Views (DMVs) as there is a lot of great information available there (since your latest SQL restart).

We have partnered with the Dynamics AX product team and the experts over at the Performance team (their blog is at http://blogs.msdn.com/axperf) as they have built a means to gather information about the AX configuration and export that information to the SQL database. The genesis of this tool was with the AX team, but it also includes Dynamics CRM, GP, SL and NAV scripts that are useful in troubleshooting performance issues in those products. In fact, Michael DeVoe from our team recently wrote up a post on the Dynamics NAV blog: http://blogs.msdn.com/b/nav/archive/2010/11/04/performance-analyzer-1-0-for-microsoft-dynamics.aspx

To run the Performance Analyzer for Dynamics, you are going to need the following:

  • Ability to create a new database on the production SQL Server (DynPerf is the database that is generated)
  • Read access to the AX database
  • Write access to the DynPerf database
  • Admin permissions to each of the AOS servers connected to the AX database

You can download the Performance Analyzer for Dynamics from the MSDN Code Gallery. Here's the link to the download: http://code.msdn.microsoft.com/DynamicsPerf


There is an installation guide within the .zip file, so when you download the tool, be sure to take a minute to review the Install Guide to go through the proper steps to get the SQL Solution deployed and to collect the AX information in the database.

We are working with the product teams to release updates to the Performance Analyzer on a quarterly basis as we come up with more valuable information to collect.  The Code Gallery site will be updated with the latest version, so check back there to find the latest and greatest.

The Performance Analyzer for Dynamics is the tool the Premier Field Engineering, Product and Support teams use when doing troubleshooting performance and when conducting the Performance Review and Performance Hands on Lab services. The ability to gather pertinent information quickly allows us to spend more time providing suggestions for how to remedy the performance issues the customer is experiencing.

  • In the article, How Preallocated Number Sequences are cached in AX ; we discuss setting up pre-allocation

  • 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

  • We're less than four weeks away from Convergence 2011 - it is coming up quick. Convergence 2011 will

  • We are getting close to the release of the Version 1.10 for the Performance Analyzer for Dynamics , and

  • We are getting close to the release of the Version 1.10 for the Performance Analyzer for Dynamics , and

  • Our Premier Field Engineers travel a lot. When customers need assistance during deployments or need a

  • We did another podcast to provide some more background on the Performance Analyzer for Microsoft Dynamics

Page 1 of 1 (8 items)