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.
I am sure all fellow developers have faced the frustrating problem of not being able to insert certain objects when importing an XPO or comparing different models, layers. Today I will show you how can you insert the Table Delete actions in code compare window for AX 2012 R3.
Modify \Classes\SysTreeNode\mergeInsertSubnode method in the AOT as per below:
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.
The setup of notifications for batch jobs are quite simple if you know where to look. To enable batch e-mail alerts from code all you need to do is create new records under \\Tables\BatchJobAlerts with the right flags for an AX user, who has their e-mail address set in the User Options correctly.
The following X++ Job is self-explanatory on how to enable batch e-mail alerts from code in case of Cancellation or an Error:
static void WIK_enableBatchJobAlerts(Args _args)
{
#Batch
Map alertsMap;
Map alertsEntity;
BatchJob batchJob;
BatchJobAlerts batchJobAlerts;
while select batchJob
where (batchJob.Status == BatchStatus::Executing
|| batchJob.Status == BatchStatus::Waiting)
// Optional filtering for dedicated batch service account only
&& batchJob.createdBy == 'axbatchuserid'
notexists join batchJobAlerts
where batchJobAlerts.BatchJobId == batchJob.RecId
// Include all batch jobs where e-mail alert is disabled for our account
&& batchJobAlerts.UserId == batchJob.createdBy
&& batchJobAlerts.Email == NoYes::Yes
{
// This can be called any number of times to alert multiple users even with different settings
alertsMap = BatchJobAlerts::addAlertsToMap(
batchJob.createdBy, // Whom to alert
NoYes::No, // Ended
NoYes::Yes, // Error
NoYes::Yes, // Canceled
NoYes::No, // Popup
NoYes::Yes // E-mail
);
BatchJobAlerts::saveAlerts(batchJob.RecId, alertsMap);
alertsEntity = alertsMap.lookup(batchJob.createdBy);
info(strFmt('Enabling alert (Ended=%1 Error=%2 Canceled=%3 Popup=%4 E-mail=%5) for user <%6> and batch <%7>',
enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobEnded)),
enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobError)),
enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobCanceled)),
enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#popup)),
enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#email)),
batchJob.createdBy,
batchJob.Caption
));
}
}The output will be something like this:
Enabling alert (Ended=No Error=Yes Canceled=Yes Popup=No E-mail=Yes) for user <sa.axbat> and batch <ADMIN – Due date alerts – every 15m>