У SQL Server є пул пам'яті, призначений для зберігання планів виконання і буферів даних. Процентне співвідношення розміру пулу, виділеного для планів виконання і буферів даних, динамічно змінюється в залежності від стану системи. Частина пулу пам'яті, що використовується для зберігання планів виконання, називається кешем процедур.
У SQL Server плани виконання складаються з наступних основних компонентів.
Тіло плану виконання є реєнтерабельним структурою даних тільки для читання, яка призначена для використання будь-яким числом користувачів. Воно називається планом запиту. План запиту не містить контексту користувача. У пам'яті не може перебувати більше однієї або двох копій плану запиту: одна - для всіх послідовних виконань, а інша - для всіх паралельних виконань. Одна паралельна копія обслуговує всі паралельні виконання незалежно від ступеня паралелізму.
Для кожного користувача, який зараз виконує запит, є структура даних, яка містить дані, що відносяться до даного виконання, наприклад значення параметрів. Ця структура даних називається контекстом виконання. Структури даних контексту виконання є повторно використовуваними. Якщо користувач виконує запит і одна з структур не використовується, вона повторно ініціалізується контекстом нового користувача.
У SQL Server при виконанні інструкції SQL реляційний механізм спочатку переглядає кеш процедур, перевіряючи, чи немає в ньому плану виконання для такої ж інструкції. SQL Server повторно використовує будь-який план виконання, який знайде, знижуючи витрати на його повторну компіляцію. Якщо не знайдено жодного існуючого плану, SQL Server формує для цього запиту новий план.
SQL Server реалізує ефективний алгоритм пошуку існуючих планів виконання для будь-якої інструкції SQL. У більшості систем ресурси, що витрачаються на пошук готового плану, завжди менше ресурсів, що витрачаються на повторну компіляцію кожної інструкції SQL.
Алгоритми пошуку відповідності інструкції SQL існуючого невикористовуваних плану виконання в кеші вимагають, щоб всі посилання на об'єкти були повними. Наприклад, для першої з наступних інструкцій SELECT відповідність чинному плану не буде знайдено, а для другого - буде:
Плани виконання залишаються в кеші процедур до тих пір, поки для їх зберігання залишається досить пам'яті. При нестачі пам'яті компонент 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: StmtRecompile інформує вас про Перекомпіляція на рівні інструкцій. Воно може застосовуватися для трасування і налагодження Перекомпіляція. Подія SP: Recompile створюється тільки для збережених процедур і тригерів, а SQL: StmtRecompile - для збережених процедур, тригерів, спеціальних пакетів, пакетів, які виконуються через sp_executesql. підготовлених запитів і динамічного SQL.
Стовпець EventSubClass для подій SP: Recompile і SQL: StmtRecompile містить код у вигляді цілого числа, що позначає причину перекомпіляції. У наступній таблиці перераховано значення для кожного з цих кодів.