There has been some compatibility improvements and fixes on SQL Server 2014 for the slow performing statements in AX job. The updated version can be accessed from the original article:
It is very crucial for your environment to audit, evaluate, then carry out an AX database tuning and maintenance task regularly in order to keep Microsoft Dynamics AX in a good shape.
There are many areas which are frequently being overlooked by partners and customers, for which Bertrand from the Microsoft PFE team has written an excellent summary that you should read here:
Many of these configurations are very important to keep your environment healthy from day 1. Often overlooked settings include for example the Index fill factor. It causes fragmentation as records are getting created and updated in your database, resulting in slower statement execution times and even timeouts, which is very bad and has been discussed in this post:
If you’ve got very large DBs like we have, you could be safe with setting 90% then doing an index rebuild to not bloat your DB size by too much. Other than that the MS recommendation is between 80-95%.
Having the correct SQL Trace Flags are also equally important, you could read about them again on the PFE blog. We are running 1117, 1118, 1224, 2371 and 4199. Also it is recommended to use the dataAreaIdLiteral AX setting if you have a multi-company environment, to avoid parameter sniffing.
In case if you were wondering what are the modified objects between two AX 2012 databases (renamed, or deleted entries based on OriginId), we could use the Linked Server feature of Microsoft SQL Server to find out.
Our scenario was an AX 2012 RTM Feature Pack installation that is being upgraded to R3 version, and we wanted to find out which objects have been renamed or removed (either DEL_ prefix, or complete deletion) between the two instances with my colleague, Peter Prokopecz.
The solution was to open SSMS and under Server Objects > Linked Servers add a connection for the other SQL instance, then we could use the queries below to find out which are the modified objects between two AX 2012 databases of any versions.
Sometimes the Purchase invoicing department has complained that posting took a very long time to complete. We could correlate this with the slow performing SQL statements alert explained in my previous post to identify the cause, now it is time to improve AX performace by fixing bad Query plans. My colleague, Peter Prokopecz provided some great insights on resolving this problem.
Either an AX trace, or the SQL alert could reveal the bad code. In our case it was the standard functionality of matching Tax transactions with the General journal entries, which can be found in \Data Dictionary\Tables\TaxTransGeneralJournalAccountEntry\Methods\create. It is a very large insert_recordset statement with multiple inner and exists joins:
There are not that many tools to assist us in proactively monitoring our AX database health. I have created a set of SQL jobs that could generate e-mail alerts for slow performing SQL statements in AX to improve the system predictability.
If you are using DynamicsPerf already, it is very likely that the SQL Trace in the User options got already enabled to capture slow performing statements that take longer than 5 seconds, otherwise you could enable that yourself to start capturing data in SysTraceTableSQL in your AX database.
The information stored in the aforementioned table can be analyzed and aggregated to show what tables have been performing slow since the last execution of my job each day. I am also separating wildcard searches, since the LIKE statements mostly do not reuse query plans and you cannot improve their speed much further. It is however a must to keep an eye on the tables with high number of increments for possible outdated query plans, old statistics, missing indexes or poorly designed AX statements.
These are the requirements and steps for setting alerts for slow performing SQL statements in AX:
- Enable SQL Trace with a sensible timeout for the AX users to be monitored.
- Create a database where the daily aggregated slow performing statements and the Table name extracting function will be stored, in my sample code as [master].
- Make sure there is an e-mail profile set up for sending the mails from under SQL Server Management Studio > SQL instance > Management > Database Mail, in my sample code as MYEXCHANGE.
- Create a new SQL Server Agent Job and paste the attached statement as a Transact-SQL body. Make sure you replace the variables and values for the administration database ([master]), the target AX database (MyAXDB), mail profile (MYEXCHANGE) and destination e-mail address values.
- Add a daily Recurring schedule to the Job to occur every 2 hours, or any interval to your liking below 24 hours
- Run the job manually for the first time to create the table/function and pre-populate the records
Once the setup is successfully completed and a couple of slow statements gets recorded in the SysTraceTableSQL table in AX, the next scheduled run will pick up the values and send out the e-mail alert if you have done everything correctly.