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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 27.04.2007, 17:10   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
dynamicsusers: NAV SQL Tuning - Covering Indexes
Источник: http://dynamicsuser.net/blogs/mark_b...g-indexes.aspx
==============

SQL and NAV is hot these days. More and more knowledge becomes available and more companies and freelancers start helping customers tuning their databases.
Most of the problems exist in "older" databases. Especialy early version 3 databases have difficulties performing.
Fortunalely version 5 of NAV is very much pre-tuned on SQL so you are not likely to run into big issues until the database reaches a big size or some customisations come into business.
This blog is not about what you can do with indexes, maintenance or code changes but specificaly about "covering indexes". Something not very heard of in the NAV world.
What is a Covering Index; A covering index means an index that includes all columns in a specific query. In NAV that would mean the requested fields in the Where Clause and the Order by clause, and in some cases the Select clause.
In NAV (and AX) almost al queries are transformed into a Select * from... This means that SQL has to return all values in the table instead of only the required columns, resulting in a bookmark lookup to the Clustered index for every query. Every query? No.
There are some exeptions to this.
1. Select TOP NULL From...
This query is the result of an ISEMPTY statement in C/AL. If you are only interested in if a record is there but not in the contents this is your friend. An example in standard NAV is Reservation Management where this is used. Unfortunately this has to be tuned as there is only one C/AL statement for both Trade Documents, Journals and Manufacturing.
Reports also tend to create TOP NULL Queries
2. Select COUNT (*) From
This statement is generated by the COUNT statement in C/AL. If there is a covering index for all of the fields in the filter you can speed up this statement.
3. & 4 Select SUM
This is generated from either FLOWFIELDS or CALCSUMS in NAV. When there is a SIFT table for the SUM it will generate a SUM on this table, otherwise it will generate a SUM on the actual table.
When we go to a customer, one of the steps in the tuning process it SIFT tuning. SIFT tuning is a real give-or-take process where you need to fine-balance for read and write performance. Sometimes you just cannot disable a SIFT level.
A solution can be a covering index on a SIFT Table.
Good luck on your Tuning Projects.
Mark Brummel
MVP - Microsoft Dynamics NAV | Certified SQL Perform Consultant
More information
http://www.sql-server-performance.co...ng_indexes.asp
http://www.sqlskills.com/blogs/kimberly/


Источник: http://dynamicsuser.net/blogs/mark_b...g-indexes.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
dynamicsusers: Making NAV use a certain index on SQL Server Blog bot DAX Blogs 0 21.08.2007 10:40
Dynamics AX: SQL Tuning: Table & Index Scans Blog bot DAX Blogs 0 20.07.2007 11:50
Dynamics AX: SQL Performance and Dynamics AX Tuning Blog bot DAX Blogs 0 19.07.2007 23:12
dynamicsusers: NAV SQL Indexes Blog bot DAX Blogs 0 23.05.2007 23:40
dynamicsusers: SQL and SharePoint we know, but what is PerformancePoint Blog bot DAX Blogs 0 18.03.2007 14:40
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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