Review: "Dynamics AX Performance Optimization Guide"

I hope everyone is doing well this fine Friday! I wanted to spend time today, giving you my insights into a recently published book. That being the title of "Dynamics AX Performance Optimization Guide."



First let me say that the depth on the database & query optimization that is given within the pages of this book is worth the purchase. The focus around optimizing the database, for sure is the right focus. I would say, in the field, 80% of the performance issues I've ran across relate directly to a Dynamics AX instance SQL Server. Further, 60-70% of these performance issues identified, usually relate back to indexes. This could be anything from missing indexes, or not having indexes properly configured and covering for where clauses of select statements.

This book goes into good detail around specific settings, how to trace down resource bottlenecks for a Dynamics AX database and more. It goes into the most commonly used Dynamic Managed Views (DMVs). Further it makes a solid attempt actually on trying to address the virutalization side of performance as it relates to Dynamics AX. Wait types, addressing blocking and other such common performance issues are well addressed in this book.



Now that being stated, like all reviews it can't all be good. You can tell that both Martin Zhen & Daniel Liao have spent time in the trenches chasing down resource vampires. Still even with my recommendation of buying this book already, it lacks in several area's.

First, section 3.11 on page 102 titled "Offload Reporting (SSRS)" is a mere nod at the point of reporting and it's impact around performance. It's a single paragraph, that honestly should have just been left off. There is no depth to this point, and it adds little value to the reader wanting to understand this very important topic.

Finally, it lacks in the focus of performance making use of SQL Server 2012 can bring, and further with it's Always On technology. I would also like to have seen mention around InformationSource. I realize however you can't cover every topic around performance or the book would be about 10 times the size that it currently represents.

Final verdict on this book, 3 out of 5 stars. It's for sure got some really great nuggets of information. It's not just for administrator / DBA / consultant types either. It would benefit well that a Dynamics AX developer, read and understand the contents of this book. It would help them in being able to grasp what their code is doing to the actual underlying data layer from X++ code. Further, helping put forth the practice of using the trace parser when developing anything for Dynamics AX.

Well that is all for this review, many thanks to Martin Zhen & Daniel Liao for completing a very nice book, that helps address and give insight on the majority of where performance issues live - The Dynamics AX Database!Til Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

Freaky Tech Friday - Attack of the Resource Vampires

Welcome back Ghouls & Ghost, for this year's Shock-tober final fray into Freaky Tech Friday's! For those are still with us, you are the lucky souls who have traveled along the dark and twisted path. I end this years haunting season with a tale that strikes at the heart of many Microsoft Dynamics AX implementations. What I speak too is Attack of the Resource Vampires!



These resource sucking entities creep into existence, sometimes slowly, and sometimes right away in an instance of AX. They start with a focus around performance degrading as more and more processes are spun up for a customer. Those unknowing, slowly loose their ability to respond fast to process needs. Reports start to creep with on-set of early aging - as it would appear at least.

The major attack for these creepy creatures is typically a customers Microsoft SQL Server Database. This is the heart of performance, and like any good vampire, resource vampires always go for the heart. It's where the life blood flows...



Fear not though, young AX travelers as there is remedy for this lifeless state one might find their instance of AX in. There is hope for battling back the waves and waves of complaints from end users, as the processes & reports they so dearly need, dwindle into performance nightmares.

In order to fight back the unholy horde of resource sucking vampires, one must be armed with the right knowledge and correct tools. The sacred text tells us that the focus must first be on your SQL Server for getting the most out of your AX Investment from a performance perspective. There is a guide to this, once that applies to AX 2009 as well as AX 2012. You may find access to this guide, to the first step into performance enlightenment here.: Microsoft Dynamics AX 2009 White Paper: Planning Database Configuration. Keep in mind this applies, the same level of needs, no matter virtual or physical.

The idea is to attack the resource vampires foothold, with correct TempDB file settings, having create Disk I/O for Transaction Log commits, and enough RAM to load your entire DB - if possible. Further and beyond that is a focus on indexes. After having your SQL Server set in order, ready to fight, the next level the resource vampires move into is the indexes. They latch onto queries that execute and cause either full table scans or Clustered Index Scans. Both are costly and both can be addressed with equal levels of success. The focus here is around having the concept of "coverage indexes", that is an index that covers the where clause of your queries for the business logic and reports that execute them.

