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

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

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

Сопоставление структур планов с запросами

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

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

• Структура плана SQL соответствует запросам, выполняющимся в контексте изолированных инструкций Transact-SQL и пакетов, не входящих ни в один объект базы данных. Структуры планов SQL также можно использовать для соответствия запросам с параметрами.

• Структура плана TEMPLATE соответствует изолированным инструкциям с параметрами. Эти структуры планов используются для замещения текущего параметра PARAMETERIZATION инструкции SET базы данных для класса запросов. Дополнительные сведения см. в разделах Простая параметризация и Принудительная параметризация.

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

Структуры планов OBJECT

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

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

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

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

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

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

Структуры планов SQL

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

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

Чтобы избежать создания параллельного плана выполнения для этого запроса, создайте приведенную ниже структуру плана и присвойте подсказке запроса MAXDOP значение 1 в параметре @hints.

sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader 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 (по умолчанию), но для определенного класса запросов желательно использовать принудительную параметризацию.

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

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

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Ниже представлена структура плана для параметризованного запроса.

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

В предыдущем примере значение аргумента @stmt является параметризованной формой запроса. Единственным надежным способом выяснения указанного значения для использования в процедуре sp_create_plan_guide является использование системной хранимой процедуры sp_get_query_template. С помощью следующего сценария можно выполнить параметризованный запрос, а затем создать на его основе структуру плана.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Важно!
Значения постоянных литералов аргумента @stmt, передаваемого в процедуру sp_get_query_template, определяют тип данных для аргумента, заменяющего указанные литералы. Это влияет на совпадение структур планов. Возможно, придется создать несколько структур плана для обработки различных диапазонов значений аргумента.

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

Применение фиксированного плана запроса к структуре плана

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

В следующем примере создается структура плана для простой нерегламентированной инструкции SQL. Требуемый план запроса для этого оператора представлен в структуре плана путем указания XML Showplan для запроса непосредственно в параметре @hints . В примере сначала выполняется инструкция SQL для создания плана в кэше планов. Допустим, что созданный план является желаемым планом и дополнительной настройки запросов не требуется. Данные XML Showplan для запроса получаются путем запроса динамических административных представлений sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_text_query_plan и присваивания их переменной @xml\_showplan. Переменная @xml\_showplan затем передается оператору sp_create_plan_guide в параметре @hints. Также можно создать структуру плана на основе плана запроса в кэше планов, используя хранимую процедуру sp_create_plan_guide_from_handle.

USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

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

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

• Проверьте существующие структуры планов с помощью функции sys.fn_validate_plan_guide.

• В приложении Приложение SQL Server Profiler проверьте наличие планов без структуры за определенное время с помощью события Plan Guide Unsuccessful.

См. также

Справочник

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

Подсказки в запросах (Transact-SQL)

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

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

Принудительная параметризация

Простая параметризация

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

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