Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 20.07.2007, 11:50   #1  
Blog bot is offline
Blog bot
23,901 / 804 (74) +++++++
Регистрация: 28.10.2006
Dynamics AX: SQL Tuning: Table & Index Scans
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:, also post a job for only $99.00!

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
Dynamics AX Sustained Engineering: SQL Server 2005 sp3 & SQL Server 2008 with Dynamics AX Blog bot DAX Blogs 0 12.02.2009 06:08
axStart: Microsoft Dynamics AX 2009 Hot Topics Web Seminar Series Blog bot DAX Blogs 0 06.08.2008 12:05
Dynamics AX: Dynamics AX 2009 & SQL Server 2008 Blog bot DAX Blogs 0 10.06.2008 21:08
Dynamics AX: SQL Performance and Dynamics AX Tuning Blog bot DAX Blogs 0 19.07.2007 23:12
Dynamics AX: SQL Server, Heart of Dynamics AX Blog bot DAX Blogs 0 13.07.2007 18:00
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 02:31.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.