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
Microsoft Dynamics AX parameter sniffing fix
Link Microsoft Dynamics AX users to SQL SPID
https://technet.microsoft.com/en-us/library/hh699644.aspx
Happy DAXing!
Seattle cityscape, from Denny Park
janeteblake 