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

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.

DEPLOYING PERFORMANCE ANALYZER Dynamics AX Components

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 Dynamics AX instance. 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 Deployment and User Guide – Core Installation before completing the following steps.

Before you deploy Performance Analyzer, you must complete the following:

Extract the DynamicsPerfxxx.zip file to a location to where you can browse from the database and AOS servers

Make sure you have rights to create new databases on the database server

Verify you have read access to the AX database

Verify also that you have write access to the DynamicsPerf database (this database gets created as part of Performance Analyzer)

Ensure you have Admin permissions to each of the AOS servers connected to the AX database

Ensure you have created a local folder on the database server called \SQLTRACE to store the trace files that get generated

Make sure 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)

STEP 1 - CAPTURE AOS SETTINGS AND EVENT LOGS

The Capture AOS Settings and Event Logs collector will capture AOS configuration and event logs from each active AOS Server in the environment.

NOTE: THIS TASK HAS BEEN GREATLY SIMPLIFIED SINCE PREVIOUS VERSIONS OF DYNAMICSPERF.

This task is now implemented as a SQL Server Agent Job.

Open SQL Server Management Studio on the Server with DynamicsPerf installed

Navigate to SQL Server Agent | Jobs

Right click DYNPERF_COLLECT_AOS_CONFIG job and select properties

Click on Steps on left side of the screen and then click the EDIT button at the bottom middle of the screen

Change the first 4 lines of code to the appropriate SQL Server and Database names:

Click Ok twice

Test the job

Right click DYNPERF_COLLECT_AOS_CONFIG job and select Start Job at Step

Verify the job runs

STEP 2 - Configure and Schedule AOT Metadata Capture

To be able to review the table and index properties 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 DynamicsPerfdatabase.

NOTE: This new version does not create any tables in your Dynamics AX database any longer. It will export directly to the DynamicsPerf database, this collector is initiated through the AOTExport X++ Class.

Launch an Dynamics AX client

Open the Application Object Tree (AOT) in Dynamics AX (Ctl-Shift-W to open a new developer workspace)

Click the Import icon

Browse to the dynamicsperf\scripts-dynamics ax\PrivateProject_AOTExport2012_DynamicsPerfDirect.xpo file found where you extracted the files from in step 1 of the “Before you begin” section

Click OK to import

Navigate to Classes in the AOT

Select the AOTEXPORT2012Direct class (will be 2009 for that version)

Right click the class and select OPEN

To run now, fill out the dialogue correctly and press OK

To set it up as a batch job, click the Batch tab and complete the dialogue as desired

STEP 3 - Enable Long Running Query Capture for AX

If using Dynamics AX, you can set thresholds which capture long running queries with Dynamics AX source code. In the following steps you will configure the system to capture long running queries. The data collected will be stored in the DynamicsPerfdatabase.

NOTE: This enables long duration tracing for all AX users by updating the USERINFO table and sets the long running query threshold to 5000ms (5 seconds). if using a version of Dynamics AX prior to version 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 and it requires a restart of the AOS.

On the database server, open SQL Server Management Studio (SSMS)

Click File>Open, Project/Solution

Browse to the location for where you extracted the DynamicsPerf2.0zip

Select the Performance Analyzer 2.00 Analyze Data.ssmsln file

In Solution Explorer, open the Dynamics AX Client Tracing.sql script

Change xxxxxxx to the name of your AX database

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 */

USE DynamicsPerf

GO

EXEC SET_AX_SQLTRACE

@DATABASE_NAME = 'xxxxxxxxx',

@QUERY_TIME_LIMIT = 5000

8. To view the results of a user within the application:

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