カーソルを使用したクエリでの 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 |
可 |
可 |
適用なし |
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) プランは、カーソル クエリに一致する行のセットを最初に収集するプランなので認識できます。
.gif "重要な注意事項") 重要 |
|---|
カーソルを使用しないプランをカーソル クエリに適用したり、カーソルを使用するプランをカーソル不使用のクエリに適用しないでください。また、これを行うと、カーソル クエリとカーソルを使用しないクエリが同じであったとしても、プラン適用に失敗する場合があります。 |
カーソル プランを記述する、次の種類の 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) 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 カーソルを使用して 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
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>'')'
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 を使用して送信されるパラメータ化クエリと一致するだけでなく、プラン ガイドと正常に一致する必要があります。