Конструирование руководств планов для параметризованных запросов
Для параметризованного запроса можно создать руководство плана. Запрос может быть параметризован в силу одной из следующих причин:
- запрос передан на обработку при помощи хранимой процедуры sp_executesql;
- в базе данных активирована принудительная параметризация, при этом параметризуются все допустимые запросы;
- для класса запросов, к которому относится данный запрос, было создано отдельное руководство плана с указанием того, что запросы должны быть параметризованы.
Создавая руководство плана для параметризованного запроса, вы, по сути, создаете руководство плана для всех запросов, параметризуемых в одну форму и различающихся только константами. Например в базе данных, для которой активирована принудительная параметризация, два следующих запроса параметризуются в одну и ту же форму:
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 50
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 100
Чтобы создать руководство плана для параметризованных запросов, создайте руководство плана типа SQL и укажите параметризованную форму запроса в хранимой процедуре sp_create_plan_guide.
Например чтобы получить параметризованную форму одного из запросов из предыдущего примера и создать для него руководство плана, заставляющее оптимизатор использовать хэш-соединение, выполните следующее:
- Получите параметризованную форму запроса, выполнив хранимую процедуру sp_get_query_template.
- Если SQL Server еще не параметризовал запрос с использованием хранимой процедуры sp_executesql или параметра PARAMETERIZATION FORCED базы данных, создайте для выполнения принудительной параметризации руководство плана типа TEMPLATE.
- Создайте для параметризованного запроса руководство плана типа SQL.
Следующий пакет выполняет эти шаги:
--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 101 GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50',
@stmt OUTPUT,
@params OUTPUT
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)'
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(HASH JOIN)'
Теперь руководство плана будет применяться ко всем запросам, параметризуемым в указанную форму, но содержащим разные значения констант.
См. также
Основные понятия
Оптимизация запросов в используемых приложениях с помощью руководств планов
Другие ресурсы
Производительность запроса
sp_create_plan_guide (Transact-SQL)