logo search
SQL2008_Administration

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

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

Для баз данных SQL Server 2008 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS.

Для создания и обновления статистики вручную используются команды CREATE STATISTICS и UPDATE STATISTICS.

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

  1. Найти запросы, которые подлежат оптимизации:

  1. Установить для соединения параметр NOCOUNT - SET NOCOUNT ON. При установке этого параметра:

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

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

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

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

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

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

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

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

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

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

На что следует обратить внимание при анализе запросов

Если вы анализируете запросы, которое выполняет к базе данных какое-либо приложение, есть смысл в первую очередь обратить внимание на следующие моменты: