8.4.8. Оптимизация запросов

Статистика — это служебная информация о распределении данных в столбцах таблицы.

Для баз данных 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. При установке этого параметра:

   1. Отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (то есть строки "N row(s) affected" на вкладке Messages при выполнении запроса в Management Studio).

   2. Отключается передача специального серверного сообщения DONE_IN_PROC, которое по умолчанию возвращается для каждого этапа хранимой процедуры.

   3. Повышается производительность выполнения хранимых процедур и обычных запросов (до 10%).

3. Просмотреть план выполнения запроса, используя вкладку Execution Plan. Для этого:

   1. чтобы получить информацию об ожидаемом плане выполнения запроса - в меню Query выбрать команду Display Estimated Execution Plan.

   2. Чтобы просмотреть реальный план выполнения запроса – перед выполнением запроса установить в меню Query флажок Include Actual Execution Plan.

Рис. 8.14. План выполнения запроса в SQL Server Management Studio

4. В окне Management Studio выполнить команду SET STATISTICS IO ON для вывода дополнительной информации. Наиболее информативным является параметр Logical Reads, отображающий количество логических чтений при выполнении запросов (то есть количество чтений и из кэша, и с диска).

5. Попытаться изменить план выполнения запроса, используя хинты (подсказки) оптимизатора, и узнать суммарное количество логических чтений для каждого плана. Чаще всего используются следующие хинты:

   1. NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK — хинты для управления блокировками.

   2. FAST количество_строк — будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное вами количество строк (первых с начала набора записей);

   3. FORCE ORDER — объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;

   4. MAXDOP (от Maximum Degree of Parallelism — максимальная степень распараллеливания запроса) — при помощи этого хинта можно указать максимальное количество процессоров, которые можно будет использовать для выполнения этого запроса.

   5. OPTIMIZE FOR — возможность указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, значения фильтра для WHERE);

   6. 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 не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными).