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!

 

Agenda

  • 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

Uses:

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

Features:

  • 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

Uses:

  • Microsoft Dynamics AX client and AOS trace file analysis

Features:

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

 

Performance Analysis of Logs (PAL) tool

Uses:

  • Windows performance monitor automated analysis

Features:

  • 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

Uses:

  • View live and historic data SQL Data

Features:

  • 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

TempDB:

  • 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 https://dynamicsperf.codeplex.com/

Performance Analysis of Logs (PAL) Tool https://pal.codeplex.com/

SQL Performance Dashboard Reports https://www.microsoft.com/en-us/download/details.aspx?id=29063

 

Info Links

Dynamics Performance Analyzer

http://blogs.msdn.com/b/axinthefield/archive/2015/12/30/dynamicsperf-2-0-deployment-guide.aspx

https://blogs.msdn.microsoft.com/axinthefield/2015/12/30/dynamicsperf-2-0-installation-for-dynamics-ax/

 

Microsoft Dynamics AX parameter sniffing fix

https://blogs.msdn.microsoft.com/axinthefield/2014/01/09/sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil-redux-5-19-2014/

 

Link Microsoft Dynamics AX users to SQL SPID

https://technet.microsoft.com/en-us/library/hh699644.aspx

 

 

Happy DAXing!

Seattle cityscape, from Denny Park

IMG_0798

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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