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
In order to use Performance Analyzer, you must first create the DynamicsPerf database, it’s objects, and jobs. In the following steps you will create the DynamicsPerf database, its objects, and jobs.
NOTE: You must have completed the installation steps documented in the Performance Analyzer for Dynamics Deployement and User Guide – Core Components first
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 DynamicsPerf 1.20 zip
4. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file
5. In Solution Explorer, open the 2-Create_AX_Objects.sql script from the Solution Explorer
6. Execute the script. [This will create the necessary objects in the DynamicsPerf database]
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. if using a version of Dynamics AX prior to AX2012 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
Please ensure step 5 in the “Before you begin” section has been completed
3. Browse to the location for where you extracted the DynamicsPerf1.20.zip
5. In Solution Explorer, open the DynamicsAX Client Tracing.sql script
6. Change xxxxxxx 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
/****************** Set AX Client tracing *************/
/* NOTE: must enable AX client tracing on the AOS servers */
@DATABASE_NAME = 'xxxxxxxxx',
@QUERY_TIME_LIMIT = 5000
8. 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 following is a list of items that should be checked periodically to ensure Performance Analyzer is running and collecting the data.
Is the performance data being collected on a regular schedule?
Check the history of the
DYNPERF_Capture_Stats job to ensure it is running every day
Is the performance data being purged on a regular schedule?
Check the history of the DYNPER_Stats_Purge job to ensure it is running every day
Is the AOS configuration and event logs being collected on a regular schedule?
Check the history of the AOSANALYSIS job to ensure it is running on regular schedule.
Is the AOT metadata being collected on a regular schedule?
Check the AOTExport batch job within AX to ensure it is running on regular schedule
Is the Database blocking being collected on a regular schedule?
DYNPERF_Default_Trace_Start job to ensure it is running every day
Is AX client tracing enabled on every AOS server?
Check if the “Allow client tracing on Application Object Server instance” is enabled on every AOS server
Is AX tracing enabled for every user?
Run the DynamicsAX Client Tracing.sql (enable portion of the script only) periodically to ensure set for all users
Are the AX tracing tables being periodically purged?
DYNPERF_Purge_SYSTRACETABLESQL job to
ensure it is running on a regular schedule
Are the performance counter logs running on the database server?
Check if the templates that you imported are running in the Performance Monitor on the database server
Are the performance counter logs running on the AOS servers?
Check if the templates that you imported are running in the Performance Monitor on every AOS server
If you are using the
DYNPERF_Optional_Polling_for_Blocking job is the data being periodically purged?
DYNPERF_Optional_Polling_for_Blocking job to ensure it is running periodically
The Capture AOS Metadata collector is specific to Dynamics AX and used to capture AOT property data to include table properties, index properties, and cache settings. This collector is initiated through the AOTExport X++ Class.
When the X++ AOTExport Class is imported into AX, it will create the following objects:
• AOTTABLEPROPERTIES table
• AOTINDEXPROPERTIES table
• AOTINDEXFIELDS table
• AOTExport Class
The AOTExport class is both runnable and batchable. When executed, it will populate the tables listed above. Subsequent executions of DYNPERF_Capture_Stats will pull data from those tables into related tables in the DynamicsPerf database.
FIGURE 4 AOTExport Class
The AOTExport X++ Class should be scheduled on a periodic basis to ensure all changes into the AOT are added to the DynamicsPerf database.
Simon, the Systems Implementer, is experiencing performance issues and wants to use Performance Analyzer to analyze the cache settings for his AX tables. Performance Analyzer collects table and index properties from the AOT.
Simon validates that the DYNPERF_Capture_Stats job ran the previous night and that it populated the required tables with the cache settings. He executes a script to determine which tables in AX have the
“EntireTableCache” property enabled and the table is over 128K in size.
Simon realizes that any results mean that the cache settings should be changed to another AX cache setting as tables this large should not be set to “EntireTableCache”.
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 vbscript. 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 Database server and instance name AX database name
The Capture AOS Settings and Event Logs can be executed manually when needed or scheduled to run periodically.
Simon, the Systems Implementer, is experiencing performance issues and wants to use Performance Analyzer to analyze the configuration settings for all of the AOS servers. Performance Analyzer collects AOS configuration information from all active AOS servers.
Before he executes the AOSANALYSIS vbscript, he modifies the batch script with his server name and AX database name. Simon then runs the vb script. The information is now pulled into the DynamicsPerf database where he can analyze the settings.
Simon runs scripts against the tables that were populated in the DynamicPerf database and notices that the debugging settings are enabled on all of his AOS servers. Microsoft has confirmed with Simon that it is not best practice to run with the debug settings enabled as it could cause performance issues. Simon makes the necessary changes to his configuration.
There are several tasks that need to be scheduled to properly maintain the DynamicsPerf database such as purging unneeded data.
The following is a summarized checklist of the steps to maintain Performance Analyzer. See the steps below for detailed information.
Configure and Schedule Performance Data Purge
Configure and Schedule AX Long Running Query Collection Purge
Configure and Schedule Blocking Data Polling Purge
In the following steps, you will configure and schedule the process for purging the contents of the tracing tables within the Dynamics AX database that are used to capture long running query traces from Dynamics AX. After a period of time, this data is no longer useful so it is good practice to schedule this deletion to maintain optimal Dynamics AX database size.
NOTE: The overhead of enabling this is directly proportional to the number of events that exceed the threshold since a record is written to the SYSTRACETABLESQL table each time that happens. When the duration threshold is set to a reasonably high value (2000ms or greater), the overhead is very small and should not be noticeable to end users. If you’re interested in how much logging is happening, just refer to the SYSTRACETABLESQL table. A timestamp exists on each row indicating when it was logged.
These options can be programmatically turned on or off at any time via a stored procedure in the Health Check database. This helps to avoid the tedious work of managing the settings via the user options screens within the application.
1. Open SQL Server Management Studio (SSMS)
2. In Object Explorer, expand SQL Server Agent>Jobs
3. Open the DYNPERF_Purge_SYSTRACETABLESQL_AX job
4. Check the Enabled checkbox
5. In the Select a page pane select Schedules
6. Double click the schedule task
7. Change the schedule to how often you want to purge this data. A monthly recurring schedule may be sufficient to begin with.
8. Click OK to close the window
9. Click OK to close the job properties window
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”
6. Change <dbname> to the name of your AX database
@DATABASE_NAME = '<dbname>',
d. Notice the SQL checkbox is unmarked, the long query threshold is blank, and the Table (database) checkbox is disabled
If you want to manually execute the process for capturing AOT metadata, follow these steps:
6. Within the AOT, browse to Classes and find the AOTExport Class
7. Right click>Open
8. The cursor will appear for a few seconds indicating that it is running
To manually execute the AOS configuration and event logs capture, follow these steps:
1. Browse to the location to where you extracted the files in step 1 of the “Before you begin” section
2. Edit the AOSANALYSIS.CMD file
3. Change the first parameter e.g., DYNAMICSVM to the name of your database server
4. Change the second parameter e.g., DYNAMICSDB to the name of your AX database name
5. Save the edited AOSANALYSIS.CMD file
6. Open a Command prompt
7. Browse to the location of the AOSANALYSIS.CMD and AOSANALYSIS.VBS files
8. From the Command prompt, enter AOSANALYSIS
9. Wait until the name of all AOS servers are listed and completed before you exit
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