page contents

DynamicsPerf 2.0 Deployment Guide


INTRODUCTION

The tool DynamicsPerf (Performance Analyzer for Dynamics) is used by Microsoft Dynamics Support team, Premier Field Engineers and Product Group team members to diagnose performance issues with Dynamics products such as Dynamics AX and Dynamics CRM.

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 Application Object Tree (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.

image

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

Performance Analyzer is delivered as two SQL Server solution files:

  1. [Performance Analyzer 2.00 Installation.ssmssln]; the first includes a set of SQL jobs, X++ classes, Visual Basic scripts and performance counters to initiate the collection process.
  2. [Performance Analyzer 2.00 Analyze Data.ssmssln]: the second includes a set of sample SQL scripts that can be used to query and analyze the populated tables and views in the DynamicsPerf database.

Before we begin, you can read about DynamicsPerf at: What's New DynamicsPerf 2.0

Deploying DynamicsPerf

There are several steps that need to be completed in order to successfully deploy DynamicsPerf. The toolset is meant to be deployed and set up for data collection on a continual basis throughout the life of your Microsoft Dynamics produts. This ensures that if performance issues arise, you are able to quickly identify the bottleneck as well as use for comparison purposes. We currently have scripts for Dynamics AX and Dynamics CRM in version 2.0.

Deployment Setup Checklist

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

Step 1

Run Script 1 Create Core Objects, this creates the DynamicsPerf database and all objects

Step 2

Run script 2 Deploy DynamicsPerf Schedules

Step 3

If DynamicsPerf is being installed remotely run script 3 Setup Linked Servers, otherwise skip this step

Step 4

Run Script 4 Configure DBs to Collect, specify your Dynamics Databases that you want to collect data for

Step 5

Run Script 5 Setup SSRS Data Collection, this will allow collection of SSRS Report performance data

Step 6

Run Script 6 Install Fulltext Indexes in DynamicsPerf database, must have FULLTEXT Index Service Installed

Step 7

Run Script 7 Deploy Extended Events, MUST BE RUN on SQL instance hosting your Dynamics application database, SQL 2012 and above only

Step 8

Deploy the Windows Perfmon counters

Step 9

For Dynamics AX, Edit the DYNPERF_COLLECT_AOS_CONFIG SQL Agent job, must put in connection information for Dynamics SQL and DynamicsPerf SQL

Step 10

For Dynamics AX, Import and Run the new AOTEXPORT class from DynamicsPerf\Scripts - Dynamics AX folder

Steps 9 and 10 are documented here:

http://blogs.msdn.com/b/axinthefield/archive/2015/12/30/dynamicsperf-2-0-installation-for-dynamics-ax.aspx

 

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
  2. Make sure you have rights to create new databases on the database server
  3. Verify you have read access to the Dynamics Application database
  4. Verify also you have write access to the DynamicsPerf database (this database gets created as part of Performance Analyzer)
  5. Ensure you have created a local folder on the database server called \SQLTRACE to store the extended event files that get generated

 

STEP 1 - Create Database Objects, and Jobs

In order to use Performance Analyzer, you must first create the DynamicsPerf database, its 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 DynamicsPerf2.0.zip
  4. Select the Performance Analyzer 2.0 Installation.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.]
  7. clip_image004

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

 

NOTE:  If installed on SQL 2008R2, you WILL get errors.  Not all of the new views are compatible with SQL Server 2008R2.  Highly recommended to do a remote installation of DynamicsPerf 2.0 on SQL 2012 collecting data from your older SQL 2008R2 server.

99% of DynamicsPerf 2.0 works on SQL Server 2008R2.  It's just a couple new views and extended events that don't work on SQL 2008R2.

 

STEP 2 - Deploy DynamicsPerf Schedules

DynamicsPerf 2.0 has its own internal scheduling engine separate from SQL Server Agent. This makes it possible to have 50 different collectors without having to schedule 50 different tasks in SQL Server Agent. This also makes it much simpler to control via tables in the DynamicsPerf database.

The three main scheduling tables are:

  • DATABASES_2_COLLECT
  • DYNPERF_TASK_SCHEDULER
  • DYNPERF_TASK_HISTORY
  1. In Solution Explorer, open the 2-Deploy DynamicsPerf Schedules.sql script
  2. Execute the script. [This will deploy the tasks and schedules for the DynamicsPerf database.]
  3. clip_image006

 

 

