Статья
04/01/2012

Проектирование и реализация структур планов

Структуры плана используются для оптимизации производительности запросов, когда изменить непосредственно текст запроса невозможно или нежелательно. Структуры планов влияют на оптимизацию запросов, присоединяя к ним подсказки в запросе или фиксированные планы запроса. Можно создавать структуры планов, которые будут соответствовать запросам, выполняющимся в таких контекстах.

Структура плана OBJECT соответствует запросам, выполняющимся в контексте хранимых процедур Transact-SQL, определяемых пользователем скалярных функций, определяемых пользователем многооператорных функций, возвращающих табличное значение, и триггеров DML.
Структура плана SQL соответствует запросам, выполняющимся в контексте изолированных инструкций Transact-SQL и пакетов, не входящих ни в один объект базы данных. Структуры планов SQL также можно использовать для соответствия запросам с параметрами.
Структура плана TEMPLATE соответствует изолированным параметризованным запросам. Эти структуры планов используются для переопределения текущего параметра PARAMETERIZATION инструкции SET базы данных для класса запросов.

Дополнительные сведения см. в разделе Основные сведения о структурах планов.

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

Рекомендуется переоценить и протестировать определения структур планов при обновлении приложения для работы с новым выпуском SQL Server. Требования к настройке производительности и поведение сопоставления структур планов могут меняться. Несмотря на то, что неверная структура плана не приведет к ошибочному завершению запроса, компиляция плана осуществляется без использования структуры плана. После обновления базы данных рекомендуется при помощи функции sys.fn_validate_plan_guide выполнить следующие задачи, чтобы проверить существующие структуры планов. Кроме того, отслеживать недопустимые структуры планов можно при помощи события Ошибочная структура плана в Приложение SQL Server Profiler.

Примечание
Структура плана может использоваться только в выпусках SQL Server Standard Edition, Developer Edition, Evaluation Edition и Enterprise Edition, однако структуры плана видны в любой версии. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server.

Структура плана может использоваться только в выпусках SQL Server Standard Edition, Developer Edition, Evaluation Edition и Enterprise Edition, однако структуры плана видны в любой версии. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server.

Присоединение подсказок в запросах к структуре плана

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

Внимание!
Структуры планов с неправильным использованием подсказок в запросе могут привести к проблемам при компиляции во время выполнения и ухудшению производительности. Структуры планов должны применяться только опытными разработчиками и администраторами баз данных.

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

Общие подсказки в запросах, используемые в структурах планов

Запросы, получающие преимущества от структур планов, обычно используют параметры и могут выполняться неэффективно, так как при их выполнении кэшируются планы запросов со значениями параметров, не соответствующими сценарию худшего случая или типичному сценарию. Для решения этой проблемы можно использовать в запросе подсказки OPTIMIZE FOR и RECOMPILE. Подсказка OPTIMIZE FOR сообщает SQL Server, что нужно использовать конкретное значение параметра при оптимизации запроса. Подсказка RECOMPILE сообщает серверу, что после выполнения план запроса нужно удалить и что оптимизатору запросов нужно будет перекомпилировать план при следующем выполнении запроса. См. пример в разделе Основные сведения о структурах планов.

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

Присоединение плана запроса к структуре плана

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

Чтобы присоединить какой-либо план запроса к структуре плана, можно указать XML Showplan этого плана в параметре xml_showplan инструкции sp_create_plan_guide или задать обработку кэшированного плана в инструкции sp_create_plan_guide_from_handle. Оба метода применяют к целевому запросу фиксированный план запроса.

Требования по соответствию для структур планов

Структуры планов действительны в области видимости базы данных, в которой они создаются. Следовательно, только структуры планов, существующие в базе данных, которая является текущей на момент выполнения запроса, могут быть сопоставлены с запросом. Например, если База данных AdventureWorks2008R2 является текущей базой данных и выполняется нижеследующий запрос:

SELECT FirstName, LastName FROM Person.Person;

Только структуры планов в базе данных База данных AdventureWorks2008R2 подлежат сопоставлению с этим запросом.

Но если База данных AdventureWorks2008R2 является текущей базой данных и выполняются нижеследующие инструкции:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

Только структуры планов в DB1 подлежат сопоставлению с запросом, поскольку запрос выполняется в контексте DB1.

В структурах плана, основанных на SQL или TEMPLATE, SQL Server сопоставляет с запросом значения аргументов @module_or_batch и @params, сравнивая эти два значения посимвольно. Это означает, что необходимо предоставить текст точно в том же виде, в каком SQL Server получит его в действительном пакете.

Если @type = 'SQL' и @module_or_batch имеет значение NULL, то значение @module_or_batch устанавливается равным @stmt. Это значит, что значение statement_text должно быть объявлено в точно таком же формате, символ за символом, как предложено в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.

В целом следует тестировать структуры планов с использованием приложения Приложение SQL Server Profiler, чтобы убедиться, что запрос сопоставляется со структурой плана. Тестирование структур планов, основанных на SQL или TEMPLATE, посредством выполнения пакетов из среды Среда SQL Server Management Studio может привести к неожиданным результатам. Дополнительные сведения см. в разделе Использование приложения SQL Server Profiler для создания и проверки структур планов.

Примечание
Пакет, содержащий инструкцию, по которой нужно создать структуру плана, не может содержать инструкцию USE database.

Влияние структуры плана на кэш планов

Создание структуры плана в модуле стирает план запроса для этого модуля из кэша планов. Создание структуры плана типа OBJECT или SQL в пакете стирает план запроса для пакета, который имеет такое же значение хеш-функции. Создание структуры плана типа TEMPLATE стирает все пакеты с одной инструкцией из кэша планов в базе данных.