詳解SQL Server 2008遷移查詢計劃

作者：佚名 2009-03-17 13:25:13

本文將重點介紹SQL Server 2008中的遷移查詢計劃，包括相關步驟以及如何強制實施計劃。

大多數情況下，將數據庫升級到 SQL Server 2008 會提高查詢性能。但是，如果您具有已針對性能進行過認真優化的任務關鍵查詢，在升級前***為每個查詢創建一個計劃指南，以保留這些查詢的查詢計劃。如果在升級后，查詢優化器為一個或多個查詢選擇了效率較低的計劃，則可以啟用這些計劃指南并強制查詢優化器使用升級前的計劃。

若要在升級前創建計劃指南，請按照以下步驟執行操作：

通過使用 sp_create_plan_guide 存儲過程并在 USE PLAN 查詢提示中指定查詢計劃來記錄每個任務關鍵查詢的當前計劃。

驗證計劃指南是否適用于此查詢

將數據庫升級到 SQL Server 2008。

計劃保留在升級后的數據庫中的計劃指南中，如果在升級后計劃的性能出現退步，則這些計劃將用作后備計劃。
建議您在升級后不要啟用計劃指南，因為由于統計信息進行了更新，您可能會錯過新版本中的更好計劃或者重新編譯所帶來的益處。

如果在升級后選擇了效率較低的計劃，可以激活所有計劃指南或部分計劃指南以取代新計劃。

示例

下面的示例顯示如何通過創建計劃指南來為查詢記錄升級前的計劃。

步驟 1：收集計劃

計劃指南中記錄的查詢計劃必須采用 XML 格式。可通過以下方式生成 XML 格式的查詢計劃：

SET SHOWPLAN_XML
SET STATISTICS XML

查詢 sys.dm_exec_query_plan 動態管理函數的 query_plan 列。

SQL Server Profiler Showplan XML、Showplan XML Statistics Profile 和 Showplan XML For Query Compile 事件類。

下面的示例通過查詢動態管理視圖收集語句

 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;%';

步驟 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 中收集的查詢計劃創建計劃指南，并在 @hints 參數中插入此查詢的 XML 顯示計劃。為簡潔起見，此示例中僅包括部分顯示計劃輸出。

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=''''
http://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：驗證計劃指南是否適用于查詢

再次運行查詢，并檢查生成的查詢計劃。您應看到該計劃與您在計劃指南中指定的計劃相符。

【編輯推薦】

SQL Server 2005常見問題淺析
并行查詢讓SQL Server加速運行
SQL Server數據庫優化經驗總結

責任編輯：彭凡來源： MSDN

查詢遷移 SQL Server