Кэширование и повторное использование плана выполнения

Изменения: 17 июля 2006 г.

В SQL Server 2005 есть пул памяти, предназначенный для хранения планов выполнения и буферов данных. Процентное соотношение размера пула, выделенного для планов выполнения и буферов данных, динамически изменяется в зависимости от состояния системы. Часть пула памяти, используемого для хранения планов выполнения, называется кэшем процедур.

В SQL Server 2005 планы выполнения состоят из следующих основных компонентов.

• План запроса
  Тело плана выполнения является реентерабельной структурой данных только для чтения, которая предназначена для использования любым числом пользователей. Оно называется планом запроса. План запроса не содержит контекста пользователя. В памяти не может находиться более одной или двух копий плана запроса: одна — для всех последовательных выполнений, а другая — для всех параллельных выполнений. Одна параллельная копия обслуживает все параллельные выполнения независимо от степени параллелизма.
• Контекст выполнения
  Каждый пользователь, который в настоящий момент выполняет запрос, имеет структуру данных, которая содержит данные, относящиеся к данному выполнению, например значения параметров. Эта структура данных называется контекстом выполнения. Структуры данных контекста выполнения являются повторно используемыми. Если пользователь выполняет запрос и одна из структур не используется, она повторно инициализируется контекстом нового пользователя.

В SQL Server 2005 при выполнении инструкции SQL реляционный механизм сначала просматривает кэш процедур, проверяя, нет ли в нем плана выполнения для такой же инструкции. SQL Server 2005 повторно использует любой план выполнения, который найдет, снижая издержки на его повторную компиляцию. Если не найдено ни одного существующего плана, SQL Server 2005 формирует для этого запроса новый план.

SQL Server 2005 реализует эффективный алгоритм поиска существующих планов выполнения для любой инструкции SQL. В большинстве систем ресурсы, затрачиваемые на поиск готового плана, всегда меньше ресурсов, затрачиваемых на повторную компиляцию каждой инструкции SQL.

Алгоритмы поиска соответствия инструкции SQL существующему неиспользуемому плану выполнения в кэше требуют, чтобы все ссылки на объекты были полностью квалифицированы. Например, для первой из следующих инструкций SELECT соответствие существующему плану не будет найдено, а для второго — будет:

SELECT * FROM Contact

SELECT * FROM Person.Contact

Конкретный план выполнения будет повторно использован в экземпляре SQL Server 2000 и SQL Server 2005 с большей вероятностью, чем в SQL Server версии 6.5 и более ранней.

Устаревание планов выполнения

После формирования плана выполнения он хранится в кэше процедур. SQL Server 2005 при необходимости освободить память удаляет из кэша старые, неиспользуемые процедуры. Каждый план запроса и контекст выполнения имеет соответствующий коэффициент стоимости, который указывает, насколько затратной является компиляция данной структуры. В ней присутствует также и поле возраста. Каждый раз, когда соединение ссылается на объект, поле возраста увеличивается на коэффициент стоимости. Например, если план запроса имеет коэффициент стоимости 8 и на него ссылались дважды, его возраст становится равным 16. Процесс отложенной записи периодически просматривает список объектов, находящихся в кэше процедур. При каждом просмотре поле возраста уменьшается на единицу. В нашем примере возраст плана запроса в кэше будет уменьшен до нуля за 16 проходов, если ни один из пользователей им не воспользуется. Процесс отложенной записи освобождает объект в следующих условиях.

• Диспетчеру памяти требуется память, а вся доступная память уже занята.
• Поле возраста объекта равно 0.
• На объект в настоящий момент нет ссылок из соединений.

Поскольку поле возраста увеличивается каждый раз при ссылке на объект, для часто используемых объектов оно не успевает достигнуть значения 0, и поэтому они не удаляются из кэша. Редко используемые объекты вскоре будут доступны для освобождения, но фактически будут удалены только тогда, когда занимаемая ими память понадобится под другие объекты.

Перекомпиляция планов выполнения

Некоторые изменения в базе данных могут привести к тому, что план выполнения при изменении ее состояния станет неэффективным или неправильным. SQL Server обнаруживает изменения, которые могут сделать недействительным план выполнения, и помечает такой план как неправильный. При следующем выполнении данного запроса план должен быть перекомпилирован. План может стать недействительным при выполнении следующих действий.

