Microsoft Dynamics Technical Conference, 2016 / Day 2 Session 2

My report from the technical conference.


Our second session on the second day, Wednesday February 24, 2016, was a breakout session, called Microsoft Dynamics AX 2012: Performance tools overview and best practices, with Tom Treen, Senior Support Engineer.


Yes, I went back to 2012, because who couldn’t use performance tips?


Tom has a few favorite tools, which you will see mentioned here. He also provided several links for downloads and more information, do make sure to check them out!



  • Performance monitoring tools
  • Setup and configure
  • Performance configuration best practices


Performance monitoring tools

  • Dynamics Performance Analyzer
  • Microsoft Dynamics AX Trace Parser
  • Performance Analysis of Logs (PAL) tool
  • SQL performance dashboard reports



Microsoft Dynamics Performance Analyzer

V2 just came out, it’s still in Beta

We are catching data more frequently


  • Database performance tuning
  • Microsoft Dynamics AX setup & best practices checks


  • Database setup and settings
  • SQL query details and analysis
  • Missing indexes
  • Table setup and caching
  • Number sequence analysis
  • Hidden index scans


Microsoft Dynamics AX trace parser


  • Microsoft Dynamics AX client and AOS trace file analysis


  • Drill down view of captured sessions
  • Captured SQL statements
  • X++ methods
  • Comparison between traces
  • Sub-trace creation


Performance Analysis of Logs (PAL) tool


  • Windows performance monitor automated analysis


  • Template generation for system, SQL, Microsoft Dynamics AX Counters, plus more
  • HTML report produced form analysis
  • Ability to queue up analysis for multiple files
  • Multi-threaded


SQL performance dashboard reports

Went a long time without getting updated


  • View live and historic data SQL Data


  • SQL server management studio report
  • Simple setup
  • SQL query history
  • IO statistics
  • Wait stats


Demo: Setup and Configure

Dynamics Perf is a download

Now installation solution and analysis data solution

Open the installation solution

Take a look at their views in SSMS and at SQL Server agent jobs

Check the RunAs for permissions

There is an xpo you should load and incremental CIL and run – set up as a recurring batch job

Open analyze data solution / project

Max degrees of parallelism should be 1, unless you’ve got 16 cores, then 2-3

Check max server memory – sometimes you need to adjust it down

Distribute the load

Trace [illegible]

Suggested indexes: don’t necessarily create all that are identified there

AX server configuration > performance > 128

Number sequence form – cache allocation


PAL tool

Threshold file

AX tracing cockpit

Services – start trace, do stuff in ax, stop trace


Trace Parser


SQL performance dashboard

Free download

IO statistics is a great report


Performance configuration best practices

General SQL settings:

  • SQL Trace flags – normally recommend 1117, 1118, 1224, 2371 and 4199
  • Review max server memory
  • Ensure that max degrees of parallelism is set to 1. For higher (16+ CPU servers), try increasing this to 2 or 3
  • Within local security policies, set the “Lock pages in memory” and “perform volume maintenance task” has been assigned to the SQL server service account
  • Check for maintenance plans for index rebuilds and statistics updates
  • Index fill factor


  • Recommend that you have one data file for each CPU core, up to between 8 to 12 files


Database settings for the Microsoft Dynamics AX DB

  • Check the SQL collation matches the TempDB collation
  • Check if read committed snapshot on
  • Check auto update stats, auto create stats are on
  • Make sure that auto shrink and auto update stats async are off

Microsoft Dynamics AX settings:

  • Ensure that AOS debugging is disabled
  • Review number sequences – where possible do not use continuous sequences, and use fetch ahead for the most used non-continuous
  • Review the entire table cache settings and set the AOS server cache to 128KB
  • Parameter sniffing fix
  • Review database logging and alerts
  • Disable fact boxes


Download Links

Dynamics Performance Analyzer

Performance Analysis of Logs (PAL) Tool

SQL Performance Dashboard Reports


Info Links

Dynamics Performance Analyzer


Microsoft Dynamics AX parameter sniffing fix


Link Microsoft Dynamics AX users to SQL SPID



Happy DAXing!

Seattle cityscape, from Denny Park


About janeteblake

Dynamics AX developer
This entry was posted in AX2012, Dynamics AX and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s