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

發行項
12/15/2008

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

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

變更聯結順序、使用 FORCE ORDER、使用聯結提示、索引提示以及其他的技術以決定如何修改查詢，才能為查詢產生良好的計劃，但並不會變更查詢的邏輯意義。
擷取重寫查詢的計劃，提交像是原始查詢 (例如使用 sp_executesql、sp_cursorprepexec 或做為獨立批次)。
附加包含已擷取計劃的 OPTION (USE PLAN) 查詢提示子句，來修改原始查詢的複本，並測試看看您是否可在查詢上執行擷取的計劃。
如果測試失敗，請嘗試其他查詢重寫，或者進行偵錯，直到您取得可以在原始查詢上強制執行的適合計劃。
建立計劃指南以便在原始查詢上強制執行所取得的良好計劃。

範例

假設下列查詢所產生的查詢計劃太慢：

USE AdventureWorks;
GO
SET STATISTICS XML ON;
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
SET STATISTICS XML OFF;
GO

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

USE AdventureWorks;
GO
SET STATISTICS XML ON;
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].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    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)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

在擷取重寫查詢的 STATISTICS XML 查詢計劃時，在原始查詢上測試它、建立計劃指南以強制原始查詢上的計劃，如下列程式碼所示：

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@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 = N'OPTION (USE PLAN 
N''?put XML showplan for modified query here ?')'

請確定在替換 @hints 字串中的查詢計換之前，先使用四個單引號 ('''') 來逸出 XML 查詢計劃內的單引號 (')。這是因為在兩個字串常值中巢狀化查詢計劃。

請參閱

共用方式為

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

範例

請參閱

工作

概念

其他資源

說明及資訊

其他資源