SQL Server - The heart of performance for Dynamics AX

With all the wonderful excitement surrounding the release of Microsoft Dynamics AX 2012, I thought it was time to take a step back and focus on something that can benefit those running on AX 2009, as well as AX 2012. What I'm talking about here is performance!

Before I get into some of the really good resources, to share with you on this topic, performance for SQL Server comes down to three area's: 1. Having enough RAM, 2. Right amount of Cores and 3. A great disk I/O Sub-system.

The last one, 3. A great disk I/O Sub-system, is usually always the issue when it comes to performance related problems in an AX solution. I can't stress this enough! Therefore, we have some good resources now, published by Microsoft, that everyone using AX, no matter what version, needs to understand. Make sure to work with your partner to get the following information, applied correctly to your instance of AX.

First off, we have the newly updated checklist / whitepaper for things to make sure you have set on your SQL Server. Microsoft Dynamics AX 2009 White Paper: Planning Database Configuration

Now don't be fooled you AX 2012-ers, this is also for you. The title has not been updated, but the content still applies. There is some great points in here, around RADI 1+0 over RAID 5, splitting your TempDB into as many physical files as you have Cores, etc. Read it, understand it, and make sure for your instance this is applied.

Next up, we have two trace flags. The first I covered last year, but still needs focus for this area. That is: Important SQL Server Change! - Parameter Sniffing and Query Plan Caching

Based on research by the Microsoft perf team, some great benefits have come from applying the above trace flag to a SQL Server. Next, we have a new trace flag, that works in conjunction with the TempDB split advice. This next trace flag, will make sure that all TempDB files grow at the same time, and with the same size increase. This too, has been proven, when you have your TempDB split out, to increase performance. Consider Enabling Trace Flag 1117 on Dynamics AX SQL Server

With this arming you as weapons, you can slay the nasty performance dragons, with ease! Of course, there is still nothing that can be done for bad Query design, or coding that forces huge resultsets, or many trips to the database. That must be addressed, and using the Trace Parser in AX 2012 is just a wonderful thing. Microsoft did a great job of making that tool, very useful, and very, very easy to use.

That's all for now, but check back soon as more to come. Till next time!

"Visit the Dynamics AX Community Page today!"