Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]

Updated on 09/09/16 for DynamicsPerf v2.0.


This check list is primarily aimed at troubleshooting general performance issues in Microsoft Dynamics AX 2009 and 2012. ‘General’ here typically means a set of unidentified issues across one or more modules, or indeed the entire application. However it is good practice to have  at least a quick check of set up and settings when working with any performance issue. It is important to always have a good foundation to diagnose issues from.

It is based on issues encountered on support and best practice documentation. As always, you should be sure that you fully understand the impact of any changes you make and implement them in your test environment first.

Part 1 covers SQL and AX configuration and is intended to give you the foundation. This in turn is split into 2 parts:

  • SQL Server and Storage Settings (this page).
  • AX application and AOS configuration (page 2).

Part 2 includes hardware, indexing, queries,  blocking and code (at a high level). This naturally involves deeper analysis, which will be iterative and is probably where the vast majority of the time will be spent – but it requires a solid foundation (i.e. part 1).

You can find Part 2 here.

It is suggested that you analyse/implement changes in three phases – Part 1, Part 2 (except code), code review, however each phase may overlap. You should also plan to spread out your deployments (of any remediation activities for this) as much as possible, for easier diagnosis and reversibility in case of any problems.

Within each section below, the links under ‘Recommendation’ relate to further details, while the links under ‘How to…’ relate to implementing the changes. You should ensure you have the information relevant to your software versions.

With each part, there will be a link to an analysis script which you can use to help gather the information from your system. These scripts require the Performance Analyser to be installed:


Part 1A: SQL Server and storage settings

Analysis script:

Infrastructure / assumptions:

-You are following the documented best practices for Windows Server and SQL Server.
-You are using a dedicated server that is appropriately sized according to the workload and meets the relevant system requirements (above).
-You are using a single instance of SQL Server that is dedicated to running the Microsoft Dynamics AX production databases.
-We recommend that you store your test and development databases on a separate server from the production databases.
-SQL Server settings: Affinity Masks and Priority boost are left as default (automatic and off respectively).

-While there are clear benefits from using virtualisation, it has been known to degrade performance under high load scenarios; if the virtualisation is not optimally configured then obviously the severity can increase. See “Virtual server support” in the system requirements.
See also: Hyper-V Benchmark for Microsoft Dynamics AX 2012
How to…
-Refer to your hardware/infrastructure vendor for advice.



-Verify that SQL Server is configured to run as a background service in Windows.
-Set the power plan to ‘high performance’ (all AX servers):
-[AX 2009 only] In Windows Server 2003, set the Specify memory usage option to Programs.
-[AX 2009 only] If you are using Windows Server 2003 with AMD processors, ensure that boot.ini contains the parameter /USEPMTIMER
How to…
Configure Application Performance on Windows Server 2008 R2:
SQL Server Instance

-Set max degree of parallelism to 1 (for normal Production operations)
-Max Server Memory: make sure that sufficient memory is available for the operation of Windows Server. Use the Memory: Available Mbytes performance counter for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods
-AWE enabled on large memory 32bit systems [AX 2009]
-Check for large TokenAndPermUserStore (> a few hundred MB)
How to…
Max Degree of Parallelism Option:
Windows Performance Monitor:
Memory Architecture [AWE]:
SQL Server Service

-Confirm that the account for the SQL Server service has been granted the Lock pages in memory privilege.
-Configure the account for the SQL Server service for instant file initialization.
-Enable only the required network protocols – AX only requires TCP/IP.
-Disable hyper-threading.
How to…
Enable the Lock Pages in Memory Option (Windows):
Database Instant File Initialization:
Enable or Disable a Server Network Protocol:
Disabling of Hyper-threading must be performed in the BIOS settings of the server. For instructions, see the hardware documentation for your server.
TempDB storage

