Оптимизация запросов
Статистика — это служебная информация о распределении данных в столбцах таблицы.
Для баз данных SQL Server 2008 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS.
Для создания и обновления статистики вручную используются команды CREATE STATISTICS и UPDATE STATISTICS.
Оптимизация запросов:
1. Найти запросы, которые подлежат оптимизации:
· Воспользоваться SQL Profiler, установив фильтр на время выполнения запроса (Duration). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5 секунд.
· Использовать информацию о запросах, предоставляемую средствами Index Tuning Wizard / Database Tuning Advisor.
2. Установить для соединения параметр NOCOUNT - SET NOCOUNT ON. При установке этого параметра:
a. Отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (то есть строки "N row(s) affected" на вкладке Messages при выполнении запроса в Management Studio).
b. Отключается передача специального серверного сообщения DONE_IN_PROC, которое по умолчанию возвращается для каждого этапа хранимой процедуры.
c. Повышается производительность выполнения хранимых процедур и обычных запросов (до 10%).
3. Просмотреть план выполнения запроса, используя вкладку Execution Plan. Для этого:
a. чтобы получить информацию об ожидаемом плане выполнения запроса - в меню Query выбрать команду Display Estimated Execution Plan.
b. Чтобы просмотреть реальный план выполнения запроса – перед выполнением запроса установить в меню Query флажок Include Actual Execution Plan.

Рис. 8.14. План выполнения запроса в SQL Server Management Studio
4. В окне Management Studio выполнить команду SET STATISTICS IO ON для вывода дополнительной информации. Наиболее информативным является параметр Logical Reads, отображающий количество логических чтений при выполнении запросов (то есть количество чтений и из кэша, и с диска).
5. Попытаться изменить план выполнения запроса, используя хинты (подсказки) оптимизатора, и узнать суммарное количество логических чтений для каждого плана. Чаще всего используются следующие хинты:
a. NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK — хинты для управления блокировками.
b. FAST количество_строк — будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное вами количество строк (первых с начала набора записей);
c. FORCE ORDER — объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;
d. MAXDOP (от Maximum Degree of Parallelism — максимальная степень распараллеливания запроса) — при помощи этого хинта можно указать максимальное количество процессоров, которые можно будет использовать для выполнения этого запроса.
e. OPTIMIZE FOR — возможность указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, значения фильтра для WHERE);
f. USE PLAN — при помощи этого хинта можно явно определить план выполнения запроса, передав этот план в виде строкового значения в формате XML. План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 8.14, и выбрав в контекстном меню команду Save Execution Plan As).
Гиды по планам выполнения
Очень часто бывает так, что код запроса нам не изменить: он жестко "прошит" в код откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2008 появилась новая хранимая процедура sp_create_plan_guide. Она позволяет создавать так называемые руководства по планам выполнения (plan guides), которые будут автоматически применяться к соответствующим запросам.
На что следует обратить внимание при анализе запросов
Если вы анализируете запросы, которое выполняет к базе данных какое-либо приложение, есть смысл в первую очередь обратить внимание на следующие моменты:
· насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы);
· используются ли в коде курсоры?;
· используются ли в коде временные таблицы или тип данных Table?;
· используются ли в коде команды на изменение структуры временных таблиц;
· если приложение передает на сервер команды EXECUTE, рекомендуется заменить их на вызов хранимой процедуры sp_executesql;
· повышения производительности можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов.
· постараться не смешивать в коде хранимой процедуры команды DML и DDL
· следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными).
|