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