After this the resource vampires start to feel the pressure as your performance story starts to take a turn for the better, instead of one for the worse. Here is where those ugly resource sucking creatures dig in, and latch themselves onto poorly wrote code. We all have sinned in such ways, creating nested while selects that make multiple trips to the database.

The way out from this painful plight is to endure an analysis of the processes being reported as slow, and trace through the code that represents said processes. Find the resource vampires hiding in those nested while selects, or where clauses that have no coverage index. Then address them head on! Take them out, by changing the nested while selects to more complex joins, that reduce the result set being sent from the SQL Server to the end user.

There are other area's that one can look to totally eradicate the evil entities, but the above should be the majority focus for gaining ground and taking control of your instance of AX. Beyond the Server, Database, Indexes and code - sometimes the process has to be reconsidered for desired speed.

Well Ghouls & Ghost, take this warning about resource vampires to heart, and arm yourself's with the knowledge found above to fight back! Further you can download from InformationSource the IDMF tool, that helps with the on-going fight of battling these monters. Further a final warning on this, always stay vigilent in your efforts as performance tuning is a dynamic, on-going process. Let it slip, and before long your knee deep back into resource sucking vampires everywhere you turn!

I hop you have enjoyed this season's gruesome tales of Freaky Tech Friday's! I will leave you with what I looked like after have falling, and becoming a conference zombie. Be warned, the following picture shows the shadow of the man I once was, just before departing for Seattle.




Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

Intelligent Data Management Framework in Microsoft Dynamics AX 2012 - Courseware

Good morning everyone! I wanted to take the time and do a small, quick highlight post about recent courseware that was released on PartnerSource. You will need PartnerSource Access with a Training plan in order to be able to access this courseware.

What I'm speaking about is.: Course 80425 - Intelligent Data Management Framework in Microsoft Dynamics AX 2012

From the description of the courseware.:
" This training provides a thorough look at Intelligent Data Management Framework for Microsoft Dynamics AX 2012. The topics covered in the training include installation, configuration, scheduling management and troubleshooting.

Audience This training is intended for database and system administrators, who are responsible for the administration and maintenance of the Microsoft Dynamics AX application and the Microsoft SQL Server database."



You can find out more around the IDMF offering from this following highlight post I did some time back.: Spotlight: Dive into IDMF for AX 2012

That's all for this morning! I hope you enjoy this new courseware, and helps add to your learning - and driving overall value for Microsoft Dynamics AX 2012 customers! Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Performance and IL - invokeStaticMethodIL

With the release of Microsoft Dynamics AX 2012, a huge focus has been around the move for X++ code to be compiled to IL, as well as creating C# code to achieve development needs, when possible.

Recently, I started talking about Performance in AX 2012, and how this goes beyond the database layer. It's still true, that the majority of performance is around the SQL Server, and specifically the disk I/O Sub-system speed.

However, with that said, there are some great improvements throughout the layers that make up AX 2012. Something recent that I highlighted was around Scale out of SSRS as well as Services.

Keeping with this theme, and to highlight some other area's of performance improvements, lets move towards the code side of things. To help set the stage, lets reference a Microsoft resource: Compile into .NET Framework CIL [AX 2012].

"The Compile into .NET Framework CIL task converts your compiled application code into Common Intermediate Language (CIL) code that can be consumed by the Microsoft .NET Framework."

With this, there are certain area's of AX that always compile down to IL / MSIL / CIL. All of those are in reference to the .Net Framework execution. This means, that the same C# code that actually executes, is compiled down to IL. The same now, takes place for some area's of X++. There are huge benefits in code execution when this is the case, and why this is such an important topic to understand from a performance point of view.

Keeping this in mind, there are sometimes when X++ code does not execute as CIL. Wouldn't it be neat, if there was a way to execute code as IL, X++ code that's not compiled down to IL? There is a way!

Enter: SysDictClass::invokeStaticMethodIL() "Runs the specified method and makes sure that the method is executed under X++ IL."

