移轉查詢計劃

  • 發行項

在大部分情況下，將資料庫升級至最新版本的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 執行程序表插入 @hints 參數內。 為了簡潔起見，只有部分的執行程序表輸出包含在此範例中。

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)
計畫指南