發行項
04/02/2012

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

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

捲動選項 (API 伺服器資料指標的 @scrollopt 值)	API 伺服器資料指標所支援的 USE PLAN	使用 Transact-SQL 擴充語法的 Transact-SQL 資料指標所支援的 USE PLAN	使用 ISO 語法的 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 ProfilerRPC:Starting 事件。

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

此範例假設您透過 ODBC 資料指標，使用與 AdventureWorks2008R2 資料庫互動的應用程式，而且您想要使用 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 ProfilerRPC:Starting 事件中所看到的陳述式字串與參數定義字串。參數定義字串也需要取得成功的計畫指南搭配，就像它與使用 sp_executesql 所提交的參數化查詢搭配使用一樣。

請參閱

共用方式為