Статья Вадима Гончаренко, начавшаяся с описания процесса оптимизации одного запроса и закончившаяся размышлениями о вреде использования индексных хинтов.
Оптимизация «тяжелых» запросов или Использовать ли индексные хинты?
Наверное, каждый, кто регулярно пользуется MorphXplorer-ом, сталкивался с одной его очень неприятной особенностью: открытие контекстного меню на любом объекте происходит с большой задержкой и является достаточно тяжелой операцией. Посмотрим, с чем это связано и можно ли с этим что-то сделать.
Для начала неплохо было бы определить то самое «узкое место», с которым связано большое время выполнения операции. Есть масса способов сделать это, попробуем на этот раз сделать все «по-взрослому», например, с использованием утилиты Performance monitor, входящей в состав операционных систем Windows 2000, Windows XP и.т.д.
Первым делом включаем два напрашивающихся счетчика:
- Process: %Processor time для процесса ax32.exe
- Process: %Processor time для процесса sqlservr.exe
Они должны дать нам понять, с чем связана проблема – с неэффективным кодом в приложении (тогда должна вырасти нагрузка на процессор, создаваемая процессом ax 32. exe ) или неэффективным выполнением запросов (должно расти нагрузка от процесса sqlservr . exe ). Итак, счетчики настроены, приложение запущено, вызываем всплывающее меню и смотрим показатели perfmon:
Стало ли хоть немного понятнее? Ясно одно – upgrade процессора нам в этом случае не поможет, так как его загрузка далека от предельной. Расширим круг поиска – добавим счетчик
- Physical disk: Disk Transfers/sec
и снова откроем контекстное меню
Ситуация немного проясняется: некоторый всплеск загрузки процессора все-таки наблюдается, но дело явно не в нем. Чтобы убедиться в этом, достаточно посмотреть на то, что происходит со счетчиком Physical disk : Disk transfers / sec , виден явный продолжительный пик нагрузки. Это может быть связано либо с кодом, интенсивно читающим AOD , либо с плохим планом исполнения запроса. Проверим сначала второй вариант, потому что:
- первый вариант требует просмотра большого количества кода
- нам потребуется профайлер кода, а это тема для большой самостоятельной статьи
- второй вариант потребует от нас сделать всего одну простую дополнительную настройку
- результат на момент написания статьи мне уже известен :)
Итак, для того, чтобы анализировать план исполнения «плохого» запроса, нужен сам запрос. Есть несколько способов его отыскать:
- включить мониторинг продолжительных запросов в Аксапте
- воспользоваться утилитой Profiler от MSSQL
Мы воспользуемся первым способом. Для этого откроем форму sysSetupForm (Сервис \ Параметры) и на закладке SQL включим пункты «Мониторинг запросов SQL», «Таблица (база данных» в группе полей «Длинные запросы». За красивым словом «апертура» стоит минимальное время выполнения запроса, при превышении которого он будет запротоколирован. Установим его значение не слишком малым, чтобы не загромождать журнал трассировки множеством записей, например 1000 миллисекунд, или 1 секунда. И повторим наши манипуляции в MorphXplorer.
Обратимся к журналу трассировки (Администрирование \ Запросы \ База данных \ Журнал трассировки операторов SQL). Нас интересуют последняя запись в нем
По кнопке «Редактирование кода» смотрим, откуда выполнялся этот запрос
Первое, что бросается в глаза – использование сразу трех INDEX HINT. Довольно самонадеянно со стороны разработчиков навязывать в коде использование тех индексов, которые они считают «правильными». Особенно в случае, когда
- приложение должно работать у множества заказчиков
- приложение будет работать на РАЗНЫХ ВЕРСИЯХ РАЗНЫХ СЕРВЕРОВ БД ( MSSQL и Oracle )
- распределение данных у каждого заказчика будет РАЗНЫМ
- запросы будут выполняться с разными аргументами
- структура БД рано или поздно изменится
И это при том, что в усовершенствование оптимизатора запросов СУБД вкладываются огромные усилия высококвалифицированных программистов и математиков, а использование неоптимального плана исполнения чревато серьезным снижением производительности.
Посмотрим план выполнения запроса
Мне в нем не нравится сканирование таблицы xRefNames – в ней сейчас порядка 300 тысяч записей и занимает она более 50 мегабайт. И это сканирование навязано хинтом в строке
join xRefNames index hint RecId в запросе
Может быть, станет лучше, если мы избавимся от этого хинта? Проверим наше предположение: отредактируем текст запроса прямо в этой же форме в поле «Оператор SQL», удалив вхождения INDEX(%IndexName%) и воспользуемся кнопкой «Рассчитать новый план»
Хороший план исполнения даже выглядеть должен красиво. В запросе без «выкручивания рук» оптимизатору мы избавились от сканирования таблицы. Проверим, как это сказывается на времени выполнения. Закомментируем использованные в коде хинты.
Чтобы никто не мог обвинить нас в том, что сейчас мы используем уже закэшированные первым запросом данные, сбросим кэш SQL Server командой
DBCC DROPCLEANBUFFERS
Возвращаемся в MorphXplorer и снова откроем контекстное меню. В моем эксперименте этот запрос выполнился менее одной секунды (вместо 34 секунд с использованием хинтов) и уже не попал в журнал трассировки.
Какой из всего этого можно сделать вывод?
В сервере БД заложен мощный механизм оптимизации запросов, он располагает самой свежей информацией о структуре и распределении данных, и было бы неправильно не использовать этот механизм. Разумеется, не стоит бросаться искать и удалять индексные хинты по всему приложению. Но вариант отказа от них может быть полезен при оптимизации самых «тяжелых» запросов. И, разумеется, не стоит злоупотреблять их использованием в собственных разработках.
Вадим Гончаренко, vgoncharenko@rabota-na-rezultat.ru