Now there are some remarks, that need to be understood from the above reference.: "This method demands the XppILExecutePermission class and asserts both the RunAsPermission class and the ExecutePermission class. The invocation will transition to X++ IL by using the RunAs function if it is running as interpreted. If it is already running as X++ IL, the method is invoked directly.

As there may be some overhead, this method should only be used on computationally-extensive methods.

This method will not transition to IL if the current TTS level is larger than zero (unless forced) because the transaction scope does not flow across IL boundaries."

With this in hand now, we have a way to help possibly boost performance, and execute static methods, and therefore area's of code that could benefit from running in the IL, or chunks of Business Logic that can be statically invoked or kicked off. This is a nice little gem of knowledge to keep handy, when your facing down performance issues, and really need to squeeze all the resource juice you can out of a process.

Well that's all for now, but check back soon as a whole lot more to come. Till next time!

"Visit the Dynamics AX Community Page today!"

AX 2012 Performance Improvements

With the release of Microsoft Dynamics AX 2012 there have been so major strides that Microsoft has made in terms of performance. This is not just limited to the scope of, say, the Data layer. That is, this is just not limited to some of the great things Microsoft has done with taking further advantage of SQL Server 2008 / 2008 R2. No this goes well beyond the data layer, as Microsoft has really focused on how performance and scale are in improved at the Data layer, within code and services, as well at the Application layer itself, with items like improvements around MRP.

To help kick off this series of post, live from AXUG, I wanted to highlight a great video that dives into some of these improvements. This video has Microsoft's own Sri Srinivasan walking us through some of these great new concepts.:



I will be diving into these area's as a new series of post, and how these can be applied, used, affect and improve your AX 2012 investment.

That's all I have time for now, but check back soon as a whole lot more coverage to come, including some great post-AXUG Summit articles.

Till next time!

"Visit the Dynamics AX Community Page today!"

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

A Focus on Performance with Robert Boone

On Monday I did a highlight of the upcoming Decisions Fall 2010 - AX Day, Virtual conference. You can find that post here.: Decisions 2010 - Next week!

As part of that highlight, I did a spotlight on sessions including one by one of my peers at Sunrise, Robert Boone.

In order to build into that upcoming session, Robert wanted to spend a little time on some performance tips around Dynamics AX, and with that below is his first guest article on this blog.:

-----


Dynamics AX Performance Quick Tips - Robert Boone

There is a good bit of information out on the blogsphere, MSDN, and other sources. A lot of this in contained within whitepapers, about Dynamics AX performance. For me however, when I am dealing with Performance and Dynamics AX, the following is my top three list of resources and tools for resolving performance issues.:

1. Pre-Implementation Planning Stage - Database Hardware

Dynamics AX is a great fit for a wide variety of businesses. For many, the hardware requirements may be very relatively minimal. But for larger implementations, those with many users or very intensive processing requirements, do not skimp on database hardware. Larger implementations especially need to plan for enough dedicated disks to follow the recommendations of the Performance Team listed at the bottom of the following checklist. Checklist link.

Final note on this, it is almost always much less expensive to get adequate hardware than to expend effort later on extensive optimization.

2.) During Implementation - Test - Use SQL Server DMVs

Make sure you've got representative data well before go-live and test. This is key to what DMV's can tell to a build up to production go live, and post go live performance expectations.

Know what's happening in the database. Know what your longest running queries are. Do they have reasonable query plans? Any missing indexes? What kind of wait states do you have? These are relatively easy things to see with SQL DMVs.

This is also a great top 10 list for Dynamics AX/SQL performance: SQLCat.com Top Ten Tips.

3.) To Quickly Identify Issues, use the Dynamics AX Trace Parser!

First a big thanks to the Performance Team for releasing this! Trace Parser is invaluable if you need to look at some Dynamics AX process to find out exactly where it is spending time. All of the above database tools, skills, and processes should be in place. With the combination of those and Trace Parser you should be able to identify and resolve any issues that do come up.

On Trace Parser, a couple of tips: Use this on a system that's not busy. Ideally, when tracing you'll be the only one in that instance. This is probably obvious to anyone who has used Dynamics AX tracing, but trace for them minimum amount of time necessary to capture the problem as trace files can quickly become huge.

