page contents

Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Core Installation


 

INTRODUCTION

DynamicsPerf (Performance Analyzer) ,http://dynamicsperf.codeplex.com,  is the tool used by Microsoft Dynamics support, Premier Field Engineers, and product team members to diagnose performance issues with Dynamics products.

IMPORTANT: The purpose of the Performance Analyzer is to be used on a continual basis so it is important for administrators to understand the components that make up the tool to ensure all jobs and collectors are running.

DynamicsPerf collects a variety of pertinent information from the database server, application object server (AOS), and application server. This information is captured from a number of collectors provided by Dynamicsperf that includes query statistics, query plans, index statistics, database and AOS server configurations, AOS event logs, and AOT metadata. In addition, blocking and deadlocking events are collected through SQL Eventing while performance counter data is collected from the database and AOS servers.

clip_image003

FIGURE 1 PERFORMANCE ANALYZER FOR DYNAMICS (DynamicsPerf)

As you can see from the illustration above, the DynamicsPerf database is the central repository for most of the data collected for Performance Analyzer.

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

• Capture AOT Metadata

• Capture AOS Settings and Event Logs

• Capture Database Blocking

• Capture Performance Counter Data

We will discuss each one of the collectors in the following sections and the process for deploying and maintaining Performance Analyzer in later sections.

 

DEPLOYING PERFORMANCE ANALYZER

There are several steps that need to be completed in order to successfully deploy Performance Analyzer for Microsoft Dynamics. 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.

 

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

2

Configure and Schedule Performance Data Capture

3

Configure and Schedule Database Blocking Capture

4

Enable Long Running Query Capture for AX (AX)

5

Configure and Schedule AOT Metadata Capture (AX)

6

Configure and Schedule AOS Configuration and Event Logs Capture

7

Configure and Schedule Performance Counter Logging on Database Server

8

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

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.

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 for SQL2008+.zip

4. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file

5. In Solution Explorer, open the 1-Create_Core_Objects.sql script

6. Execute the script. [This will create the DynamicsPerf database and SQL jobs.]

clip_image001NOTE: Ensure that you read the notes in the script if you wish to path the DynamicsPerf files to a location other than the C drive

7. In Solution Explorer, open the 2-Create_AX_Objects.sql script from the Solution Explorer

8. Execute the script. [This will create the necessary objects in the DynamicsPerf database]

 

 

Configure and Schedule Performance Data Capture

After you have created the DynamicsPerf database, objects, and jobs it is time to configure the database and jobs to capture the performance data. In the following steps you will configure and schedule the capture of the performance data. The performance data collected will be stored in the DynamicsPerf database:

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

2. In Object Explorer, expand SQL Server Agent>Jobs

3. Open the DYNPERF_Capture_Stats job

clip_image003

4. In the Select a page pane, select Steps

clip_image005

5. Double click Step 1 sp_capturestats to open it.

clip_image007

6. Change the @DATABASE_NAME to the name of the AX database name (Example: ‘AX2009’)

7. Select OK to close the window

8. In the Select a page pane select Schedules

clip_image009

9. By default, this task is enabled and will run every day at 5:00 PM. If you wish to change the time for which it runs daily:

a. Double click the Daily schedule to open it

clip_image011

b. Make your changes in this window

c. Click OK to close the window

10. Click OK to close the DYNPERF_Capture_Stats job window

 

 

Configure and Schedule Database Blocking Capture

In order to collect database blocking events, it is necessary to configure and schedule the database blocking capture. In the following steps you will configure and schedule the capture of database blocking events. The events collected will be contained in SQL trace files.

clip_image012NOTE: This option should be used in all cases for tracking blocking events and is recommended option if you are tracking blocking over a long period of time.

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

2. In Object Explorer, expand SQL Server Agent>Jobs

3. Open the DYNPERF_Default_Trace_Start job

clip_image014

4. Check the Enable checkbox

5. In the Select a page pane, select Steps

clip_image016

