Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation


INTRODUCTION

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

http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx

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.

 

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

 

 

Deployment Setup Checklist

The following is a summarized checklist of the steps to deploy Performance Analyzer. See the steps below for detailed information.

Step #

Task

   

1

Enable Long Running Query Capture for AX (AX)

2

Configure and Schedule AOT Metadata Capture (AX)

3

Configure and Schedule AOS Configuration and Event Logs Capture

4

Configure and Schedule Performance Counter Logging on Database Server

5

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

 

 

Enable Long Running Query Capture for AX

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.

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

USE DynamicsPerf

GO

EXEC SET_AX_SQLTRACE

@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

clip_image003

 

 

Configure and Schedule AOT Metadata Capture

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

 

 

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. In the following steps, you will configure the database server for performance counter logging. This information will be logged to performance counter log files.

clip_image004

1. Start > Run > Perfmon

2. Expand Data Collector Sets

3. Expand Data Collector Sets

clip_image006

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

 

 

 

 

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

 

 

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

/****************** Set AX Client tracing *************/

/* NOTE: must enable AX client tracing on the AOS servers */

USE DynamicsPerf

GO

EXEC SET_AX_SQLTRACE

@DATABASE_NAME = ‘<dbname>’,

@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 unmarked, the long query threshold is blank, and the Table (database) checkbox is disabled

Comments (11)
  1. Justin says:

    Hi,
    I see “Allow client tracing on Application Object Server instance’ checkbox enabled (2009 only step)”. For an AX 2012 R3 installation do we ignore this for the Note in “Enable Long Running Query Capture for AX” or do we need to setup a Trace in the AOT?

    1. Hi Justin,

      For Dynamics AX 2012, you can ignore this pre requisite. The “Long running queries” Dynamics AX kernel trace is a great tool to capture both SQL Statement and X++ Call stack in production with limited overhead, so I definitively encourage you to use this tool on regular basis to review the potential bottlenecks.

      Regards,
      @BertrandCaillet

  2. KENNETH WON says:

    Hi, can anyone teach me how to view the captured data

  3. Kenneth says:

    How to view the captured data?

  4. Anonymous says:

    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();"

    to:

    if (isRunningOnServer())

       {

           start = WinAPIServer::getTickCount();

       }

       else

       {

           start = WinAPI::getTickCount();

       }

    Need to change line 279 from "stop  = WinAPI::getTickCount();"

    to:

    if (isRunningOnServer())

      {

          stop  = WinAPIServer::getTickCount();

      }

      else

      {

          stop  = WinAPI::getTickCount();

      }

    thanks

  5. Damien Maspero says:

    To capture AOT Metadata for AX 2012 releases, use PrivateProject_AOTExport2012.xpo instead of PrivateProject_AOTExport_Batch.xpo

  6. Anonymous says:

    Don't seem to be able to open/convert this project in SSMS 2014. Any suggestions?

  7. Anonymous says:

    Great, thanks Rod! Keep up the good work 🙂

  8. Anonymous says:

    SQL Server 2008 and above.  AX4 and above for AX versions.

  9. Anonymous says:

    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.

  10. Anonymous says:

    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.

Comments are closed.