Please be sure to install the core components of Performance Analyzer for Microsoft Dynamics before completing this guide.
Performance Analyzer is delivered as a SQL Server solution file and includes a set of SQL jobs, X++ classes, VB scripts, and performance counters to initiate the collection process. It also includes a set of sample SQL scripts that can be used to query and analyze the populated tables and views in the DynamicsPerf database.
The Performance Analyzer is delivered as a SQL Server solution and consists of a number of collectors as SQL jobs, X++ classes, VB scripts, and performance counters. These collectors that make up the Performance Analyzer are categorized within this document as the following:
• Capture AOT Metadata
• Capture AOS Settings and Event Logs
We will discuss each one of the collectors specific to Dynamics AX in the following sections and the process for deploying and maintaining Performance Analyzer in later sections.
There are several steps that need to be completed in order to successfully deploy Performance Analyzer for Microsoft Dynamics AX. The Performance Analyzer is meant to be deployed and set up for data collection on a continual basis throughout the life of your AX system. This ensures that if performance issues arise, you are able to quickly identify the bottleneck as well as use for comparison purposes. You must have completed the steps in the Performance Analyzer for Dynamics Deployement and User Guide – Core Installation before completing the following steps.
The following is a summarized checklist of the steps to deploy Performance Analyzer. See the steps below for detailed information.
Enable Long Running Query Capture for AX (AX)
Configure and Schedule AOT Metadata Capture (AX)
Configure and Schedule AOS Configuration and Event Logs Capture
Configure and Schedule Performance Counter Logging on Database Server
Configure and Schedule Performance Counter Logging on AOS Server(s)
Before you deploy Performance Analyzer, you must complete the following:
1. Extract the DynamicsPerfxxx.zip file to a location to where you can browse from the database and AOS servers
2. Ensure you have rights to create new databases on the database server
3. Ensure you have read access to the AX database
4. Ensure you have write access to the DynamicsPerf database (this database gets created as part of Performance Analyzer)
5. Ensure you have Admin permissions to each of the AOS servers connected to the AX database
6. Ensure you have created a local folder on the database server called \SQLTRACE to store the trace files that get generated
7. Ensure that every active AOS server in the AX instance has been started with the ‘Allow client tracing on Application Object Server instance’ checkbox enabled (2009 only step)
If using Dynamics AX, you can set thresholds which capture long running queries from AX source code.
In the following steps you will configure the system to capture long running queries from AX source code. The data collected will be stored in the DynamicsPerf database.
NOTE: This enables long duration tracing for all AX users by updating the USERINFO table and sets the long running query threshold to 5000ms. The ‘Allow client tracing on Application Object Service instance’ checkbox on the AOS Server Configuration Utility for each AOS Server must be marked before executing this stored procedure.
1. Please ensure step 7 in the “Before you begin” section has been completed
2. On the database server, open SQL Server Management Studio (SSMS)
3. Click File>Open, Project/Solution
4. Browse to the location for where you extracted the DynamicsPerf1.15 for SQL2008+.zip
5. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file
6. In Solution Explorer, open the DynamicsAX Client Tracing.sql script
7. Change <dbname> to the name of your AX database
8. Execute only the part listed below from the script against the DynamicsPerf database to enable client tracing for all AX users
/****************** Set AX Client tracing *************/
/* NOTE: must enable AX client tracing on the AOS servers */
@DATABASE_NAME = '<dbname>',
@QUERY_TIME_LIMIT = 5000
9. To view the results of a user within AX:
a. Open Dynamics AX
b. Go to Tools>Options
c. Select the SQL tab
d. Notice the SQL checkbox is marked, the long query threshold is 5000, and the Table
(database) checkbox is enabled
To be able to review the table and index property settings from within the AOT for AX tables, you will configure and schedule the AOT metadata capture. In the following steps you will configure and schedule the AOT metadata capture. The data collected will be stored in the DynamicsPerf database.
1. Launch an AX client
2. Open the Application Object Tree (AOT) in Dynamics AX
3. Click the Import icon
4. Browse to the PrivateProject_AOTExport_Batch.xpo file found where you extracted the files from in step 1 of the “Before you begin” section
5. Click OK to import
6. Open Basic>Inquiries>Batch jobs
7. The Batch jobs window opens
8. Create new batch job with Job description name of AOTExport
9. Click Save
10. Click View tasks button
11. Create new Batch task
a. Task description = AOTExport
b. Company = DAT
c. Class name = AOTExport
12. Click Save
13. Close Batch tasks window
14. On the Batch jobs window, select the Recurrence button
a. Select Recurring pattern of Days and Every weekday
b. Click OK
15. Click OK to close Recurrence window
16. On the Batch jobs window, select the AOTExport batch job
17. Select Functions>Change status
18. Change status to Waiting
To log valuable information about your AOS servers such as cpu, memory, etc., it is important to configure and schedule the performance counter logging. In the following steps, you will configure the database server for performance counter logging. This information will be logged to performance counter log files.
1. Start > Run > Perfmon
2. Expand Data Collector Sets
3. Expand Data Collector Sets
4. Right click User Defined and select New > Data Collector Set.
5. Name it “AOS Server Performance”
6. Select the “Create from a template” option.
7. Click Next
8. Select “System Performance” and click Browse to browse to the Server2008_AOS.xml file 9. Click Finish
10. Right-click on the AOS Server Performance data collector set and click Properties
11. NOTE: By default, the performance counter logs will log to C:\perflogs. If you wish to change this path follow these steps. Otherwise, go to step 12:
a. Select the Directory tab
b. Browse to a local root directory to where you want to store the performance counter logs.
12. Select the Schedule tab
13. Click the Add button to create new schedule
14. Select beginning date as of today and leave the rest as default so it will run continuously without an end date.
15. Click OK to close Folder action window
16. Ensure All schedules enabled checkbox is checked
17. Click OK to close window
The Capture AOS Settings and Event Logs collector will capture AOS configuration and event logs from each active AOS Server in the environment. This collector is initiated through the AOSANALYSIS.VBS vb script. It works in conjunction with the AOSANLAYSIS.CMD batch script to populate tables in the DynamicsPerf database with registry settings and two weeks of event logs for each AOS instance currently connected to the AX database.
• AOSANALYSIS. VBS – This script will populate tables in the DynamicsPerf database with registry settings and two weeks of event logs for each AOS instance currently connected to the AX database.
• AOSANALYSIS.CMD – This is the batch script used to execute AOSANALYSIS.VBS. Two arguments must be passed” o Database server and instance name
o AX database name
The Capture AOS Settings and Event Logs can be executed manually when needed or scheduled to run periodically.
This section describes other commands and processes that can be used with the Performance Analyzer.
To disable the long running query capture for AX, follow these steps”
1. On the database server, open SQL Server Management Studio (SSMS)
2. Click File>Open, Project/Solution
3. Browse to the location for where you extracted the DynamicsPerf1.20.zip
4. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file
5. In Solution Explorer, open the DynamicsAX Client Tracing.sql script
6. Change <dbname> to the name of your AX database
7. Execute only the part listed below from the script against the DynamicsPerf database to enable client tracing for all AX users
8. To view the results of a user within AX:
d. Notice the SQL checkbox is unmarked, the long query threshold is blank, and the Table (database) checkbox is disabled
Awesome! I´m trying this version out on a test system for a Customer. The upgrade script worked like a charm. Still a bug in the job Capture_stats_purge, but changing the time to 23:59:58 does the trick.
Great stuff, thanks. Do you know what versions of SQL / AX the Performance Analyzer is supported on? We've had great success with it more recently but are looking to try it with an older customer. Thanks RS.
SQL Server 2008 and above. AX4 and above for AX versions.
Great, thanks Rod! Keep up the good work :)
Don't seem to be able to open/convert this project in SSMS 2014. Any suggestions?
To capture AOT Metadata for AX 2012 releases, use PrivateProject_AOTExport2012.xpo instead of PrivateProject_AOTExport_Batch.xpo
The AOTExport job will need to be changed in order to run in batch. the WinAPI should be WinAPIServer for running on server.
Need to change line 53 from "start = WinAPI::getTickCount();"
start = WinAPIServer::getTickCount();
start = WinAPI::getTickCount();
Need to change line 279 from "stop = WinAPI::getTickCount();"
stop = WinAPIServer::getTickCount();
stop = WinAPI::getTickCount();
Hi, can anyone teach me how to view the captured data