Dynamics AX in the Field

Microsoft Dynamics AX from the Premier Field Engineering team at Microsoft.

Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation

Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation

Rate This
  • Comments 6

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

Create Database, Objects, and Jobs for Dynamics AX

2

Enable Long Running Query Capture for AX (AX)

3

Configure and Schedule AOT Metadata Capture (AX)

4

Configure and Schedule AOS Configuration and Event Logs Capture

5

Configure and Schedule Performance Counter Logging on Database Server

6

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

 

Create Database, Objects, and Jobs for Dynamics AX

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]

 

 

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.

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

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

 

 

Deployment Verification Checklist

The following is a list of items that should be checked periodically to ensure Performance Analyzer is running and collecting the data.

#

Verification

Where?

1

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

2

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

3

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.

4

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

5

Is the Database blocking being collected on a regular schedule?

Check the history of the

DYNPERF_Default_Trace_Start job to ensure it is running every day

6

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

7

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

8

Are the AX tracing tables being periodically purged?

Check the history of the

DYNPERF_Purge_SYSTRACETABLESQL job to

ensure it is running on a regular schedule

9

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

10

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

11

If you are using the

DYNPERF_Optional_Polling_for_Blocking job is the data being periodically purged?

Check the history of the

DYNPERF_Optional_Polling_for_Blocking job to ensure it is running periodically

 

 

 

CAPTURE AOT METADATA

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.

clip_image008

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.

Use Case – Verify Cache Settings

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

 

 

 

 

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

Use Case – Analyze AOS Configuration Settings

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.

 

PERFORMANCE ANALYZER MAINTENANCE

There are several tasks that need to be scheduled to properly maintain the DynamicsPerf database such as purging unneeded data.

Maintenance Checklist

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

Step #

Task

1

Configure and Schedule Performance Data Purge

2

Configure and Schedule AX Long Running Query Collection Purge

3

Configure and Schedule Blocking Data Polling Purge

 

 

Configure and Schedule AX Long Running Query Collection 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

clip_image013

4. Check the Enabled checkbox

5. In the Select a page pane select Schedules

clip_image015

6. Double click the schedule task

clip_image017

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

 

 

 

 

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

 

 

How to Manually Execute the AOT Metadata Capture

If you want to manually execute the process for capturing AOT metadata, follow these steps:

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

 

 

How to Manually Run the AOS Configuration and Event Logs Capture

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

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post