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.
WIK_SlowPerformingStatements_Job