Getting the most out of Dynamics AX - Performance I

Alright, so last week I left you would a post about SSRS performance and scaling. This was to kind of wrap up the long series I have been doing on SSRS, which is a part of Dynamics AX BI and Reporting.

I will continue to write about reporting, and get into more pratical post about it. I wanted to also take the time to start a new series of post 'Getting the most out of Dynamics AX - Performance'.

In this series of post, what I would like to do is talk about performance tuning a Dynamics AX instance. I have thought about my approach to this and considered several different ways.

After looking at this from different ways, I want to address this topic in the manor in which this topic exists, Dynamically.

So in order to deliver a series of Dynamic post about performance tunning and scaling of a Dynamics AX instance, we have to look at this from a scenario approach.

Before we get into the first scenario though, it's important to understand what I mean by 'addess this topic in the manor in which this topic exists.'

What I mean by that statement is that performance tunning is not a single, clear, happyily wrapped box. It's a very Dynamic, instance specific on-going series of events that happens over the lifetime of an instance.

There are certain tools that are used in common across instances, like SQL Server Profiler and the Data Tunning Engine. Like using Query Plans to analyze use of fields, indices, etc. Also taking account events, and performance bench marks about CPU, Disk and Network I/O, RAM usage and need, etc. etc.

All of this must come into play, and you must be able to paint in your head with these tools, logs and so forth a picture of the performance your given Dynamics AX instance is experincing at the time.

So lets take a given scenario now and move from there:

You started out with a single, decent size SQL Server, two AOSes, and a few custom applications. Your concurrent users stayed at 50 or below. But now it's been over 1.5 years since you went live, a lot more custom reports and code has been done by your internal staff and you VAR. You also have 100+ concurrent users. MRP and other processes are running a long time, and custom code, processes and report ar taking extreme amounts of time to complete.

What to you do?

First you need to take and write down a list of all the processes, including reports and noting modules and times that are slowing down or taking a while to complete.

Then you need to take that information, and build yourself a plan of attack for what to look for.

While your building your plan of attack start your SQL Server Profiler Trace run, and make sure to get all the right columns and objects. Also make sure to start performance monitoring for CPU, RAM, Disk and Network on your SQL Server.

Let things run for around 4-8 hours depending on how serve, and how much data your Profiler trace is capturing. Take this captured information, along with your performance information and now you can load both up into profiler and look at the times reported slow downs are taking place.

Then attack the known processes and reports that have slowed down, and look at the queries that are executed as part of those. Use the Data Tunning Advisor on those queries, making sure correct indexes exist, noting ones that need to be created and creating them.

The idea, if you can tell from what I am saying so far is start with your SQL Server database. And look at the interaction between AOS / Dynamics AX and SQL Server Database. This means look at XppSQL, making sure that there is not a lot of unneeded while select looping statments. Making sure that there are covering indexes for your where clauses, and that you make use of joins.

Again, focus on the SQL Server, but that also means the code that is being executed from the AOS and the objects they are hitting or should be hitting (like missing indexes).

There is plenty of information on the web for how to use the SQL Server Profiler, how to read a Query plan, and make use of the DTA. Just keep in mind, with all the information, you have to read into to it. Don't just randomly accept the suggestions from the tools, like DTA. Investigate if what is being suggested is valid.

The other side to this SQL Server focus is looking at the possibility that you need to increase the RAM on the SQL Server, or maybe CPU power. The performance monitor will tell you this.

Alright, we will stay with this scenario and dive deeper. This is a good start to think about performance, and start getting your mind around what it takes to be able to address performance problems.

The mind set must be, it's a Dynamic set of on going events. This can't be a static thing that happens once. You don't have to deal with it everyday, but just because you get things running smooth and fast, does not mean that will last for 3-6 months. If you add a lot, change a lot, etc. then what ever is changing and adding needs to be investigated for best performance possible.

I will continue this series, and stick with the scenario later this week. To make this more Dynamic, if you have questions about getting the most out of your Dynamics AX instance, feel free to leave me a comment or email me through my profile. If you leave a comment, leave a way for me to contact you. Also if you wish the comment remain private, for email spam protection, etc. make sure to note as well in the comment and I will not publish the comment.

That's all for now, see you soon!



"Visit the Dynamics AX Community Page today!"