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!"

SSRS Performance and scaling

Now that we have dove deeper and deeper into the BI world, one of the Technical things that needs to be understood by someone who would be implementing and administarting a companies BI solution, is proper planning of hardware, and also scaling-out for performance.

That means, that someone would need the ability to understand SSRS, how to scale, when to scale, differences in version between SQL Server 2005 and 2008 for SSRS, etc.

So lets start with an older article from MS TechNet. Planning for Scalability and Performance with Reporting Services

This article was wrote in 2005, focusing on SSRS for SQL Server 2005. Still the knowledge that can be gained from this article is a good bit, and a great place to start.

This document covers scaling up vs. scaling out, the technologies used with SSRS and getting into specifics.



Like some of the basics for SSRS performance would be spliting out the Report Catalog (SQL Serve Databases) from the Report Server (IIS and SSRS Service). That would be an example actually of a scale out.

More true to the term of a scale out would be though, taking the Report Server, and having a few different boxes that run the Report Server role, and load balance them for execution. These all would point to the same Report Catalog, and therefore could connect to Dynamics AX.

You can see this from the following:
"The remote configuration eliminates contention for machine resources between the Report Server and the SQL Server hosting the catalog. However, you must provide adequate network bandwidth between the Report Server and the catalog server.

Scale-Up and Scale-Out

After you split the catalog to another system, you can choose to either scale up the Report Server, by adding processors, or scale out by adding machines. Figure 4 illustrates a scale-out configuration that uses multiple Report Servers to access a single catalog."


After you get a grasp at this level, the next step would be to go a little further with the when to scale out, and there is another article found here: Reporting Services Scale-Out Architecture



That one goes into more detail about the scale out architecture, and also covers the max number of concurrent users SSRS can handle, and how this increases when your scale out.

After getting this under your belt, the next level would be to understand the comparison between SQL Server 2005 and SQL Server 2008 SSRS. For that, a very detailed analysis was done over at SQLCat.com. The article for that can be found here: Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned

From the post:
"Http Errors vs. Request Time-Out Errors

To better understand the hardware limits we hit with Reporting Services 2008 compared to Reporting Services 2005, we compared the HTTP 503 errors we saw with Reporting Services 2005 with the Reporting Services time-out errors that we saw with Reporting Services 2008. We discovered that while the failure rate for reports with Reporting Services 2005 was directly correlated with HTTP 503 errors, the failure rate for reports with Reporting Services 2008 was directly correlated with time-out errors. These time-out errors, combined with our analysis of the disk queuing on the data source, indicate that the data source was unable to keep up with the request for data from the front-end servers."


So This is all very important to understand, or have someone udnerstand who will be helping you get the best out of your SSRS part of your overall Dynamics AX 2009 solution.

Well check back soon, performance tunning Dynamics AX 2009 will be getting a lot of focus from my post. I will also be adding another Dynamics AX BI pratical post, focusing this time on making use of OLAP Cubes, and creating a custom cube for use within your Dynamics AX 2009 instance.

See you then!




"Visit the Dynamics AX Community Page today!"