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.