Modified objects between two AX 2012 databases

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.

(more…)

DAXRunBase / 2016-03-14 / AX 2012 / 1 Comment

Improve AX performace by fixing bad Query plans

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:

taxtransgeneraljournalaccountentry

(more…)

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

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