一 使用背景
1 應用程序數據庫查詢性能低下，而我們又無法獲得源代碼來修改查詢文本
2 我們不希望直接更改查詢文本而又要提高查詢性能
二 基本介紹：
SQL Server 2005引入了稱為”計劃指南”的新特性，可以幫我們解決上述問題。本質上，計劃指南是通過將查詢提示附加到計劃指南來影響查詢的優化。
三 運作原理：
1． 當應用程序向Sql Server發送代碼，查詢優化器首先檢查在緩存中是否有一個適當的詢問計劃，如果有，查詢就按被找到的查詢計劃執行。
2． 如果沒有匹配的查詢計劃，代碼將被與一個稱為內部查尋表(lookup table)比較, 看是否有一個已存在的計劃指南與之匹配
3． 如果在第2步找到匹配的計劃指南，詢問優化器將修改源代碼以包括計劃指南中查詢提示。源代碼中之前的任何查詢提示將被新提示替換
4． 查詢計劃將被編譯和緩存
5． 查詢將按照你之前在計劃指南設定的查詢提示執行。
四 計劃指南的分類：
有三種方式的計劃指南：
1．OBJECT 計劃指南：與在 Transact-SQL 存儲過程、標量函數、多語句表值函數和 DML 觸發器的上下文中執行的查詢匹配。
2．SQL 計劃指南：與在獨立的 Transact-SQL 語句和批處理（不屬于數據庫對象）的上下文中執行的查詢匹配。基于 SQL 的計劃指南還可用于與參數化為指定形式的查詢匹配。SQL 計劃指南適用于應用程序使用 sp_executesql 系統存儲過程頻繁提交的語句和批處理。
3．TEMPLATE 計劃指南：與參數化為指定形式的獨立查詢匹配。這些計劃指南用于覆蓋查詢類的數據庫的當前 PARAMETERIZATION 數據庫 SET 選項。TEMPLATE 計劃指南用于覆蓋特定查詢形式的參數化行為。您可以在以下任一情況下創建 TEMPLATE 計劃指南：
五 可以在計劃指南使用中的查詢提示：
{HASH | ORDER} GROUP
{CONCAT | HASH | MERGE} UNION
{LOOP | MERGE | HASH} JOIN
FAST number_rows
FORCE ORDER
MAXDOP number_of_processors
OPTIMIZE FOR ( @variable_name = literal_constant ) [ ,…n ]
RECOMPILE
ROBUST PLAN
KEEP PLAN
KEEPFIXED PLAN
EXPAND VIEWS
MAXRECURSION number
USE PLAN <xmlplan>
六 使用示例：
以下二個存儲過程創造和處理計劃指南
sp_create_plan_guide
sp_control_plan_guide
1． sp_create_plan_guide
創建用于將查詢提示與數據庫中的查詢進行關聯的計劃指南
語法：
sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
, [ @module_or_batch = ]
{
N'[ schema_name. ] object_name'
| N'batch_text'
| NULL
}
, [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
, [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' | NULL }
示例
sp_create_plan_guide
@name = N'PlanGuideTest',
@stmt = N'SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate
BETWEEN ''1/1/2000'' AND ''1/1/2005'' ',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO
代碼的運作原理
@name = N'PlanGuideTest'
指定計劃指南的名稱為 PlanGuide1
@stmt = N'SELECT COUNT(*) AS Total FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate
BETWEEN "1/1/2000" AND "1/1/2005" '
這段代碼是詢問優化器應該匹配的代碼。 無論應用程序何時向服務器發送這段代碼，詢問優化器設法匹配代碼。當發現代碼和上面列出的匹配時，查詢優化器在內部查詢表(loolup table)中查找正確的計劃指南，并運用計劃指南
@type = N'SQL',
說明計劃指南的類型，這是一個 SQL 計劃指南
@module_or_batch = NULL,
這個代碼行用于指定statement_text對象的名字,僅在計劃指南類型是OBJECT 計劃指南時需要
@params = NULL,
指定嵌入 statement_text 中的所有參數的定義，在Template 計劃指南類型時需要
@hints = N'OPTION (MERGE JOIN)'
指定將 OPTION 子句附加到與 @stmt 匹配的查詢上
2．sp_control_plan_guide
刪除、啟用或禁用計劃指南。
語法
sp_control_plan_guide [ @operation = ] N'<control_option>' [ , [ @name = ]
N'plan_guide_name' ]
<control_option>::=
{
DROP --刪除由plan_guide_name 指定的計劃指南
| DROP ALL --刪除當前數據庫中的所有計劃指南
| DISABLE --禁用由plan_guide_name 指定的計劃指南
| DISABLE ALL --禁用當前數據庫中的所有計劃指南
| ENABLE --啟用由plan_guide_name 指定的計劃指南
| ENABLE ALL --啟用當前數據庫中的所有計劃指南
下面是有些例子的是怎樣使用這個存儲過程
sp_control_plan_guide N'DROP', N'PlanGuideTest'
sp_control_plan_guide N'DISABLE', N'PlanGuideTest'
sp_control_plan_guide N'ENABLE', N'PlanGuideTest'
注： 如果您設法刪除﹑修改﹑一個涉及到計劃指南的存儲過程﹑函數﹑或DML觸發器， 它系統將報錯。 必須先刪除計劃指南。
3．查看計劃指南
可以使用sys.plan_guides目錄視圖查看數據庫中的計劃指南
SELECT * FROM sys.plan_guides
七 使用事件探查器驗證是否執行計劃指南：
1. 啟動事件探查器 跟蹤，選中”性能”->”Showplan XML” 事件類型。
2. 運行查詢
3. 在 Showplan XML 事件中查找受影響的查詢。
4. 如果計劃指南的類型為 OBJECT 或 SQL，則驗證 Showplan XML 事件是否包含您希望與查詢匹配的計劃指南的 PlanGuideDB 和 PlanGuideName 屬性。或者，如果計劃指南的類型為 TEMPLATE，則驗證 Showplan XML 事件是否包含預期計劃指南的 TemplatePlanGuideDB 和 TemplatePlanGuideName 屬性。這可以驗證計劃指南是否在運行。這些屬性包含在計劃的 <StmtSimple> 元素下。
八 計劃指南通常使用的情況：
1． 在參數化的查詢中增加OPTIMIZE FOR or RECOMPILE 詢提示
2． 用USE PLAN查詢提示強制使用更好的查詢計劃
3． 強迫非并行查詢使用MAXDOP查詢提示
4． 強制join查詢試驗join查詢提示
5． 刪除，修改，替代現有的查詢提示
九 最佳實踐：
1． 計劃指南的作用域是創建這些計劃指南所用的數據庫
2． 計劃指南需要有經驗的DBA才能使用
3． 如果沒有其他方法來解決查詢效率問題，請使用一個計劃指南
4． 計劃指南需經過測試才正式使用
5． Sql Server版本涉及或打SP補丁后，需要重新評估計劃指南的效率，以確定是否繼續使用計劃指南
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1505218