發行項
09/24/2010

計畫強制案例：建立計畫指南以強制從重寫查詢取得計畫

通常取得查詢改善計畫最方便的方法，是使用查詢提示手動重寫查詢以強制聯結順序、聯結演算法或索引使用方式，而不需變更查詢的邏輯意義。然而，如果查詢是在已部署的應用程式中，此方法可能無法使用。使用計畫指南對於這個狀況很有幫助。當無法直接變更查詢的文字時，可將查詢提示或查詢計畫附加至查詢中，查詢指南就會發生效用。如需詳細資訊，請參閱＜使用計畫指南對已部署應用程式中的查詢進行最佳化＞。

若要手動重寫查詢，請擷取該查詢的計畫，然後套用擷取的計畫至原始查詢 (使用包含擷取之計畫的計畫指南)，並遵循以下程序：

變更聯結順序、使用 FORCE ORDER、使用聯結提示、索引提示以及利用其他的技術，以決定如何修改查詢，才能為查詢產生良好的計畫，但並不會變更查詢的邏輯意義。
擷取重寫查詢的計畫，提交像是原始查詢 (例如使用 sp_executesql、sp_cursorprepexec 或做為獨立批次)。
建立計畫指南以便在原始查詢上強制執行所取得的良好計畫。
使用 SQL Server Profiler，並從 [效能] 類別目錄選取 Plan Guide Successful 和 Plan Guide Unsuccessful 事件，然後執行原始的查詢。檢查 SQL Server Profiler，以確認此查詢有使用計畫指南。

範例

假設下列查詢所產生的查詢計畫太慢。

USE AdventureWorks;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
    ,e.Title
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID 
    INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

您可用下列相同的邏輯方式但以不同的聯結順序重寫查詢，這是強制的。

USE AdventureWorks;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
    ,e.Title
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID 
    INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

若要建立計畫指南以強制使用原始查詢上重寫之查詢的計畫，請在變數中擷取此計畫，並在計畫指南陳述式中指定此變數，如下列程式碼所示。

DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
    ,e.Title
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID 
    INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
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'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
    soh.SalesPersonID
    ,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
    ,e.Title
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID 
    INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
    WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

在您執行原始查詢以前，請使用 SQL Server Profiler 建立追蹤，並從 [效能] 類別目錄選取 Plan Guide Successful 和 Plan Guide Unsuccessful 事件。執行原始查詢，並確認追蹤輸出內的查詢結果。

USE AdventureWorks;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
    ,e.Title
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID 
    INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

請參閱

共用方式為

計畫強制案例：建立計畫指南以強制從重寫查詢取得計畫

範例

請參閱

工作

參考

概念

其他資源

其他資源