This post has been republished via RSS; it originally appeared at: MSDN Blogs.

INTRODUCTION

Please be sure to install the core components of Performance Analyzer for Microsoft Dynamics before completing this guide.

https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-10-deployment-guide/

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.

Deployment Setup Checklist

The following is a summarized checklist of the steps to deploy Performance Analyzer. See the steps below for detailed information. These steps are continued from the https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-10-deployment-guide/.

Step  1

Configure and Schedule AOS Configuration and Event Logs Capture

Step  2

Configure and Schedule AOT Metadata Capture (AX)

Step  3

Enable Long Running Query Capture for AX (AX)

Step  4

Configure and Schedule Performance Counter Logging on AOS Server(s)

Before you begin

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. Make sure you have rights to create new databases on the database server
3. Verify you have read access to the AX database
4. Verify also that 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. 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.

1. Open SQL Server Management Studio on the Server with DynamicsPerf installed
2. Navigate to SQL Server Agent | Jobs
3. 
4. Right click DYNPERF_COLLECT_AOS_CONFIG job and select properties
5. Click on Steps on left side of the screen and then click the EDIT button at the bottom middle of the screen
6. 
7. Change the first 4 lines of code to the appropriate SQL Server and Database names:
8. 
9. Click Ok twice

Test the job

1. Right click DYNPERF_COLLECT_AOS_CONFIG job and select Start Job at Step
2. 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 DynamicsPerf database.

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.

1. Launch an Dynamics AX client
2. Open the Application Object Tree (AOT) in Dynamics AX (Ctl-Shift-W to open a new developer workspace)
3. Click the Import icon
4. Browse to the dynamicsperfscripts-dynamics axPrivateProject_AOTExport2012_DynamicsPerfDirect.xpo file found where you extracted the files from in step 1 of the “Before you begin” section
5. Click OK to import
6. Navigate to Classes in the AOT
7. Select the AOTEXPORT2012Direct class (will be 2009 for that version)
8. 
9. Right click the class and select OPEN
10. To run now, fill out the dialogue correctly and press OK
11. 
12. To set it up as a batch job, click the Batch tab and complete the dialogue as desired
13. 

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

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 DynamicsPerf2.0zip
4. Select the Performance Analyzer 2.00 Analyze Data.ssmsln file
5. In Solution Explorer, open the Dynamics AX 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 */

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

STEP 4 - Configure and Schedule Performance Counter Logging on AOS Server(s)

To log valuable information about your AOS servers such as cpu, memory, etc., it is important to configure and schedule the performance counter logging.

Please follow the steps in this article for setting up Windows Performance Counters:

http://blogs.msdn.com/b/axinthefield/archive/2016/01/05/setting-up-windows-performance-monitor-templates.aspx

OTHER COMMANDS AND PROCEDURES

This section describes other commands and processes that can be used with the Performance Analyzer.

Disable Long Running Query Capture for AX

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 DynamicsPerf2.0.zip
4. Select the Performance Analyzer 2.0 for Microsoft Dynamics.ssmssln file
5. In Solution Explorer, open the Dynamics AX 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

1. /****************** Set AX Client tracing *************/
2. /* NOTE: must enable AX client tracing on the AOS servers */
3. USE DynamicsPerf
4. GO
5. EXEC SET_AX_SQLTRACE
6. @DATABASE_NAME = '<dbname>',
7. @QUERY_TIME_LIMIT = 5000

• To view the results of a user within AX:
  1. Open Dynamics AX
  2. Go to Tools>Options
  3. Select the SQL tab
  4. Notice the SQL checkbox is unmarked, the long query threshold is blank, and the Table (database) checkbox is disabled

  Regards,

  Rod “Hotrod” Hansen