STEP 3 – Setup Linked Servers

In order to collect data from a Microsoft Dynamics product database or SSRS database that are on a different SQL Servers than the DynamicsPerf database you must setup Linked Servers for them. Otherwise, this step is optional.

NOTE: You will need to setup a Linked Server for EACH remote database server.

NOTE: MUST ENABLE Distributed Transaction Coordinator in Windows Services on the server you are setting the Linked server up on.

clip_image008

There are two ways that you could setup the Linked Server: one is using the provided script in the solution, and the other is directly in SQL Server Management Studio (SSMS).  We’ll provide steps for both.

The following is a diagram that represents the Linked Servers that would need setup for two configurations:

clip_image010

Configure Linked Server via TSQL scripts

  1. In Solution Explorer, open the 3-Setup Linked Servers(optional).sql script
  2. Edit the script.
  3. Step 1 Create the Linked Server
  4. clip_image012
  5. Configure the @server= with your remote SQL Server that you want to collect data
  6. Configure @datasrc = with the DNS name of your SQL Server. (The example is Azure SQL Database which is supported
  7. Configure @catalog with the database name of your Dynamics product database or if setting up for SSRS it should be ‘ReportServer’.

Execute this script to create the Linked Server

Step 2 Add credentials and options to this linked server

clip_image014

Configure the @server= with your remote SQL Server that you want to collect data.

If you will be using Windows Authentication, leave the script as is. If you will be setting up SQL Authentication, change @useself = ‘FALSE’, and uncomment the last 2 lines and fill in as appropriate.

NOTE: If using Windows Authentication, the SQL Server Agent MUST BE started with a DOMAIN account that has rights to the remote server.

NOTE: This account must have administrative rights to query SQL DMVs on the remote server.

Execute this script to create the Login for the Linked Server

Step 3 Enable the correct options for the Linked Server

clip_image016

Replace ‘SQL_NAME_HERE’ with the name of the Linked server setup in the previous steps

Execute this script to set the correct options for the Linked Server

Additional information about setting up linked servers can be found at: https://msdn.microsoft.com/en-us/library/ff772782.aspx

Configure Linked Server via SQL Server Management Studio (SSMS):

In SSMS, navigate to Server Objects: Linked Servers and right click and select NEW Linked Server

clip_image018

Next Click the SQL Server selection box and put in the name of your remote SQL Server:

clip_image020

Next Click the Security options and fill out as necessary:

clip_image022

Next select the Server Options Tab and set the following options:

clip_image024

click OK to setup the Linked Server

 

 

Step 4 – Configure Dynamics databases to be collected

This step populates the DATABASES_2_COLLECT table in the DynamicsPerf database.  This will start data collection immediately for the database.

NOTE: Complete this step for EACH database you would like to gather performance data on.

 

  1. In Solution Explorer, open the 4-ConfigureDBs to Collect.sql script
  2. Edit the script, replace the ‘SQL_NAME_HERE' with the SQL Server that hosts the database.  Replace 'DB_NAME_HERE' with the database to be collected
  3. Execute the script
  4. clip_image026

 

STEP 5 – Setup SQL Server Reporting Server (SSRS) data collection

This step populates the SSRS_CONFIG table in the DynamicsPerf database.  This will start data collection immediately for the SSRS database.

  1. In Solution Explorer, open the 5-Setup SSRS Data Collection.sql script
  2. Edit the script, replace the ‘SSRS_SERVER_NAME_HERE’ with the SQL Server that hosts the ReportServer database.
  3. Execute the script
  4. clip_image028

NOTE: If the SSRS is remote from the SQL Server hosting the DynamicsPerf toolset, you will need to setup a Linked Server per the steps in that section.

 

 

STEP 6 – Install the FULLTEXT indexes into DynamicsPerf

This step puts FULLTEXT indexes into the DynamicsPerf database.  This allows for more dynamic investigation of the data collected.

  1. In Solution Explorer, open the 6-Install Fulltext Indexes for DynamicsPerf.sql script
  2. Execute the script
  3. clip_image030

 

 

STEP 7 – Deploy Extended Events to SQL Server

This step deploys Extended Events to the SQL Server hosting your Dynamics application database.  This collects blocking and other performance related event data.  This is the replacement for SQL Trace

PREREQUISITE:  You must create a folder on C:\SQLTRACE of the SQL Server where you are deploying this script. If you change that folder location then you will have to edit the scripts to the new location.

  1. In Solution Explorer, open the 7-Deploy Extended Events.sql script (SQL2008R2 installations use script 7a-SQL2008R2 Blocking Jobs.sql instead of the extended events)
  2. Connect to the appropriate SQL Server instance (not the DynamicsPerf SQL Server unless this is a local install see instructions below)
  3. Execute the script
  4. clip_image032
  5. clip_image034

 

For SQL2008R2 installations, do the following:

  1. In Solution Explorer, open the 7a-Blocking Jobs.sql script
  2. Connect to the appropriate SQL Server instance (not the DynamicsPerf SQL Server unless this is a local install see instructions below)
  3. Execute the script
  4. Open SQL Server Agent on the instance to which you deployed the blocking job.
  5. Open the Dynperf_Default_trace_start job and enable the job. 
  6. Edit the script to change the path location if you created a director other then C:\SQLTRACE on the server.

 

STEP 8 - Configure and Schedule Performance Counter Logging on Database Server

Please follow this link for information on setting up Windows Performance Monitor: http://blogs.msdn.com/b/axinthefield/archive/2016/01/05/setting-up-windows-performance-monitor-templates.aspx

 

 

 

 

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

Check the CAPTURE_LOG in the DynamicsPerf database

Open the Monitor DynamicsPerf Health script in the Installation Solution

2

Review collected data

Open the Analyzer Performance solution and review collected data

How to Capture Performance Data Manually?

DynamicsPerf 2.0 no longer requires this feature. The new scheduling engine makes running sp_capturestats manually obsolete.  It is collecting data every 5 minutes for some tasks.

You would use this feature when you want to collect all data regardless of the schedule.  Manually capturing data overrides, the schedules.

  1. 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.0 for Microsoft Dynamics.ssmssln file 5. In Solution Explorer, open the Manual –CaptureStats.sqlscript

USE DynamicsPerf

EXEC SP_CAPTURESTATS

@DATABASE_NAME = 'XXXXXXXXX'

--, @DEBUG = 'Y'

5.Change the @DATABASE_NAME to the name of the Microsoft Dynamics database name (Example: ‘MicrosoftDynamics’)

6. Execute the script against the DynamicsPerf database

 

How to Monitor the Health of DynamicsPerf data gathering?

We have provided a script to monitor the data collection process.  The script is called Monitor DynamicsPerf Health.sql in the installation solution.  This script will show the CAPTURE_LOG for all events.  It also displays the databases that have been setup to collect.  The 3rd dataset is the Task History table which has a column LAST_RUN which displays the last time the task was run.  You can review this data to verify data collection is setup and running without issues.

image

image

Regards,

Rod Hansen

Comments (22)
  1. Aurelio says:

    Do you know some tool to to diagnose performance for a new instalation of Dynamics AX, i mean a tool to simulate 100 users, and runs for several weeks simulating transaction from every AX client?

    That´s could be for 100 or 500 users, financial, Sales orders, purchase orders, sales invoices, purchase invoices, payments transactions, etc. Just like bots.

    At the end we need performance statements.

    1. Hello Aurelio,

      You may want to have a look at this blog post for some info on how to load users for a benchmark:
      https://blogs.msdn.microsoft.com/axinthefield/benchmark-your-dynamics-ax-solution-with-load-testing/

      Regards,
      @BertrandCaillet

    2. Dear Aurelio,

      if you look for a non-developer and easy to implement full blown solution for performance/loadtesting or even functional and Regression testing, take a look on Executive Automats. http://ea.dynamicsautomated.com/.

      Regards,
      Michael

    3. Dear Aurelio,

      if you look for a non-developer and easy to implement full blown solution for 100% realistic performance/loadtesting or even functional and regression testing, take a look on Executive Automats. http://ea.dynamicsautomated.com/.

      Regards,
      Michael

  2. Michael says:

    If we are using v1.2 already, is there a way to 'upgrade' to use version 2.0?

    Thanks,

    1. Rod Hansen says:

      There is no upgrade from 1.20 to 2.0. The schema is WAY to different to attempt to upgrade.

  3. Terry says:

    I'm for some reason not getting any of the info back from most of the 3 - Analyze AX Configuration queries, tables aren't populating. I am getting info back from AX_SYSGLOBALCONFIGURATION but that's it. I'm running DynamicsPerf on a linked server and get most of the info for the other queries if this helps?

  4. sheng says:

    Hi Rob,
    Don't worry about the link. After a bit more googling, I found the link.
    https://blogs.msdn.microsoft.com/axinthefield/setting-up-windows-performance-monitor-templates/

  5. Sheng K says:

    Hi Rob,

    Excellent article.
    Currently I am at step 8: Configure and Schedule Performance Counter Logging on Database Server.
    The html link is not valid anymore as it show "Oops! That page cant be found"

    Can anyone please help?

  6. Executing "1-Create_Core_Objects.sql" failed for me. I had to create "sa" login. Then it executed without errors...

  7. AndreHociko says:

    Hi Rod!

    In step 4, we have an option to choose the Azure DB (Yes | No); For this question, should I consider a SQL installation in an Azure VM or the SQL Azure (SaaS) product?

    Thanks in advance! 🙂

  8. After Step 8 and running check that data is being collected by doing the following

    Open the Monitor DynamicsPerf Health script in the Installation Solution, I can see nothing being collected any one know why this might not be working SQL 2012?

    When running the manual Test and running the same I can see data has been collected.

  9. Martin says:

    Hi, I do have one additional question - will step 6 also work without the FTS engine installed? I just ran the script without any errors on a database without FTS engine, and received no error messages - so I guess it's OK? Many thanks, Martin

  10. Martin says:

    Hi, just a short suggestion, I found the desrpction for step 4 to be a bit misleading.
    I see that I have to replace the ‘SQL_NAME_HERE’ and the ‘DB_NAME_HERE’ twice, and not just once as shown in the screenshot.
    You may want to enhance here. Leaving that tiny bit aside, great documentation, thanks!

    1. Yes spotted that you have to change the details twice today as well

  11. dwilkersgjt says:

    Is there a way to manually run a purge of old data. I changed the length of time data should be kept, but I don't know that it is purging data as I would expect it to. The last run of the DYNPERF_PURGE_DATA ran in less than 10secs. I would think it would run longer than that if it had to clean up quite a bit of data if I cut the retention time in half.

  12. Waldemar Pross says:

    Hi there. During installtion, when I've executed the "7-Deploy Extended Events.sql" (DynamicsPerf and AX databases are on the same server) did not create a job in SQL Server Agent > Jobs. No errors in previous steps or when executing job 7.

    I can see 3 DYNPERF_* Extended Events though.

    What could be the reasons why the job is not created?

    Thanks,

    Waldemar

    1. This is because you are running SQL 2012 and you don't need the Agent Jobs is my understanding 🙂

      If you where running SQL 2008 and had run 7a you would have the agent jobs.

  13. Hi Rod,

    is it possible to upgrade a previous 1.2 Database without loss of data?

    Best regards,

    Stefan

  14. Chris says:

    Hi Rod,

    I can't seem to get the number sequences collection to work.  I've checked the schedules and it all seems to be there as if it'll collect every 1 HH but running a select distinct stats_time from ax_num_sequences only returns a 17:00 collection from the day of install.

    Any ideas?

    Chris

  15. Tom says:

    Hi Rod, unfortunately the SSRS-Parts is not working on my Server. I have the AX database, the Dynamics Perf database and also the Reporting database on the same Server. In my case there is no linked server necessary, isn't it?

    The collection Job is running every 5minutes, but no data is appearing in the table SSRS_ExecutionLogs.

    I have this issue on 2 different installations (out of 2 trys).

    Can you help me?

    Thank you,

    Tom

    1. Luc says:

      To get the SSRS capture to work, I modified the stored procedure DYNPERF_COLLECT_SSRS_EXECUTIONLOG and added this statement exec (@SQL) in the IF @REMOTE_SERVER = 'N' block, around line 224.

Comments are closed.

Skip to main content