6. Double click Step 1 Start Tracing to open it.

clip_image018

7. If the @FILE_PATH is the incorrect path to the \SQLTRACE folder that you created in the “Before you begin” steps, change it here

8. Click OK to close the window

9. In the Select a page pane select Schedules

clip_image020

10. Verify that each task is enabled and scheduled every day at 12:00 AM and that it starts automatically when SQL Server agents starts

11. Click OK to close the 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

 

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 Performance Data Purge

In the following steps, you will configure and schedule the process for purging unneeded data from the DynamicsPerf database. This is important for several reasons but mainly to rid old performance data and maintain optimal database size. To do this you will use the DYNPERF_Capture_Stats_Purge job.

This is a job designed to remove any DMV performance data captured that is older than 14 days. A schedule has been added to this job so that it runs daily to prevent the DynamicsPerf database from consuming all disk space.

1. Open SQL Server Management Studio (SSMS)

2. In Object Explorer, expand SQL Server Agent>Jobs

3. Open the DYNPERF_Capture_Stats_Purge job

clip_image002

clip_image003NOTE: By default this job will purge data from the DynamicsPerf database that are 14 days and older.

4. In the Select a page pane select Schedules

clip_image005[4]

5. By default, this task is enabled and will run every day at 11:59:59 PM. If you wish to change the time for which it runs daily:

a. Double click the Daily schedule to open it

clip_image007[4]

b. Make your changes in this window

c. Click OK to close the window

6. Click OK to close the DYNPERF_Capture_Stats_Purge job window

 

 

How to Capture Performance Data Manually

There may be times when you want to capture the performance data manually instead of waiting for it to run at its scheduled time. In the following steps, you will capture performance data manually.

1. Open SQL Server Management Studio (SSMS)

2. Click File>Open, Project/Solution

3. Browse to the location for where you extracted the DynamicsPerf1.20 for SQL2008+.zip

4. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file

5. In Solution Explorer, open the Manual –CaptureStats.sql script

USE DynamicsPerf

EXEC SP_CAPTURESTATS

@DATABASE_NAME = 'XXXXXXXXX'

--, @DEBUG = 'Y'

6. Change the @DATABASE_NAME to the name of the AX database name (Example: ‘AX2009’)

7. Execute the script against the DynamicsPerf database

 

How to Capture Performance Data from Multiple Databases

It is possible to capture performance data from multiple databases. This may be necessary if you wish to verify the performance of databases in addition to your AX database. In the following steps, you will configure the performance data capture for all databases.

1. Open SQL Server Management Studio (SSMS)

2. Click File>Open, Project/Solution

3. Browse to the location for where you extracted the DynamicsPerf1.20 for SQL2008+.zip

4. Select the Performance Analyzer 1.20 for Microsoft Dynamics.ssmssln file

5. If you wish to do this on a recurring basis, modify the DYNPERF_Capture_Stats job with the following information or manually execute the script below one time against the DynamicsPerf database:

• Use the following SQL script

USE DynamicsPerf

EXEC SP_CAPTURESTATS

@DATABASE_NAME = NULL

• Insert the name of the other databases that you wish to capture performance data for into the DATABSES_2_COLLECT table

 

Dynamics AX customers please proceed to the install blog for the Dynamics AX components

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

Comments (4)
  1. Baber says:

    Hi Rod,

    Is there any performance overhead, if I install the DynamicsPerf database on the same SQL server instance which has LIVE database? I need to capture some performance statistics for a LIVE environment and just wondering if there would be any performance issues.

    Thanks,

    Baber.

  2. Navid says:

    Thanks Rod. Will use the script.

  3. Rod Hansen says:

    There is actually an upgrade script to take you from 1.16 to 1.20.  You can simply run that script.

  4. Navid says:

    Hi Rod,

    Great post about the installation of latest version. Is there any guide/recommendation on how to remove the existing version (1.16) from the production systems or should we just upgrade the existing version?

    Thanks

Comments are closed.

Skip to main content