performance Archives - Page 2 of 2

SQL data compression for Dynamics AX 2012 (Part 2)

It has been a while since I have touched up on SQL data compression in part 1 on my blog. It is time to re-visit the topic.

SQL data compression for Dynamics AX 2012 (Part 1)

Since that post Microsoft has published an excellent series of articles on how to apply compression within AX, or directly on SQL Server. It is a must-read blog post for everyone, so start there:

https://blogs.msdn.microsoft.com/axinthefield/sql-server-data-compression-for-dynamics-ax-0-blog-series/

Since JJ Food Service has a very large database, we have started applying compression to Production AX. Our SalesLine and CustInvoiceTrans has been between 250-350 GB size each! Our approach was to apply compression before a maintenance window using the Enterprise Edition license of SQL Server in ONLINE mode and using TempDB sorting, so it was running during business hours only adding a slight overhead to the system (mostly as disk I/O). It took 4 and 6 hours respectively, and space saving was more than 75% which is excellent.

Once the compression has finished and we were about to begin our regular Friday night maintenance, once the AX AOS, reporting and web services were all down, we could run the post-compression SQL script provided by Microsoft to populate the SQLStorage table. This is required for the Data dictionary synchronization step to recognize compressed indexes, so it would not drop and recreate them uncompressed.

Eventually we will consider compressing our whole database for Page level. The reason being is that Dynamics 365 for Finance and Operations, Enterprise edition in the background uses full page compression for the whole AX database. If you would like to use the upgrade scripts from 2012, even the documentation mentions that compression is a pre-requisite step:

https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/prepare-data-upgrade

As a result we have seen great performance boost for the tables involved and much lower numbers in the slow performing SQL statements, since more data could be kept in the SQL memory buffer.

By DAXRunBase|2018-02-27T09:45:41+01:00February 27th, 2018|Categories: AX 2012, Dynamics 365 for Finance and Operations|Tags: AX 7, compression, Dynamics 365, migration, performance, SQL|0 Comments

Refresh AX grid color with displayOption

We have a displayOption implemented for the Vendor transactions screen, which highlights the rows in red color where the posted transactions or the journal has a Document attachment. The requirement was that if we create a new DocuRef entry on the vendor transaction, after closing the DocuView form the grid should change color. In order to refresh AX grid color with displayOption only for the currently selected row we can use the FormDataSource.clearDisplayOption method.

Also there is a great comment on the Dynamics Community about refreshing a set of entries, instead of the current cursor:

https://community.dynamics.com/ax/f/33/t/191245

We have edited \Forms\DocuView\Methods\close as per below to achieve refreshing only the currently highlighted row:

public void close()
{
    FormRun         frm = infolog.parmLastActivatedForm().object();
    FormDataSource  fds;
 
    DocuRef::multiSelectRecordDelete();
 
    SysHelp::initWebBrowser(htmlView);
    curUrl = "";
 
    super();
 
    if (infolog.docu() && infolog.docu().docuView() && infolog.docu().docuView().object())
    {
        infolog.docu().clearDocuView(); //ensure that form is removed from infolog object
    }
 
    if (frm
        && frm.form().name() == formStr(VendTrans))
    {
        fds = frm.dataSource(1);
        fds.clearDisplayOption(fds.cursor());
    }
}

By DAXRunBase|2017-02-20T13:32:54+01:00February 20th, 2017|Categories: AX 2012|Tags: AX 2012, control, displayOption, performance, X++|0 Comments

Slow performing SQL statements in AX – Update

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:

E-mail alerts for slow performing SQL statements in AX

By DAXRunBase|2017-09-12T07:59:17+02:00August 16th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, e-mail, performance, SQL, statements|1 Comment

AX database tuning and maintenance – How to keep it healthy

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:

https://blogs.msdn.microsoft.com/axinthefield/top-10-issues-discovered-from-dynamics-ax-health-check/.

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:

Improve AX performace by fixing bad Query plans

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.

(more…)

By DAXRunBase|2017-09-12T07:46:44+02:00May 11th, 2016|Categories: AX 2012|Tags: AX 2012, compression, database, maintenance, performance, SQL, TempDB, trace flags|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.

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

By DAXRunBase|2016-08-16T11:09:44+02:00January 7th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, e-mail, performance, SQL, statements|4 Comments