カーソルを使用したクエリでの USE PLAN クエリヒントの使用

[アーティクル]
12/16/2008

USE PLAN クエリヒントは、カーソル要求を指定するクエリで使用できます。次の表は、API サーバーカーソルの USE PLAN、Transact-SQL 拡張構文を使用する Transact-SQL カーソル、および SQL 92 構文を使用する Transact-SQL カーソルでサポートされているカーソルスクロールオプションの組み合わせを示しています。

スクロールオプション (API サーバーカーソルの @scrollopt 値)	API サーバーカーソルでサポートされている USE PLAN	Transact-SQL 拡張構文を使用する Transact-SQL カーソルでサポートされている USE PLAN	SQL 92 構文を使用する Transact-SQL カーソルでサポートされている USE PLAN
STATIC	可	可	適用なし
DYNAMIC	不可	不可	適用なし
KEYSET	不可	不可	適用なし
FORWARD_ONLY	不可	不可	適用なし
FAST_FORWARD	可	可	適用なし
FORWARD_ONLY STATIC	適用なし	可	適用なし
INSENSITIVE	適用なし	適用なし	可

カーソルを使用せずに送信されるクエリには単一のクエリプランが関連付けられていますが、カーソルを使用するクエリには 2 つのプランが関連付けられています。これらのプランの種類は、OPEN、FETCH、または REFRESH のいずれかとなります。プランの種類は、カーソルの種類によって決まります。

カーソルに対する 2 つのプランのうちの 1 つは入力クエリから直接生成されます。もう一方のプランは自動的に生成されます。これらのプランはそれぞれ、入力クエリプラン、自動生成プランと呼ばれます。FAST_FORWARD カーソルおよび STATIC (INSENSITIVE) カーソルに対して生成されるプランを次の表に示します。

カーソルの種類	OPEN カーソルプラン	FETCH カーソルプラン	REFRESH カーソルプラン
FAST_FORWARD	適用なし	入力クエリ	自動生成
STATIC	入力クエリ	自動生成	適用なし

カーソルクエリの XML クエリプランは両方のプランを含んだ単一の XML ドキュメントとして表示されることがあります。これらのプランは 2 部構成プランと呼ばれます。

カーソルのプランは、2 つの別個のプランとして表示される場合もあります。たとえば、STATIC API カーソルクエリプラン、または Transact-SQL カーソルクエリプランの SQL Server Profiler トレースでは、2 つの異なる Showplan XML For Query Compile イベントが生成されているのを確認できます。この場合のプラン適用に関して重要なのは入力クエリ (OPEN) プランだけです。USE PLAN ヒントでは入力クエリプランを使用する必要があります。単純な生成 (FETCH) プランも生成されます。しかし、これはプランを適用する際には必要ではなく、許可もされません。入力クエリ (OPEN) プランは、カーソルクエリに一致する行のセットを最初に収集するプランなので認識できます。

重要 :
カーソルを使用しないプランをカーソルクエリに適用したり、カーソルを使用するプランをカーソル不使用のクエリに適用しないでください。また、これを行うと、カーソルクエリとカーソルを使用しないクエリが同じであったとしても、プラン適用に失敗する場合があります。

カーソルプランを記述する、次の種類の XML クエリプランの出力を使用すると、USE PLAN を使用して特定の種類のカーソルにプランを適用できます。

カーソルに対する 2 部構成プラン
カーソルに対する 1 つの部分からなる入力クエリプラン

適用するカーソルプランは、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 サーバーカーソルの使用状況の監視

DB Library、ODBC、ADO、および OLEDB アプリケーションは、API サーバーカーソルを使用して、頻繁に SQL Server と対話処理を行います。これらのインターフェイスのいずれかを使用して構築されたアプリケーションが実行されている場合に SQL Server Profiler の RPC:Starting イベントを調べることにより、API サーバーカーソルストアドプロシージャに送信される呼び出しを確認できます。

カーソルを使用したクエリにプランを適用する例

この例では、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

上記の sp_cursorprepexec ステートメントへの引数として表示されている、クエリの入力クエリプランを含んでいる Showplan XML トレースイベントを右クリックし、[イベントデータの抽出] をクリックして、クエリのプランを収集します。イベントデータ (XML プラン表示) を CursorPlan.SQLPlan というファイル名でデスクトップに保存します。CursorPlan.SQLPlan ファイルを CursorPlan.txt にコピーします。SQL Server Management Studio のエディタウィンドウで CursorPlan.txt を開きます。後にかかる時間を短縮するため、[検索と置換] を使用して、プラン内の単一引用符 (') をそれぞれ 4 つの単一引用符 ('''') で置き換えます。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 で Showplan XML イベントを使用して、そのクエリの XML 実行プランを収集します。

プランの Showplan XML イベントをクリックします。そのプランがプランガイドで適用したプランであることを確認してください。

パラメータ化カーソルクエリ

プランガイドを作成する API サーバーカーソルクエリがパラメータ化されている場合、SQL Server Profiler の RPC:Starting イベントに表示されるステートメント文字列とパラメータ定義文字列をプランガイド定義に含めてください。また、パラメータ定義文字列は、sp_executesql を使用して送信されるパラメータ化クエリと一致するだけでなく、プランガイドと正常に一致する必要があります。

参照

次の方法で共有

カーソルを使用したクエリでの USE PLAN クエリヒントの使用

アプリケーションによる API サーバーカーソルの使用状況の監視

カーソルを使用したクエリにプランを適用する例

手順 1: プランを収集する

手順 2: プランガイドを作成しプランを適用する

手順 3: クエリを実行しプランガイドがクエリに適用されていることを確認する

パラメータ化カーソルクエリ

参照

概念

その他の技術情報

ヘルプおよび情報

その他のリソース

次の方法で共有

カーソルを使用したクエリでの USE PLAN クエリ ヒントの使用

アプリケーションによる API サーバー カーソルの使用状況の監視

カーソルを使用したクエリにプランを適用する例

手順 1: プランを収集する

手順 2: プラン ガイドを作成しプランを適用する

手順 3: クエリを実行しプラン ガイドがクエリに適用されていることを確認する

パラメータ化カーソル クエリ

参照

概念

その他の技術情報

ヘルプおよび情報

その他のリソース

カーソルを使用したクエリでの USE PLAN クエリヒントの使用

アプリケーションによる API サーバーカーソルの使用状況の監視

手順 2: プランガイドを作成しプランを適用する

手順 3: クエリを実行しプランガイドがクエリに適用されていることを確認する

パラメータ化カーソルクエリ