UA-44032151-3 page contents

DynamicsPerf 2.0 Installation for Dynamics AX


INTRODUCTION

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

Dynamicsperf-2-0-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 Dynamicsperf-2-0-deployment-guide.

Step #

Task

9

Configure and Schedule AOS Configuration and Event Logs Capture

10

Configure and Schedule AOT Metadata Capture (AX)

11

Enable Long Running Query Capture for AX (AX)

12

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 9 - 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. clip_image014
  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. clip_image016
  7. Change the first 4 lines of code to the appropriate SQL Server and Database names:
  8. clip_image018
  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 10 - 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 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
  5. Click OK to import
  6. Navigate to Classes in the AOT
  7. Select the AOTEXPORT2012Direct class (will be 2009 for that version)
  8. clip_image004
  9. Right click the class and select OPEN
  10. To run now, fill out the dialogue correctly and press OK
  11. clip_image006
  12. To set it up as a batch job, click the Batch tab and complete the dialogue as desired
  13. clip_image008

 

STEP 11 - 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 12 - 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 Hansen

    Comments (10)
    1. Hi,

      I’m using AX 4.0 and the SYSSERVERCONFIG table doesn’t seem to be part of the table structure and as such is causing the DYNPERF_COLLECT_AOS_CONFIG job to fail:

      Error Description: Invalid object name ‘SYSSERVERCONFIG’. Error on Line 129. The step failed.

      Is there a way I can amend the script to use with AX 4.0?

      Thanks

      1. Hello Robbie,

        DynamicsPerf version 2.0 is not compatible with Dynamics AX 4.0 because of the schema change but also because it is relying on DMVs that are part of SQL Server release not compatible with Dynamics AX 4.0.

        You should use the previous release of DynamicsPerf for your version.

        Regards,
        @BertrandCaillet

    2. Mark Blevins says:

      You may have already corrected, but in the stored proc DYNPERF_COLLECT_SQL_DATABASE_FILES.
      Lines 137 and 193 need to be rounded or you get an error “Arithmetic overflow error converting numeric to data type numeric.”

      Cast(Round(FILEPROPERTY(name,’ + ”” + ””+ ‘SpaceUsed’ +”” + ””+ ‘) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) As FreeSpace,

      1. THank you Mark.

        Could you log your issue in the codebox site directly as well?
        http://dynamicsperf.codeplex.com/workitem/list/basic

        Thanks,
        @BertrandCaillet

    3. Kunal says:

      Hello,

      Can Step 11 be replicated in the AX client tool under Tools | Options? I didn’t see an option to log to the DynamicsPerf database anywhere in there.

      The code for this step fails when using a linked server. The culprit seems to be
      IF NOT EXISTS (SELECT *
      FROM sys.databases
      WHERE name = @DATABASE_NAME)
      BEGIN
      PRINT @DATABASE_NAME + ‘ DOES NOT EXIST’
      SET @RC = 1
      GOTO ERROR
      END

      Is it safe to disable that bit of code and let the rest of the stored proc run?

    4. Nick says:

      Is there any solution for Step 11?

      For linked servers store procedure – SET_AX_SQLTRACE giving error

    5. Tom says:

      Hi, i found out the issue – the account under which the SQL Agent is running has to be local Admin on the AOS-Servers. In My case it was the Computer-Account of the SQL-Server. Maybe Membership of the local Performance Monitoring Group is also sufficient – i never tested that.

      Greetings,

      Thomas

    6. Tom says:

      Hello Rod,

      i have AX and DynamicsPerf DB on the same SQL-Server, but STEP 9 does not deliver any data.. The Job runs successfully (it can connect to both AX and DynamicsPerf DB) but for example the tables AOS_EVENTLOG or AOS_REGISTRY remain empty.

      Could it be that the Job is not working when the SQL Agent is running under NT ServiceSQLSERVERAGENT? Maybe this account has not the permissions to get data from the AOS-Servers Registry and Eventlogs?

      Please help me in this case.

      Thank you,

      THomas

    7. Step 11, not working for multiple DAX environments says:

      Hi,

      Similar problem with step 11.

      The SET_AX_SQLTRACE does not work for linked servers.

      I added @SERVER_NAME = 'LINKEDSERVERNAME', paramenter but that is not enough.

      This procedure only checks on local server if the DB exists:

      IF NOT EXISTS (SELECT *

       FROM sys.databases

       WHERE name = @DATABAS_NAME)

       BEGIN

         PRINT @DATABASE_NAME + 'DOES NOT EXIST'

         SET @RC = 1

         GOTO ERROR

       END

    8. Step 9 - multiple DAX environments says:

      Hi,

      Thanks for improving this great tool.

      I have one question regarding step 9 –  How do I set this up for multiple environments?

      My first guess is to copy "steps" – step1 to step2 and configure different servers?

      Please guide me to the right solution.

      Regards from the top of the world

      Magnus

    Comments are closed.

    Skip to main content