-Set a specific value (MB, not %) for autogrowth (safety mechanism, i.e. “emergency release valve” only)
-1 tempdb file per processor.
-Isolate tempdb on dedicated storage (highest speed possible)
-Determine the size of the tempdb data files and log files
How to…
Optimizing tempdb Performance:
ALTER DATABASE File and Filegroup Options (Transact-SQL):
Move System Databases:
AX Database Configuration:

-Set COMPATIBILITY_LEVEL to 110 for SQL Server 2012, or to 100 for SQL Server 2008 or SQL Server 2008 R2
-Make sure that the AUTO_SHRINK option is set to off
-All Microsoft Dynamics AX databases must use the same SQL collation.
How to…
ALTER DATABASE Compatibility Level (Transact-SQL):
ALTER DATABASE SET Options (Transact-SQL):
Server Configuration – Collation:
Configuring physical storage

-Disk sector alignment: partition offset value must be a multiple of the stripe size (i.e. partition offset / stripe size resolves to an integer). File allocation unit size: bytes per cluster should usually be 64KB.
-Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10 (RAID 10 recommended).
-Store the data files for the Microsoft Dynamics AX database on separate physical stores from transaction log files.
-Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.
-Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.
-Virtual log files for each database log file. VLF_Count > 10k requires attention.
How to…
Seek guidance from your SAN vendor or if they do not provide specific recommendations, refer to:
Disk Partition Alignment Best Practices for SQL Server
Seek guidance from your SAN vendor regarding RAID configuration.
Move User Databases:
Move System Databases:
SQL Server Transaction Log Architecture and Management (VLF_Count):
Trace flags

-Generally for SQL Server the recommendation is only to implement trace flags to address the specific issues they are designed to address, however for AX the following are known to have a beneficial impact on performance and should therefore be considered:4199; 1117; 1118; 1224; 2371;7646*
-Establish the reasons behind any other trace flags being enabled.

*7646 does not apply to SQL Server 2012 and above.


How to…
Verify which trace flags are turned on:
If any of the mentioned trace flags are not turned on (see left), consider adding them as start-up parameters:
Configure Server Startup Options (SQL Server Configuration Manager)
Parameter sniffing fix (dataareaid & partition literals)

-Ensure you have implemented this important fix for general performance in AX.
How to…
SQL Server Parameter Sniffing with Dynamics AX, just plain evil:
SQL Server build

-Most recent supported Service Pack together with a recent cumulative update.
How to…
First check the system requirements (above link) for the latest supported service pack. To obtain the above service pack, search for “How to obtain the latest service pack for [your SQL Server version]”, e.g. “How to obtain the latest service pack for SQL Server 2012”:
followed by:
“The [SQL Server version] builds that were released after [SQL Server version] [above Service Pack] was released”, e.g.: “The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released:”

-In part 1 the action is just to list non standard triggers – investigate these in more detail in part 2.
How to…
SQL script:
SQL Server Agent jobs

-Is there a database backup job (among other reasons, transaction logs need regular backups to keep the sizes small)
-Ensure that you have an appropriate database maintenance strategy.
-Are there jobs that could stress the server? In part 1 the suggested action is just to list them, then investigate these in more detail in part 2.
How to…
Plan backup and recovery [AX 2012]:

Planning backup and disaster recovery[AX 2009]:

Database Maintenance Strategies for Dynamics AX:
Data archiving/purging

-Keeping the amount of data in your database to a minimum can help with performance.
-Other than those mentioned in the blog (see right), also consider the following key tables:

  • Logs and batch tables under Administration/Inquiries [AX 2009] or System Administration/Inquiries [AX 2012]. For SQL statement trace log (SysTraceTableSQL) there is also a SQL Server Agent purge job in the Performance Analyser tool.
  • InventSumLogTTS table: run MRP with full regeneration periodically (or turn off Master Planning configuration key)].
How to…

Database Maintenance Strategies for Dynamics AX:

How to accurately predict database growth in Dynamics AX: