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