在含有資料指標的查詢上使用 USE PLAN 查詢提示

發行項
12/15/2008

您可以將指定資料指標要求的查詢與 USE PLAN 查詢提示一起使用。下表顯示 API 的 USE PLAN 伺服器資料指標、使用「Transact-SQL 延伸語法」的 Transact-SQL 資料指標以及使用 SQL-92 語法的 Transact-SQL 資料指標所支援的資料指標捲動選項組合。

捲動選項 (API 伺服器資料指標的 @scrollopt 值)	API 伺服器資料指標所支援的 USE PLAN	使用 Transact-SQL Extended Syntax 的 Transact-SQL 資料指標所支援的 USE PLAN	使用 SQL 92 Syntax 的 Transact-SQL 資料指標所支援的 USE PLAN
STATIC	Y	Y	無法使用
DYNAMIC	N	N	無法使用
KEYSET	N	N	無法使用
FORWARD_ONLY	N	N	無法使用
FAST_FORWARD	Y	Y	無法使用
FORWARD_ONLY STATIC	無法使用	Y	無法使用
INSENSITIVE	無法使用	無法使用	Y

含有資料指標的查詢有兩個與它們關聯的查詢計劃，而不是單一個計劃與所提交的無資料指標之查詢關聯。視資料指標的類型而定，這些計劃可能是 OPEN、FETCH 或 REFRESH 類型，端視資料指標的類型而定。

資料指標的兩個計劃之一是直接由輸入查詢所產生，而其他計劃則是自動產生。這些計劃會各自呼叫輸入查詢計劃和產生的計劃。下表顯示 FAST_FORWARD 與 STATIC (INSENSITIVE) 資料指標所產生的計劃。

資料指標類型	開啟資料指標計劃	擷取資料指標劃	重新整理資料指標計劃
FAST_FORWARD	無法使用	輸入查詢	已產生的
STATIC	輸入查詢	已產生的	無法使用

資料指標查詢的 XML 查詢計劃只能以包含兩個計劃的單一 XML 文件出現。這些計劃稱為兩個部份的計劃。

資料指標的計劃有時會以兩個獨立計劃出現。例如，在 STATIC API 或 Transact-SQL 資料指標查詢計劃的 SQL Server Profiler 追蹤中，您可以看到有兩個不同的 Showplan XML For Query Compile 事件產生。在此例中，只有輸入查詢 (OPEN) 計劃對於計劃強制很重要。您應該在 USE PLAN 提示中使用輸入查詢計劃。也會建立簡易產生的 (FETCH) 計劃，但對於計劃強制並不是必要或允許的。您可以辨識輸入查詢 (OPEN) 計劃，因為它是會先收集符合資料指標查詢之資料列集的計劃。

重要事項：
請勿嘗試強制資料指標查詢的非資料指標計劃，反之亦然。如果您這麼做，計劃強制可能會失敗，即使資料指標查詢與非資料指標查詢相同。

下列類型的 XML 查詢計劃輸出，說明資料指標計劃可用以針對特定的資料指標類型，強制含有 USE PLAN 的計劃：

資料指標兩部份的計劃
資料指標一部份的輸入查詢計劃

您所強制的資料指標計劃，可以是透過下列任一取得 XML 查詢計劃機制所取得的計劃：

以 XML 為基礎的 SQL Server Profiler 追蹤事件。這些事件可包含 Showplan XML、Showplan XML For Query Compile 以及 Showplan XML Statistics Profile。
SET SHOWPLAN_XML ON
SET STATISTICS XML ON

動態管理檢視與函數，例如下列查詢：

SELECT *
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

觀察應用程式所使用的 API 伺服器資料指標

使用 API 伺服器資料指標，可使 DB Library、ODBC、ADO 以及 OLEDB 應用程式經常與 SQL Server 互動。您可以查看提交至 API 伺服器資料指標預存程序的呼叫，作法是在使用其中一個執行的介面建立應用程式時，檢查 SQL Server Profiler RPC:Starting 事件。

範例：在含有資料指標的查詢上強制計劃

此範例假設您透過 ODBC 資料指標，使用與 AdventureWorks 資料庫互動的應用程式，而且您想要使用 API 伺服器資料指標常式，強制提交至 SQL Server 的查詢計劃。若要強制計劃，請收集透過資料指標 API 常式提交的查詢計劃，然後建立計劃指南以強制查詢的計劃。讓應用程式再次執行查詢，然後檢查計劃以驗證已強制的計劃。

步驟 1：收集計劃

啟動 SQL Server Profiler 追蹤並選取 Showplan XML 與 RPC:Starting 事件。讓應用程式執行您想要強制計劃的查詢。按一下已產生的 RPC:Starting 事件。假設 RPC:Starting 事件具有下列文字資料：

DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=8
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 OUTPUT,@p2 OUTPUT,NULL,N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT
SELECT @p1, @p2, @p5, @p6, @p7

以滑鼠右鍵按一下 [Showplan XML] 追蹤事件 (包含以上列 sp_cursorprepexec 陳述式的引數出現的查詢之輸入查詢計劃)，來收集查詢的計劃，然後選取 [擷取事件資料]。在桌面上，將事件資料 (XML Showplan) 儲存到檔案 CursorPlan.SQLPlan。將檔案 CursorPlan.SQLPlan 複製到 CursorPlan.txt。在 SQL Server Management Studio 的編輯器視窗中開啟 CursorPlan.txt。為了節省時間，請使用 [尋找和取代] 以四個單引號 ('''') 取代計劃中的每個單引號 (')。儲存 CursorPlan.txt。

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

撰寫和執行下列 sp_create_plan_guide 陳述式來強制計劃以建立計劃指南。此計劃指南定義包含在上一步驟中計劃指南的 USE PLAN 查詢提示內所擷取的 XML 計劃。

當您撰寫此計劃指南的定義時，請將 CursorPlan.txt 的內容貼到 @hints 引數 (就在 OPTION(USE PLAN N'' 的後面) 的適當位置內。

exec sp_create_plan_guide 
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@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>'')'

步驟 3：執行查詢並驗證套用至查詢的計劃指南

透過使用 SQL Server Profiler 中的 XML Showplan 事件，讓應用程式再次執行查詢，並收集其 XML 執行計劃。

按一下計劃的 [XML Showplan] 事件。您應該會發現該計劃是您在計劃指南中所強制的計劃。

參數化的資料指標查詢

如果您想要建立計劃指南的 API 伺服器資料指標查詢是經過參數化的，請確定同時包含您在計劃指南定義的 SQL Server Profiler RPC:Starting 事件中所看到的陳述式字串與參數定義字串。參數定義字串也需要取得成功的計劃指南搭配，就像它與使用 sp_executesql 所提交的參數化查詢搭配使用一樣。

請參閱

共用方式為