共用方式為


在含有資料指標的查詢上使用 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 XMLShowplan 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 XMLRPC: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 所提交的參數化查詢搭配使用一樣。