Finally, I will be leading a session, as Brandon as highlighted on Batch Framework in Dynamics AX 2009, for the Decisions Fall Virtual Conference next Monday. What's important to understand, is that if you hit processes that nothing else seems to work, maybe it's time to look at worker processes or multi-threading a process. This is best suited for the session I will be leading, on the Batch Framework in AX 2009! (Link to session information)

-----

I would like to thank Robert for taking the time to do this guest post, and look forward to many more as we approach AX 6, and beyond!

Make sure to check out Roberts session, and get deep insight into what all the Batch Framework can help you do within Dynamics AX 2009!

Till Next time!

"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 - X++ Thread Development

One of the possible technical tools that exists when developing solutions in Dynamics AX, and inside X++ is Threads. This is actually not a new thing, thread based development has been around for a while.

The whole idea of creating a seperate thread to perform a process is an accepted technique to achieve multi processes at a given time, managed from the given, seperate application thread that the user application is taking up.

With, however Dynamics AX, sometimes it's not that clear of where and why you would use Thread to achieve a given process, or development goal.

I used and use threads a lot in C#, and the Thread development that exists in C# is for robust, and can become complex. And lets face it, Thread development and management of spawned threads from a application can become complex.

So with X++, there does exists the ability to use Threads for development needs. The following is a simple example of a Thread, a Method that the Thread is executed upon, and how to execute the simple thread.





With this we see a simple threaded development approach that can be used in X++. To break this down a little bit, you have a job, class, method etc. that is performing some work. In that is wants to spawn a thread.

The method that will be spawned as a new Thread, must accept a Thread as it's only parameter, and best practice on this says it should be a server static method that is the executed thread method.

You notice that there are input parameters and output parameters. These are containers, and therefore can store a number of different in and out parameters, to and from the threaded method. This is very useful as the thread could be developed in a way that would return some output.

Now this is great and all, but when would this be used?

Since Dynamics AX is an ERP product, that means a lot of process and needed development flows have to take place in a linear approach. However there are some things that do not have to be. Or some process that can be developed, so that threads can be used to improve the performance of a long running process for example.

So, with thinking about Threads, you have to think about divison of a given process into several Threads. Thats the nature of thread development.

This means, that if a given data process is taking hours and hours, can it be devided into smaller chunks of data? If so, but you need the whole thing to process, then a Threaded development approach can be done, where the given data process can be selected, and by the division, say a date or a group, can be used to spawn a thread to import data, doe that given divison unit.

This means, that if you have, say, 100,000 records to import. And that process is taking 3 hours. But you realize, you can break these 100,000 records into 10,000 records a peice, by a Item group.

Then you can take and have a method that loops through the grouped, by Item group, selection, and spawn a thread per 10,000 or by Item Group, and therefore speed up the process x10 fold. This means that the process that was taking 3 hours, can be done in an estimated 18 mins.

This directly ties to what DAXGuy, posted about here.: DAXGuy: SSIS for Bulk insert into Dynamics AX 2009 and this represents another way of inserting a large amount of data. What this approach, using threads, offers is it's X++ based, and therefore X++ busienss logic that might live on a given table in AX can be accessed and used. Note: Thread imports, most likely will not be as fast as a bulk insert process. But when a bulk insert is not possible, think about threaded development.

Now some notes about threaded development now. One, make sure everything that a thread accesses and executes is server tier code, or server context code. This makes sense, the thread executes on the Server tier, and in doing so, can't access client side code. If you try to do so, the thread will error out.

Also, if a thread is actually executed at the Client level, even though it's server side code being executed, for every thread spwaned a worker type user license will be used, and seen in the online users form.

Also, finally, I would like to point out that Microsoft itself uses threads in the MRP process, and other processes like ReqTrans, to try and speed up the processes in these areas. Thread development should be a serious consideration, but there are some limits.

For examples I tried to do a multi-tree thread approach. Meaning I tried to spawn sets of threads in threads. This is not possible. For whatever reason, the first thread in the threaded, thread approach will execute, after that the other spwaned threads are stopped.



If the multi-tree thread approach would work, then you could spawn mutli level threads, really attacking long running processes, by breaking it up into smaller, and smaller chunks. However, as my test have shown, this is not possible.

