Dynamics AX: SQL Tuning: Table & Index Scans

[Blog bot]

Источник: http://dynamics-ax.blogspot.com/2007...dex-scans.html
==============
To continue my post about SQL Server performance tuning for Dynamics AX, lets move into things we can look for when taking an in depth look at SQL Query Execution plans. Before I go to further this data can be captured and looked at best via the SQL Server Profiler. With this you should take and capture data for your Dynamics AX Database. With that said, lets talk about Table & Index Scans.

A table scan is something that happens when a query is called upon a table to return a result set, and that given table does Not have a Clustered Index. To give a little information about Clustered Indexes, they are the physical ordering of a given table and should be created as the most used keys for where clauses and / or join statements. So in seeing a table scan your query is going to pull back a rather large result set, and then Filter it from the where clause [if anything supplied]. This also means that given record and esclated table locks can occur. To avoid this, one needs to make sure that the given tables in Dynamics AX have a good clustered index supplied. [Some Core do Not come out of the box with Clustered Indexes, be aware.]

Next is an Index Scan. This takes places, generally, on the clustered index when a seek could not be performed. This means that either a given Covering non-clustered index for the SQL Statement does not exists and / or the given statement did not use the Clustered key in a specific filtering way to select the data. To help in this problem one would need to create what is called a covering index that covers the where clause key colummns, and possibly even some / or all of the Select fields in the Select statement. Again the issue here is a lot of data for the inital result set, with filtering later applied.

In both these cases much SQL cost of: Disk I/O, CPU, and RAM are used up and therefore translate into possible contention, resource issues, and locking & blocking issues. What you should keep in mind is that you should develop / code your X++ code with starting out getting the Smallest result set possible. This means using indexes, or creating those that are needed. Make sure that correct joins are used, instead of un-needed nested while loops. In doing so, the gain for each tuned area will translate into an exponentially better performing SQL Server instance for Dynamics AX. Another key not to make here is that a tuned query for one client by no means can be said it is tuned for all clients. You must tune Each Dynamics AX instance as you install, develop, go live, and post go live.

Well check back soon as I continue down this path for SQL Server performance tuning with Dynamics AX in mind!

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...dex-scans.html