• Изменены таблица или представления, на которые ссылается запрос (ALTER TABLE или ALTER VIEW).
• Изменены индексы, используемые планом выполнения.
• Обновлена статистика, используемая планом выполнения, сформированная либо явным образом по UPDATE STATISTICS, либо автоматически.
• Удалены индексы, используемые планом выполнения.
• Явно вызвана процедура sp_recompile.
• Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос).
• Для таблиц с триггерами, если значительно выросло число строк в таблицах inserted и deleted.
• Хранимая процедура была выполнена с параметром WITH RECOMPILE.

Большинство перекомпиляций необходимы либо для обеспечения правильности работы инструкции, либо для потенциального ускорения работы плана выполнения.

В SQL Server 2000, независимо от того, какая из инструкций пакета вызвала перекомпиляцию, перекомпилируется весь пакет, переданный через хранимую процедуру, триггер, специальный пакет или подготовленную инструкцию. В SQL Server 2005 перекомпилируется только та инструкция пакета, которая вызвала перекомпиляцию. Из-за этого отличия счетчики перекомпиляции в SQL Server 2000 и SQL Server 2005 несовместимы между собой. Кроме этого, в SQL Server 2005 больше типов перекомпиляции, что вызвано расширением набора возможностей.

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

В SQL Server 2005 событие трассировки SP:Recompile приложения SQL Server Profiler выводит сведения о рекомпиляциях на уровне инструкций. В SQL Server 2000 это событие трассировки выдает отчет только о перекомпиляции пакетов. В будущем в SQL Server 2005 столбец TextData данного события будет заполнен. Таким образом, практика отслеживания SP:StmtStarting или SP:StmtCompleted для получения текста Transact-SQL, который вызвал перекомпиляцию, существовавшая в SQL Server 2000, больше не актуальна.

В SQL Server 2005 также добавлено новое событие трассировки SQL:StmtRecompile, которое выдает отчет о перекомпиляциях на уровне инструкции. Оно может применяться для трассировки и отладки перекомпиляций. Событие SP:Recompile создается только для хранимых процедур и триггеров, а SQL:StmtRecompile — для хранимых процедур, триггеров, специальных пакетов, пакетов, которые выполняются через sp_executesql, подготовленных запросов и динамического SQL.

Столбец EventSubClass для событий SP:Recompile и SQL:StmtRecompile содержит код в виде целого числа, обозначающий причину перекомпиляции. В следующей таблице перечислено значение для каждого из этих кодов.

Значение EventSubClass	Описание
1	Изменение схемы.
2	Изменение статистики.
3	Отложенная компиляция.
4	Изменение параметра SET.
5	Изменение временной таблицы.
6	Изменение удаленного набора строк.
7	Изменение разрешения FOR BROWSE.
8	Изменение среды уведомлений о запросах.
9	Изменение секционированного представления.
10	Изменение параметров курсора.
11	Запрошено OPTION (RECOMPILE).

Примечание.

Если параметр базы данных AUTO_UPDATE_STATISTICS устанавливается в значение ON, запросы перекомпилируются в том случае, если они указывают на целевые таблицы или индексированные представления, для которых изменена статистика или мощность которых со времени последнего выполнения изменилась в значительной степени. Это относится к стандартным пользовательским, временным таблицам, а также таблицам inserted и deleted, созданным триггерами DML. На производительность запроса оказывают влияние и неумеренные перекомпиляции, вызванные установкой этого параметра в значение OFF. Если параметр базы данных AUTO_UPDATE_STATISTICS установлен в значение OFF, перекомпиляции по причине изменения статистики или мощности не выполняются за исключением таблиц inserted и deleted которые созданы триггерами DML INSTEAD OF. Так как данные таблицы создаются в базе данных tempdb, перекомпиляция запросов, которые обращаются к этим таблицам, зависит от значения параметра AUTO_UPDATE_STATISTICS в базе данных tempdb. Обратите внимание, что в SQL Server 2000 запросы при изменении мощности или статистики продолжают выполняться в таблицах триггеров DML inserted и deleted, даже если указанный параметр установлен в OFF. Дополнительные сведения об отключении параметра AUTO_UPDATE_STATISTICS см. в разделе Статистика индексов.

См. также

Справочник

SQL Server, объект SQL Statistics

Основные понятия

Управление буферами

Другие ресурсы

Компиляция пакета, повторная компиляция и проблемы кэширования плана в SQL Server 2005

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
17 июля 2006 г.	Новое содержимое Добавлено выполнение хранимой процедуры с параметром WITH RECOMPILE в список условий, делающих план недействительным.
5 декабря 2005 г.	Новое содержимое Разъяснено описание различного применения перекомпиляции запросов к таблицам inserted и deleted созданных триггерами DML INSTEAD OF, если параметр базы данных AUTO_UPDATE_STATISTICS установлен в значение OFF.