Dynamics AX: SQL Performance and Dynamics AX Tuning

[Blog bot]

Источник: http://dynamics-ax.blogspot.com/2007...ax-tuning.html
==============
Now this is a topic that I could most likely spend a lot of time writing about, and talking through. One of the first things that should be known, is that for Each customer instance is different for performance.

This means that you could write so very awesome code that could be great for your new vertical add-on, but we all know that customers add and take away with mods. In doing this, changes your indexes and performance tuning set for the given code, and therefore should be looked at and Tuned for each new customer instance your working with.

Why is this the case? Why can't Cluster Indexes be delivered out of the box? Why must you even tune Microsoft core code? The answer: Dynamics AX is based on SQL Server Now for the more detail answer. Since Dynamics AX is based on SQL Server, then we adhere to the optimization done by the SQL Server optimizaer in it's choice of a path to return the desired result set. This is why in Dev your SQL query could be super fast, but in production it could be as slow as can be. Another thing to throw things into the mix is that your query could be correct for the instance. Could be that the issue is a core code problem, or some other custom code blocking your code within SQL Server, or causing a chain of issues.

What does this all mean? Developers and Architects should look at code, per customer, and the entire tuning of the given system from implemention and forward. We should code for performance [don't select all fields when updating a single field, and don't perform a massive while select loop in a big tran.]
Architects / Technical Leads should create baselines, espectially for SQL Server, and analyze the traffic of the box for production for issues. Now the next question is what should one look for?

- Table Scans
- Index Scans
- Parrelisim
- Heavy locking / blocking
- Busy Disk I/O

So what do you do if you see these things? Well I will continue on my set of post going into these, what they mean, why it's bad for Dynamics AX and SQL Server, and what to do in order to address these issues. The point of this post was to get you thinking about these area's from a Developer and Architect level. I would like to leave you with one quote from Rod Hansen, Business System Architect for Microsoft: "Think in the smallest result sets as possible from the start of the query executing to the end."

Check back as I continue my post on SQL Server optimization for Dynamics AX.


Find a job at: www.DynamicsAXJobs.comFind a job at: DynamicsAXJobs.com, also post a job for only $99.00!


==============
Источник: http://dynamics-ax.blogspot.com/2007...ax-tuning.html