在含有資料指標的查詢上使用 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) 計畫，因為它是會先收集符合資料指標查詢之資料列集的計畫。
.gif "重要事項") 重要事項 |
|---|
請勿嘗試強制資料指標查詢的非資料指標計畫，反之亦然。如果您這麼做，計畫強制可能會失敗，即使資料指標查詢與非資料指標查詢相同。 |
下列類型的 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 所提交的參數化查詢搭配使用一樣。