I hope this helps shed some light on threads in Dynamics AX 2009, and that it helps getting you thinking about how threads can be used in x++ to help with your coding needs.

That's all for now but check back soon!




"Visit the Dynamics AX Community Page today!"

SSRS Reports URL - Performance issue on 64 bit with SQL Server 2005

Something I recently came across on an Dynamics AX 2009 instance, was a SSRS reports URL, performance issue.

This is also referred to as the reports manager URL, it's the http://server/reports URL.

What I saw was that the reports URL would be working fine, and then after some time of being idle, the worker process that runs that URL in IIS 7.0 would of course get destroyed to free that RAM up.

On start back up, of that worker process, the reports URL would enver return. Closer details show, this is a thread error taking place.

After much search, and working with the Microsoft SSRS Performance Development group, a solution was found. We had to modify the machine.config, found in the .Net 2.0 Framework, to set the min. thread workers.

After seeing this work, turns out this is now a know bug by the Microsoft SSRS development group, with performance. This has only been seen on SQL Server 2005 64-bit Edt. running on Windows Server 2003 or 2008 64 bit edt. with *greater* than 4 processors. (4 processors or lower this does not show up.)

Turns out the code, for the SSRS service handles this fine, the processor affinity masking. However the code for the reports URL, does not.

So if you run into this, there is a fix, and Microsoft should be coming up with a fix for this in SQL Server 2005 SP4 and I am sure a hot fix before then.

This has not been seen in SQL Server 2008 code base.

Well check back later, more great post to come!




"Visit the Dynamics AX Community Page today!"

Getting the most out of Dynamics AX - Performance II

Last time we talked about performnace I left off going into how performance tuning is a Dynamic string of on-going events.

Getting the most out of Dynamics AX - Performance I

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

So we left off in the details of taking a look at X++ / Xpp queries. Making sure these custom implementations of data access code are not making use of nested While Select looping statments, and have covering indexes.

I also mentioned other things, but I wanted to focus this talk now on indexes. I would say, that near 80% of all major performance tuning and gains comes from adding missing indexes. Let me bold that: 80%.

The issue, even for out-of-the box tables not all required or needed indexes exist. Microsoft, nor any company, can forsee how all companies will make use of out-of-box tables.

Add custom tables, and you have the possibility for a lot of tables scans taking place, where index seeks are needed.

So how do you spot these missing indexes. A very handy SQL Server Dynamic Managed view exists. The following is a query that query this view.:


Print '*********************************************'
Print '* *'
Print '* *'
Print '* MISSING INDEXES *'
Print '* *'
Print '* *'
Print '*********************************************'



select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc


What I find myself doing is adding to the where clause and statement like '%[Insert DAX Database here]%' which helps focus me into the given Dynamics AX database at hand.

Having this information, mixed with information feed from the Profiler, and word of mouth from the users on processes that are slow can really help identify missing indexes for a database.

Once you have some target missing indexes, you need to take these with a grain of salt. Analyze them, look at them and see if they make sense. Test them out. Then Do Not create them via SQL Server. These need to be created within Dynamics AX 2009 itself. Once they are, re-run the missing indexes query above, and your index will no longer be in the list.

A word of advice here, this shows the missing indexes, number of compiles, etc. from the last time the SQL Server Service was shut down. So if your SQL Server was recently shut down, this information will not be to valid, until a full day of processing has ran.

On top of the Missing indexes DMV, there are plenty more area's to look at for SQL Server based performance.

What you want to keep in mind is that the point of clustered and non-clustered, covering indexes, exist to make the result set and look up time by the SQL Server engine reduced.

If you have a select statement, with a where clause and no index exists that has those where clause columns and table scan will be ran. That is most costly. However if you have a clustered index (physical ordering of the table and page files) and then covering indexes, the engine can perform an index seek. That is the desired approach, to be able to look at a query plan for a compiled, executed query and see index seeks across the board.

Alright, in the next part of this series we will continue with getting the most out of your Dynamics AX 2009 instance. Next time we focus on the AOS!

Check back soon, as more great post are to follow!




"Visit the Dynamics AX Community Page today!"

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

Back again!

Well that was a nice long weekend. A good break, to take and get refocused. Sometimes we need breaks, in order to deliver our best work. So I feel refreshed this morning, ready to get back to things.

