Статья Вадима Гончаренко, начавшаяся с описания процесса оптимизации одного запроса и закончившаяся размышлениями о вреде использования индексных хинтов.

Оптимизация «тяжелых» запросов или Использовать ли индексные хинты?

Axapta MorphXplorer Наверное, каждый, кто регулярно пользуется MorphXplorer-ом, сталкивался с одной его очень неприятной особенностью: открытие контекстного меню на любом объекте происходит с большой задержкой и является достаточно тяжелой операцией. Посмотрим, с чем это связано и можно ли с этим что-то сделать.

Для начала неплохо было бы определить то самое «узкое место», с которым связано большое время выполнения операции. Есть масса способов сделать это, попробуем на этот раз сделать все «по-взрослому», например, с использованием утилиты Performance monitor, входящей в состав операционных систем Windows 2000, Windows XP и.т.д.

Первым делом включаем два напрашивающихся счетчика:

Они должны дать нам понять, с чем связана проблема – с неэффективным кодом в приложении (тогда должна вырасти нагрузка на процессор, создаваемая процессом ax 32. exe ) или неэффективным выполнением запросов (должно расти нагрузка от процесса sqlservr . exe ). Итак, счетчики настроены, приложение запущено, вызываем всплывающее меню и смотрим показатели perfmon:

Performance monitor

Стало ли хоть немного понятнее? Ясно одно – upgrade процессора нам в этом случае не поможет, так как его загрузка далека от предельной. Расширим круг поиска – добавим счетчик

и снова откроем контекстное меню

Performance monitor

Ситуация немного проясняется: некоторый всплеск загрузки процессора все-таки наблюдается, но дело явно не в нем. Чтобы убедиться в этом, достаточно посмотреть на то, что происходит со счетчиком Physical disk : Disk transfers / sec , виден явный продолжительный пик нагрузки. Это может быть связано либо с кодом, интенсивно читающим AOD , либо с плохим планом исполнения запроса. Проверим сначала второй вариант, потому что:

Итак, для того, чтобы анализировать план исполнения «плохого» запроса, нужен сам запрос. Есть несколько способов его отыскать:

Мы воспользуемся первым способом. Для этого откроем форму sysSetupForm (Сервис \ Параметры) и на закладке SQL включим пункты «Мониторинг запросов SQL», «Таблица (база данных» в группе полей «Длинные запросы». За красивым словом «апертура» стоит минимальное время выполнения запроса, при превышении которого он будет запротоколирован. Установим его значение не слишком малым, чтобы не загромождать журнал трассировки множеством записей, например 1000 миллисекунд, или 1 секунда. И повторим наши манипуляции в MorphXplorer.

Обратимся к журналу трассировки (Администрирование \ Запросы \ База данных \ Журнал трассировки операторов SQL). Нас интересуют последняя запись в нем

Журнал трассировки операторов SQL в Axapta

По кнопке «Редактирование кода» смотрим, откуда выполнялся этот запрос

Код запроса можно открыть из журнала трассировки

Первое, что бросается в глаза – использование сразу трех INDEX HINT. Довольно самонадеянно со стороны разработчиков навязывать в коде использование тех индексов, которые они считают «правильными». Особенно в случае, когда

И это при том, что в усовершенствование оптимизатора запросов СУБД вкладываются огромные усилия высококвалифицированных программистов и математиков, а использование неоптимального плана исполнения чревато серьезным снижением производительности.

Посмотрим план выполнения запроса

План выполнения запроса

Мне в нем не нравится сканирование таблицы xRefNames – в ней сейчас порядка 300 тысяч записей и занимает она более 50 мегабайт. И это сканирование навязано хинтом в строке

join xRefNames index hint RecId в запросе 

Может быть, станет лучше, если мы избавимся от этого хинта? Проверим наше предположение: отредактируем текст запроса прямо в этой же форме в поле «Оператор SQL», удалив вхождения INDEX(%IndexName%) и воспользуемся кнопкой «Рассчитать новый план»

План отптимизированного запроса

Хороший план исполнения даже выглядеть должен красиво. В запросе без «выкручивания рук» оптимизатору мы избавились от сканирования таблицы. Проверим, как это сказывается на времени выполнения. Закомментируем использованные в коде хинты.

Модифицированный код

Чтобы никто не мог обвинить нас в том, что сейчас мы используем уже закэшированные первым запросом данные, сбросим кэш SQL Server командой

DBCC DROPCLEANBUFFERS

Возвращаемся в MorphXplorer и снова откроем контекстное меню. В моем эксперименте этот запрос выполнился менее одной секунды (вместо 34 секунд с использованием хинтов) и уже не попал в журнал трассировки.

 

Какой из всего этого можно сделать вывод?

В сервере БД заложен мощный механизм оптимизации запросов, он располагает самой свежей информацией о структуре и распределении данных, и было бы неправильно не использовать этот механизм. Разумеется, не стоит бросаться искать и удалять индексные хинты по всему приложению. Но вариант отказа от них может быть полезен при оптимизации самых «тяжелых» запросов. И, разумеется, не стоит злоупотреблять их использованием в собственных разработках.

 

Вадим Гончаренко, vgoncharenko@rabota-na-rezultat.ru