sp_create_plan_guide (Transact-SQL)

Для структур планов, содержащих @type = 'SQL' или @type = 'TEMPLATE' для должного соответствия запросу, значения batch_text и @parameter\_name data_type [,...n ] необходимо задавать в формате, используемом для определения соответствующих им параметров приложения. Это означает, что необходимо предоставить текст пакета в точном соответствии с текстом, получаемым компилятором SQL Server. Для захвата действительного текста пакета и параметра используется приложение Приложение SQL Server Profiler. Дополнительные сведения см. в разделе Использование приложения SQL Server Profiler для создания и проверки структур планов.

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

Когда SQL Server сравнивает значение аргумента statement_text с аргументом batch_text и аргументом @parameter\_name data_type [,...n ] или (когда @type = **'**OBJECT') с текстом соответствующего запроса внутри аргумента object_name, следующие элементы строки не рассматриваются.

• Пробельные символы (знаки табуляции, пробелы, возвраты каретки и переводы строки) внутри строки.

• Комментарии (-- или /* */).

• Точка с запятой (;) в конце строки.

Например, в SQL Server могут совпадать строка N'SELECT * FROM T WHERE a = 10' аргумента statement_text и значение следующего аргумента batch_text.

N'SELECT *

FROM T

WHERE a=10'

Однако та же строка не совпадет с этим значением аргумента batch_text.

N'SELECT * FROM T WHERE b = 10'

SQL Server игнорирует знаки возврата каретки, перевода строки и пробелы внутри первого запроса. При рассмотрении второго запроса строки WHERE b = 10 и WHERE a = 10 считаются различными. Результат сравнения учитывает регистр и наличие диакритических знаков (даже в случае, когда в параметрах сортировки базы данных задана сортировка без учета регистра), за исключением тех случаев, когда используются ключевые слова, игнорирующие регистр. Сравнение выполняется без учета сокращенных форм ключевых слов. Например, ключевые слова EXECUTE, EXEC и execute являются эквивалентными.

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

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

В приведенном ниже примере создается структура плана, с которой сопоставляется запрос, выполняемый в контексте хранимой процедуры приложения, а также происходит применение к запросу подсказки OPTIMIZE FOR.

Ниже приведена хранимая процедура.

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

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

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

В приведенном ниже примере создается структура плана, с которой сопоставляется запрос из пакета, переданного приложением, использующим системную хранимую процедуру sp_executesql.

Ниже представлен пакет.

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

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

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

В приведенном ниже примере создается структура плана, которой сопоставляется запрос заданной параметризованной формы и которое вынуждает 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, см. в разделе Конструирование структур планов для параметризованных запросов.

Структуры планов могут совпадать с запросами, передаваемыми с помощью процедур серверных курсоров API. К указанным процедурам относятся sp_cursorprepare, sp_cursorprepexec и sp_cursoropen. Приложения, использующие интерфейс ADO, OLE DB и ODBC, обычно связываются с SQL Server при помощи серверных курсоров API. Дополнительные сведения см. в разделе Серверные курсоры API. Вызов процедур серверного курсора API-интерфейса можно увидеть в трассировках приложения Приложение SQL Server Profiler, просматривая событие трассировки SQL Profiler RPC:Starting.

Допустим, что для запроса, настраиваемого с помощью структуры плана, в событии трассировки RPC:Starting приложения SQL Profiler имеются следующие данные.

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Пусть необходимо изменить следующую настройку запроса SELECT вызова процедуры sp_cursorprepexec: используемое соединение слиянием необходимо заменить хэш-соединением. Запрос, передаваемый с помощью процедуры sp_cursorprepexec, является параметризованным, включая строку запроса и строку параметров. В точности копируя строки запроса и параметров вызова процедуры sp_cursorprepexec, можно создать следующую структуру плана.

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

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

Дополнительные сведения об использовании подсказки в запросе USE PLAN в структуре плана для выполнения запросов, переданных курсором, см. в разделе Использование подсказки USE PLAN в запросах с курсорами.

В следующем примере создается структура плана для простой нерегламентированной инструкции 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