共用方式為

  • 發行項

瞭解計畫指南

此主題描述計畫指南並說明當您無法或不想要直接變更查詢的文字時，如何使用計畫指南來最佳化查詢的效能。當從協力廠商所部署的資料庫應用程式中查詢的小部分子集未依預期執行時，計畫指南就可以發揮其效用。計畫指南是將查詢提示或固定的查詢計畫附加至查詢，藉以影響查詢的最佳化。在計畫指南中，指定您要最佳化的 Transact-SQL 陳述式以及包含您想要使用之查詢提示的 OPTION 子句，或者是您想要用來將查詢進行最佳化的特定查詢計畫。執行查詢時，SQL Server 會比對 Transact-SQL 陳述式與計畫指南，然後在執行階段中，將 OPTION 子句附加至查詢或使用指定的查詢計畫。

[!附註]

雖然計畫指南只能在 SQL Server Standard、Developer、Evaluation 和 Enterprise 版本中使用，但是計畫指南仍會顯示於所有版本中。您也可以將包含計畫指南的資料庫附加到任何版本中。當您將資料庫還原或附加至 SQL Server 2008 的升級版本時，計畫指南仍維持不變。

讓計畫指南搭配查詢

您可以建立計畫指南來搭配在下列內容中執行的查詢：

  • OBJECT 計畫指南可搭配在 Transact-SQL 預存程序、純量使用者定義函數、多個陳述式資料表值使用者定義函數以及 DML 觸發程序內容中執行的查詢。

  • SQL 計畫指南可搭配在不屬於資料庫物件的獨立 Transact-SQL 陳述式與批次內容中執行的查詢。以 SQL 為基礎的計畫指南可用以搭配參數化為指定形式的查詢。

  • TEMPLATE 計畫指南可搭配參數化為指定形式的獨立查詢。這些計畫指南可用以針對查詢類別，覆寫資料庫目前的 PARAMETERIZATION 資料庫 SET 選項。如需詳細資訊，請參閱＜簡單參數化＞和＜強制參數化＞。

如需詳細資訊，請參閱＜SQL Server 如何比對計畫指南與查詢＞。

OBJECT 計畫指南

假設下列採用 @Country\_region 參數的預存程序存在針對 AdventureWorks 資料庫所部署的資料庫應用程式中：

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;

假設此預存程序已針對 @Country\_region = N'AU' (澳洲) 編譯及最佳化。不過，由於來自澳洲的銷售訂單相當少，因此當查詢在多個銷售訂單上使用國家 (地區) 的參數值執行時，效能就會降低。由於大部分的銷售訂單都是來自美國，所以針對 @Country\_region = N'US' 所產生的查詢計畫可能會比 @Country\_region 參數的所有可能值具有更好的執行效能。

您可以修改預存程序並將 OPTIMIZE FOR 查詢提示加入查詢以處理此問題。不過，因為預存程序是在已部署的應用程式中，所以您無法直接修改應用程式的程式碼。您只能在 AdventureWorks 資料庫中建立下列計畫指南。

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

執行在 sp_create_plan_guide 陳述式中指定的查詢時，在最佳化前會先修改查詢以包含 OPTIMIZE FOR (@Country = N''US'') 子句。

SQL 計畫指南

SQL 計畫指南會套用至獨立 Transact-SQL 陳述式和批次。這些陳述式通常是由應用程式使用 sp_executesql 系統預存程序進行提交。例如，請考慮下列獨立批次：

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

若要防止這項查詢產生平行執行計畫，請建立下列計畫指南並將 @hints 參數中的 MAXDOP 查詢提示設定為 1。

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

[!附註]

在包含要建立計畫指南的陳述式之批次中，將無法包含 USE database 陳述式。

重要注意事項重要事項

針對 sp_create_plan guide 陳述式的 @module_or_batch 與 @params 引數所提供的值必須符合在實際查詢中所提交的對應文字。如需詳細資訊，請參閱＜sp_create_plan_guide (Transact-SQL)＞和＜使用 SQL Server Profiler 建立及測試計畫指南＞。

當 PARAMETERIZATION 資料庫選項 SET 為 FORCED 時，或是當建立 TEMPLATE 計畫指南以指定要參數化的查詢類別時，SQL 計畫指南也可在參數化為相同形式的查詢上建立 SQL 計畫指南。如需詳細資訊，請參閱＜設計參數化查詢的計畫指南＞。

TEMPLATE 計畫指南

TEMPLATE 計畫指南可用以覆寫特定查詢形式的參數化行為。您可以在下列其中一種情況下建立 TEMPLATE 計畫指南：

  • PARAMETERIZATION 資料庫選項設定成 FORCED，但有一些要根據簡單參數化規則編譯的查詢。

  • PARAMETERIZATION 資料庫選項設定成 SIMPLE (預設值)，但是要在某個查詢類別嘗試強制參數化。

如需詳細資訊，請參閱＜使用計畫指南指定查詢參數化行為＞。

下列範例會建立與參數化為特定格式的任何查詢相符的計畫指南，並導引 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)';
重要注意事項重要事項

傳送到 sp_get_query_template 之 @stmt 參數中的常數常值，可能會影響針對取代常值的參數所選擇的資料類型。這會影響計畫指南的比對作業。您可能需要建立一份以上的計畫指南，來處理不同的參數值範圍。

您也可以同時使用 TEMPLATE 計畫指南與 SQL 計畫指南。例如，您可以建立 TEMPLATE 計畫指南以確定參數化查詢類別。接著就可以在該查詢的參數化形式上建立 SQL 計畫指南。

將固定的查詢計畫套用至計畫指南

您可以將固定的查詢計畫套用至 OBJECT 或 SQL 類型的計畫指南。當您知道現有執行計畫的效能比最佳化工具針對特定查詢所選取的計畫更好時，套用固定查詢計畫的計畫指南就很有用。

下列範例會針對簡單的特定 SQL 陳述式建立計畫指南。直接以 @hints 參數指定查詢的 XML 執行程序表，就可以在計畫指南中提供此陳述式所需的查詢計畫。此範例會先執行 SQL 陳述式以便在計畫快取中產生計畫。基於此範例的目的，假設產生的計畫為所需的計畫，而且不需要額外調整查詢。查詢的 XML 執行程序表會透過查詢 sys.dm_exec_query_stats、sys.dm_exec_sql_text 和 sys.dm_exec_text_query_plan 動態管理檢視取得，而且會被指派給 @xml\_showplan 變數。然後，系統會將 @xml\_showplan 變數傳遞到 @hints 參數的 sp_create_plan_guide 陳述式中。或者，您可以使用 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

在升級之後驗證計畫指南

我們建議當您將應用程式升級至 SQL Server 的新版時，重新評估和測試計畫指南定義。效能微調需求和計畫指南符合的行為有可能會變更。雖然無效的計畫指南不會導致查詢失敗，但是系統將不會使用此計畫指南來編譯計畫，而且此計畫可能不是最佳選擇。將資料庫升級為 SQL Server 2008 之後，我們建議您執行下列工作：