Picking list registration performance

We recently have implemented a new Picking application in our warehouse. Our slow performing statement e-mail has immediately highlighted that with our data volume the WMSOrder and SalesTable records were underperforming, taking 5+ seconds each time when the form was opened (close to 20). We needed to fix the Picking list registration performance.

WMSOrder

Apparently it is a standard AX form and table, but the link which they have been using is incorrectly defined, resulting in an InnerJoin for SalesTable unnecessarily.

Picking list registration performance

Also the field used for the join has no index, you should create one for WMSOrder.InventTransRefId to gain more performance.

After the adjustment it looks much better and performs really fast:

WMSOrder

 

DAXRunBase / 2018-02-27 / AX 2012 / 0 Comments

DAXRunBase / 2016-08-16 / AX 2012 / 1 Comment

E-mail alerts for slow performing SQL statements in AX

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.

alerts for slow performing SQL 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

 

DAXRunBase / 2016-01-07 / AX 2012 / 4 Comments