共用方式為

移轉查詢計劃

在大部分情況下，將資料庫升級至最新版的 SQL Server 會導致改善查詢效能。 不過，如果您有已仔細調整效能的任務關鍵性查詢，建議您先保留這些查詢的查詢計劃，再升級，方法是為每個查詢建立計劃指南。 如果在升級之後，查詢優化器會為一或多個查詢選擇效率較低的計劃，您可以啟用計劃指南，並強制查詢優化器使用升級前計劃。

若要在升級之前先建立計劃指南，請遵循下列步驟：

  1. 使用 sp_create_plan_guide 預存程式，並在USE PLAN查詢提示中指定查詢計劃，記錄每個任務關鍵性查詢的目前計劃。

  2. 確認計劃指南已套用至查詢。

  3. 將資料庫升級至較新版本的 SQL Server。

    這些計劃會儲存在升級後資料庫的計劃指南中，並在升級後發生計劃回歸時作為備援。

    建議您不要在升級之後啟用計劃指南，因為您可能會因為更新的統計數據而錯過新版中較佳計劃的機會或有益的重新編譯。

  4. 如果在升級後選擇了效率較低的方案，可以啟動計劃指南的所有或部分以覆寫新的方案。

範例

下列範例示範如何藉由建立計劃指南來記錄查詢的升級前計劃。

步驟 1：收集方案

在計劃指南中記錄的查詢計劃必須是 XML 格式。 XML 格式的查詢計畫可以透過下列方式產生：

下列範例會藉由查詢動態管理檢視來收集 語句 SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; 的查詢計劃。

USE AdventureWorks;  
GO  
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;%';  
GO

步驟 2：建立計劃指南以強制計劃

在計劃指南中使用 XML 格式的查詢計劃（由先前所述的任何方法取得），將查詢計劃複製並貼上為字串常值，放入 sp_create_plan_guide 的 OPTION 子句中指定的 USE PLAN 查詢提示內。

在 XML 計劃本身內，在建立計劃指南時，先以第二個引號替換計劃中的單引號 （'）。 例如，必須將程式代碼修改為 WHERE A.varchar = ''This is a string''，以逸出包含 WHERE A.varchar = 'This is a string' 的計劃。

下列範例會為在步驟 1 中收集的查詢計劃建立計劃指導，並將查詢的 XML Showplan 插入到 @hints 參數中。 為了簡潔起見，範例中只會包含部分 Showplan 輸出。

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO

步驟 3：確認計劃指南已套用至查詢

再次執行查詢，並檢查產生的查詢計劃。 您應該會看到方案符合您在計劃指南中指定的方案。

另請參閱

sp_create_plan_guide （Transact-SQL）
查詢提示 (Transact-SQL)
計畫指南

  • Last updated on