設計參數化查詢的計劃指南
您可以在已參數化的查詢上建立計劃指南。基於下列任一理由,都可將查詢參數化:
- 查詢是用 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 資料庫 SET 選項將該查詢參數化,請建立 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)