Dynamics AX 2012 R3 CU13 x SQL Server 2016 Enterprise CU7

(0) Share

Report

Posted on by Rafael Araujo

183

We have migrated the AX to Azure and changed the SQL Server 2014 to SQL Server 2016. The servers is better than the on premise servers, but the performance is so bad! The on premise servers were using the SQL Server 2014, but now is not possible to rollback the migration from SQL Server 2016 to SQL Server 2014. We did some tests, but nothing was detected before the Azure Go Live. We are working to create new indexes, but it is so strange. Anyone have had the same issue?

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

André Arnaud de Cal... 301,025 Super User 2025 Season 2 on at

Like (0)

Report

Hi Rafael,

You have to start monitoring what could cause the issue. Is it latency, SQL server settings, indexes, storage or insufficient hardware resources? You can only find out by monitoring hardware resources and looking at SQL performance reports. You can also ask this questions on the SQL server forum as it is in fact not directly related to Dynamics AX.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Did you use Premium Storage (SSD) for the database servers? That's the first thing to check. I don't think your problem is related to SQL Server version, most likely it's something else. And the most common reason for general slowness in AX is poor disk performance of the database server.

You can use DynamicsPerf tools with PAL tool (Performance Analysis of Logs) to analyze the disk speed and many other things. Basically it uses Windows Performance Monitor to capture certain metrics and then generates a report for you.

Or you can just check the disk related counters yourself. Disk read/write wait time, disk read/write queue length for example.

Was this reply helpful? Yes No
Rafael Araujo 183 on at

Like (0)

Report

We are using the Standard_DS14_v2. This option has SSD disk.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,025 Super User 2025 Season 2 on at

Like (0)

Report

Hi Rafael,

When using this machine type, you have choice to use standard HDD, a standard SSD or premium SSD for the disks. Ensure it is Premium SSD.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Andre is right, DS machines support SSD disks but you have to choose to use Premium Storage when you deploy the system from LCS.

Was this reply helpful? Yes No
Rafael Araujo 183 on at

Like (0)

Report

Hi Andre! We are using the SSD Ultra (Premium).

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Then you can use the DynamicsPerf tools mentioned earlier to gather diagnostics information about your system.

Is it overall sluggish or are only some processes slower than in your old system?

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report
Sql 2016 should be faster. However, how its configured for you we don't know.

I suggest review this help first: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/lifecycle-services/ax-2012/plan-2012-r3-deployment-azure

Particularly these links

SQL best practices have been followed for deployment in Azure, using the Performance best practices for SQL Server in Azure Virtual Machines.

Best practices for SQL Server configuration for AX 2012 have been followed, as specified in Configure SQL Server and storage settings (TechNet).

In general I have poor experience of azure - most systems I have seen have been undersized, and poorly configured.

For Azure VMs running on Windows Server® 2008 R2 and previous releases, the only striping technology available is striped volumes for dynamic disks. Storage space was a new function made available in Windows 8.0+ and Windows 2012+. Which are you using?

IO is key . so ensure that database settings consider how the IO can be reduced.

Enable instant file initialization for data files by granting “Perform Volume Maintenance Tasks” permission to service account for SQL Server. This is necessary to restore a database which has huge database files.

Enable database page compression. This reduces IO by reading more data in less IO.

Disable autogrow and autoshrink for the database. The DBA should plan the database size and grow it periodically and not let SQL grow the file. In the same way, autoshrink is not advisable for a database.

Use Lock Pages in Memory. This setting helps in locking the memory i.e.to avoid paging-out the memory to pagefile.

The C drive should not be used to store any database or SQL Errorlog or default trace file. Once SQL is installed, to move all of these off the C drive to a drive which is part of Data disk.

A standard disk in Azure has IOPs limits. A storage account has a maximum IOPs limit of 20,000. Avoid storing multiple heavily used data disks in the same storage account. Multiple data disks can be used to stripe storage space, or OS striping to increase the IOPs. You can create a single storage pool with 16 data disks. This helps provision 8000 IOPs for the volume. Exceeding the IOPs limit of the given volume would create random high latency.

If you have not configured the disk properly, then you will see error log messages:

You can also try the steps outline here: https://blogs.msdn.microsoft.com/axinthefield/dynamics-ax-performance-step/

Was this reply helpful? Yes No
Saleh ghoshehZ 5 on at

Like (0)

Report

set max degree of paralllelism = 1 on DB level and enable trace flag 9481

Was this reply helpful? Yes No