Основные сведения о руководствах планов

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

Примечание.
Руководства планов могут создаваться и использоваться только в выпусках SQL Server 2005 Standard, Developer, Evaluation и Enterprise. Удалять руководства планов можно во всех выпусках.

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

Другая часто используемое руководство плана — подсказка USE PLAN в запросе. Эта подсказка в запросе применяется, когда уже известно, что существующий план выполнения может быть заменен на выбранный оптимизатором для некоего определенного запроса, так как известно, что с новым планом выполнение будет быстрее. Подсказка USE PLAN принуждает SQL Server использовать конкретный план запроса, явно заданный в синтаксисе подсказки при выполнении запроса. Руководство плана, применяемое в запросе подсказкой USE PLAN, особенно полезно, когда удобно получать хороший план выполнения, переписав запрос и изменив порядок соединения с помощью подсказок соединений или индексов. Дополнительные сведения см. в разделе Сценарий форсирования планов: создайте руководство плана для принудительного исполнения плана, полученного из перезаписанного запроса.

Дополнительные сведения о RECOMPILE, OPTIMIZE FOR, USE PLAN и других подсказках в запросе см. в разделе Подсказка в запросе (Transact-SQL).

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

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

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

Руководства планов OBJECT

Рассмотрим следующую хранимую процедуру, принимающую параметр @Country, которая существует в приложении базы данных, развертываемом для базы данных AdventureWorks.

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

Обратите внимание, что эта хранимая процедура скомпилирована и оптимизирована для значения @Country = N'AU' (Австралия). Однако из Австралии поступает сравнительно мало заказов. Когда запрос выполняется со значениями параметра, соответствующими странам, откуда поступает больше заказов, производительность снижается. Так как страна, из которой поступает больше всего заказов — США, план запроса, сформированный для значения @Country=N'US', вероятно, обеспечит лучшую производительность для всех возможных значений параметра @Country.

Эту проблему можно решить, изменив хранимую процедуру и добавив подсказку OPTIMIZE FOR в запрос. Однако так как хранимая процедура находится в развернутом приложении, напрямую менять код приложения нельзя. Вместо этого можно создать следующее руководство плана в базе данных AdventureWorks.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

При выполнении запроса, указанного в инструкции sp_create_plan_guide, этот запрос изменяется до оптимизации, чтобы включить в себя предложение OPTIMIZE FOR (@Country = N''US'').

Руководства планов SQL

Руководства планов SQL применяются к инструкциям и пакетам, часто выполняющимся приложением с помощью системной хранимой процедуры sp_executesql. Например, рассмотрим следующий изолированный пакет:

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

Чтобы предотвратить создание по этому запросу параллельного плана выполнения, создайте следующее руководство плана:

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

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

Важно!
Значения, предоставленные для аргументов @module_or_batch и @params инструкции sp_create_plan guide должны соответствовать тексту буквально, как если бы передавались настоящему запросу. Дополнительные сведения см. в разделах sp_create_plan_guide (Transact-SQL) и Использование приложения SQL Server Profiler для создания и проверки руководств планов.

Руководства планов SQL также можно создавать для запросов с той же параметризованной формой, если значением параметра PARAMETERIZATION инструкции SET является FORCED или если есть руководство плана TEMPLATE, определяющее, что класс запросов должен быть параметризован. Дополнительные сведения см. в разделе Конструирование руководств планов для параметризованных запросов.

Руководства планов TEMPLATE

Руководства планов TEMPLATE используются для замещения поведения параметризации в отдельных формах запросов. Руководства планов TEMPLATE создаются в одной из следующих ситуаций.

• Параметр базы данных PARAMETERIZATION инструкции SET хранит значение FORCED, но есть запросы, которые желательно скомпилировать в соответствии с правилами простой параметризации.
• Параметр базы данных PARAMETERIZATION инструкции SET хранит значение SIMPLE (по умолчанию), но для определенного класса запросов желательно использовать принудительную параметризацию.

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

Руководства планов TEMPLATE также можно использовать совместно с руководствами планов SQL. Например, можно создать руководство плана TEMPLATE, чтобы убедиться, что класс запросов будет параметризован. Затем можно создать руководство плана SQL для параметризованной формы запроса.

См. также

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

Оптимизация запросов в используемых приложениях с помощью руководств планов
Проектирование и реализация руководств планов

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

Производительность запроса
sp_create_plan_guide (Transact-SQL)
Хранимая процедура sp_control_plan_guide (Transact-SQL)
sys.plan_guides

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

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