I have several things to focus on. For this blog, I will continue down the BI road for Dynamics AX, again going into more pratical uses of SSRS and OLAP with today's technology that you find in Dynamics AX 2009.

I will have post coming out about Role Centers as well, and also I will be starting a series on Performance Tunning your instance of Dynamics AX 2009. Creating a plan, and doing this to get the most out of your instance.

So there are plenty of great post coming. I also have plans for this summer to have more Microsoft interviews, including some with the product team about AX 6.0.

So check back soon and often!




"Visit the Dynamics AX Community Page today!"

SQL Sever 2008 - Performance with Dynamics AX 2009 - Resource Governor

Alright, so lets get back to the SQL Server 2008 and how it's new features can be used with Dynamics AX 2009 to help with the performance of an instance.

Now with most any instance, the key to performance is in understanding query execution, query plans, and being able to tune these with indexes, and such. This of course is after you have done your benchmark and made sure you have enough RAM, CPU and HD Spindels running for you and correctly running.

These still apply to a DAX 2009 / SQL Server 2008 instance. However as I mentioned before, there are a good bit of new features in SQL Server 2008 to really help an instance out.

On this post, let's look at the: Resource Governor

So lets think of a make believe instance of DAX 2009. Just deployed, with EDI at this company, MRP, and nightly integrations flowing out of DAX 2009. Sounds typical these days for a lot of instances.

Ok, so you have all of this running, you do the normal SQL performance tunning and you notice that the nighlty integrations that flow out of DAX are causing some delays to the MRP process and also the same thing happens all the time with the EDI transactions and jobs that are being ran.

So we have run away query's going on here, and what can we do about it?

Well in SQL Server 2008, the resource governor can be used. This is the first version of SQL Server that you can now differentiate between workloads! What a concept huh?

So now you can take the resource govenor, create resource pools and workload groups, even policies that make use of classifier functions, to limit how much certian, specific incoming spids and their workloads can have access to the SQL Server's resources.

So you can make your MRP and normal DAX SQL traffic have higher priority to SQL Server resources, while placing the integrations and EDI into a resource workload group and pool that could limit the max amount of resources those could ever have. This way you can manage these, and stop these run away queries from ever hurting your normal day to day or high priority Dynamics AX processes.

Of course you should do this anyway, and then get with the EDI vendor and the development of the integrations so they can improve their processes to work better with the limited resources. ;-)

So the resource governor is something totally new to SQL Server 2008, and is a new tool that we can use to help manage and improve our Dynamics AX 2009 instances with.

Check back soon for more!




"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 & SQL Server 2008

In my last post I talked about, how we are just now starting to scratch the Dynamics AX 2009 surface. And with the soon to be release SQL Server 2008 Database Product, I thought it was a perfect time to start talking about the two together.

I did some digging around, and came across some nice published information about Dynamics AX and SQL Server 2008. First off we have this recently published SQL Server 2008 Benchmark document:

Microsoft SQL Server 2008 - Benchmark Report

When looking at the above benchmark, do a search on the page for 'Dynamics AX'. Doing so will bring you to the following quoted statement:

"Record scale improvement of 70% in throughput, scalability and response time
Benchmark tests demonstrate record scale showing up to 70 percent improvement in throughput scalability and response time; maximizing performance while minimizing database growth using SQL Server 2008 database compression."

And Dynamics CRM benchmark information was just right below that:

"Record scale at 24,000 concurrent users with sub-second response rate
Benchmark tests demonstrate that record scale at 24,000 concurrent users with sub-second response rate was achieved with Microsoft Dynamics CRM 4.0, SQL Server 2008, and Windows Server 2008 for enterprise-level workload. Learn more"

Now, lets take a look at the offical Press Release for Microsoft Dynamics AX 2009: (Microsoft Dynamics AX 2009)

"- Enhanced security, reliability and scalability though integration with Microsoft SQL Server 2008. For example, Microsoft Dynamics AX 2009, through Microsoft SQL Server 2008, can compress the database size by 40 percent to 60 percent, which can lead to lower hardware and data maintenance costs."

So there is for sure a benchmarked reason why clients would want to upgrade to Dynamics AX 2009 & SQL Server 2008:

