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:
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.
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
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
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