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

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

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

  • План запроса

    Тело плана выполнения является реентерабельной структурой данных только для чтения, которая предназначена для использования любым числом пользователей. Оно называется планом запроса. План запроса не содержит контекста пользователя. В памяти содержится одна или две копии плана запроса (но не более): одна — для всех последовательных выполнений, а другая — для всех параллельных выполнений. Одна параллельная копия обслуживает все параллельные выполнения независимо от степени параллелизма.

  • Контекст выполнения

    Для каждого пользователя, который в настоящий момент выполняет запрос, имеется структура данных, которая содержит данные, относящиеся к данному выполнению, например значения параметров. Эта структура данных называется контекстом выполнения. Структуры данных контекста выполнения являются повторно используемыми. Если пользователь выполняет запрос и одна из структур не используется, она повторно инициализируется контекстом нового пользователя.

Контекст выполнения, тот же запрос, другие литералы

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

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

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

SELECT * FROM Contact

SELECT * FROM Person.Contact

Удаление планов выполнения из кэша процедур

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

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

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

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

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

Максимальный размер всех кэшей является функцией от размера буферного пула и не может превышать максимальный объем памяти сервера. Дополнительные сведения о настройке максимального объема памяти сервера см. в описании параметра max server memory в разделе sp_configure (Transact-SQL).

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

В следующих примерах показывается, какие планы выполнения удаляются из кэша процедур.

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

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

Чтобы вручную удалить отдельный план выполнения или все планы, используйте команду DBCC FREEPROCCACHE (Transact-SQL).

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

Некоторые изменения в базе данных могут привести к тому, что план выполнения при изменении ее состояния станет неэффективным или неправильным. 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 и более поздних версиях больше типов перекомпиляции, что вызвано расширением набора возможностей.

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

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

Событие трассировки 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 см. в разделе Использование статистики для повышения производительности запросов.

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

Обновления

Обновлен раздел об удалении планов выполнения из кэша процедур.