- Huge increase in throughput, scalability and response time.
- Ability to use compression, out of the box, to minimize hardware cost

And this seems to be the case for Dynamics AX 2009 in general. The overall architecture has improved greatly (see my article on MSDynamicsWorld.com: Scaling of the AOS), and now we can scale up and out, and take advantage of newer technology offerings. This also means that a client would gain a lot of performance benefit for switching from SQL Server 2005 to SQL Server 2008.

Check back soon!




"Visit the Dynamics AX Community Page today!"

MSDW: What Will Be the Most Important Performance Enhancement in Dynamics AX 2009? Try Scaling of the AOS

Recently I created a new article for MSDynamicsWorld.com, and it is now posted and the top article right now. Check out the article here: Direct Link to: 'What Will Be the Most Important Performance Enhancement in Dynamics AX 2009? Try Scaling of the AOS'

This is part of my column on MSDynamicsWorld.com. You can view my entire column, by going here: My Column: 'AX Solution Outlook'

This article is the first in a series I hope to continue on the new performance improvements that await us in Dynamics AX 2009.

Check back soon!

Dynamics Ax Performance team Blog

Well March has been a great month for Dynamics AX news. All about convergence, and the DAX 2009 coming out. With all this, some deeper questions exist from different levels about performance tools, and testing. SQL Server is the main place in which to have tuned for performance, and then the AOS, and so forth. With that, a new blog was started this month, by the 'Dynamics AX Performance Team'.

The blog link is here:
Dynamics AX Performance Team Blog

I will also add this to the links on the left, as I hope to see some great things from this blog, as we have some good things from the X++ team blog as well. (X++ Team Blog)

Make sure to check this blog out from time to time and add it to your reading list for Dynamics AX sites!

Check back soon!

Dynamics AX - how scalable?

One of the first things that clients alike ask about is Dynamics AX scalability. Well I wanted to talk a little about this in my post here.

If we look at the latest scalability posted test, which you can find here PDF Link, you will see that the scalability test ran by Microsoft where all the way up to 3,000 concurrent users. This is way more than most any client will need at a given time, and so that number along speaks volumes.

Here is some information from that PDF:
"Performance Improvements and Scalability:

Based on rigorous scalability benchmark testing at the 1,000 concurrent user level, Microsoft Dynamics AX performed extremely well on both responsiveness and throughput while testing tasks in a full order-to-cash cycle, including the creation of purchase orders and entering sales orders. In addition, using the industry’s commonly used benchmarking standards, Microsoft Dynamics AX completed simple scenario orders for 3,000 concurrent users, far surpassing the needs of the majority of upper mid-market and enterprise customers."

So I would recommend to those that want to know more of the details behind this report, you can again find the PDF here: PDF Link

I want to conclude on this note. This was done in 2006, nearly over 2 years ago! Dynamics AX 2009 is coming out this Summer, which is going to be even more improved for performance and scalability. So is Dynamics AX scalable, you better believe it, and the math is there to show it!

check back soon!

Dynamics AX project success - Performance Tuning the system

Now that I talked about some key project management level area's of success I wanted to jump to something that I don't think a lot Dynamics AX projects think about, until it's a problem. That is Performance Tuning!

What this means, for me, is starting at the SQL Server level and working your way out. Use the tools at hand for analysis of SQL Query plans, adjust clustered indexes that may not be correct for even out of the box tables, and make sure for your custom add-on's correct index creation exist for reducing table scans, and making more use of Index seeks.

So performance tuning is a big part, and should be a part of the project plan. If your a customer and do not see this, demand it happen. It will be an added action item for a technical level person, but it's Well worth the time and money it takes to do this for a customer. The reason why, in doing so, the tuning might really speed up a lot of processes. If that is the case, well just think about it and add up the time saved to do a task, several thousand times over, and the equates to performance gains for every other thing done, because SQL Server can complete job X faster, jobs Y, Z will be faster as well!

So please as part of projects, to make them a success, have performance tuning as part of it. This does not just apply to custom modules, code, and tables, this applies to all functional area's that wil be used for the customer.

Well check back soon as I continue to post!

Find a job at: www.DynamicsAXJobs.com