ストアド プロシージャの再コンパイルのトラブルシューティング

この記事は、以前は次の ID で公開されていました: JP243586
この資料は、アーカイブされました。これは "現状のまま" で提供され、更新されることはありません。
概要
この資料では、アプリケーションが Microsoft SQL Server を使用した場合に発生する、ストアド プロシージャの実行時の再コンパイルという特定のパフォーマンス問題を扱います。パフォーマンス問題を解決しようとしているが問題の正確な発生原因を特定できないという場合は、この先に進む前に、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。

224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング
この資料では、上の資料を参考に問題の範囲を既に絞り込んでいること、さらに特定のイベントとデータ列の詳細を含んでいる SQL Server プロファイラ トレースを既にキャプチャしていることを前提に説明します。
詳細
ユーザーがストアド プロシージャを実行したときに、SQL Server は、このプロシージャがキャッシュになければ、このプロシージャを読み込み、クエリ プランをコンパイルします。コンパイル後のプランはキャッシュに格納され、以降のストアド プロシージャの呼び出し側で再利用されます。これは、プランが無効化され、強制的に再コンパイルが行われるアクションが発生するまで行われます。次に、ストアド プロシージャ プランの再コンパイルを発生させる可能性のあるアクションを示します。
  • CREATE PROCEDURE または EXECUTE ステートメントでの WITH RECOMPILE 句の使用
  • 制約、デフォルト値、ルールの追加/削除を含む、任意の参照オブジェクトへのスキーマ変更
  • プロシージャによって参照されるテーブルに対する sp_recompile の実行
  • プロシージャあるいはプロシージャが参照する任意のオブジェクトを含む、データベースの復元 (データベースをまたがる操作を実施している場合)
  • サーバーの処理量の多さによりプランが時間の経過によってキャッシュから削除される状況
ストアド プロシージャの再コンパイルを発生させるこれらすべての理由は以前のバージョンから存在し、プランはプロシージャの実行を開始する前に再コンパイルを行っていました。SQL Server 7.0 では、ストアド プロシージャが実行時に再コンパイルを行う可能性のある、新しい動作が導入されました。この新しい動作は、オプティマイザが常にプロシージャ内の特定の各ステートメントに対して最適なプランを指定することを保証するものです。ストアド プロシージャの実行時再コンパイルは、以下の状況で発生する可能性があります。
  • ストアド プロシージャによって参照されるテーブル内の大量のデータが更新された場合
  • プロシージャにデータ定義言語 (DDL) とデータ操作言語 (DML) の操作が含まれていた場合
  • プロシージャにより、一時テーブル上で特定の操作が実行された場合
この資料では、これらの各原因について詳細に説明します。

ストアド プロシージャの再コンパイルにかかるデメリットは、ときに再コンパイルから得られるメリットを上回ることがあります。特に、プロシージャが大規模な場合には顕著です。再コンパイルが発生する際、バッチまたはプロシージャ全体が再コンパイルされることに注意してください。つまり、パフォーマンスの低下は直接、プロシージャやバッチのサイズに比例します。このトピックの詳細については、SQL Server 7.0 Books Online の「Transact-SQL に関するヒント」を参照してください。


この資料の以下の部分では、実行時のストアド プロシージャの再コンパイルの原因特定に焦点を当て、この再コンパイルの発生を防止する方法について説明します。

最適な対応方法

プロシージャを実行する場合は、ストアド プロシージャ名を "所有者限定" とすることをお勧めします。これにより、現在のユーザーが既存の実行プランをより明確かつ簡単に再利用できるようになります。たとえば、データベースの所有者 (dbo) でないユーザーが pubs データベース内の dbo 所有のストアド プロシージャ (この例では、myProc という名前) を実行する場合は、次のステートメントを使用します。
exec dbo.myProc				
次のステートメントはお勧めしません。
exec myProc				
このテクニックを使用すると、コーディングと保守の観点から、複数の所有者による他のバージョンのプロシージャを使用した場合の混乱がなくなり、SQL Server で特定のプロシージャの実行プランにさらに直接的にアクセスできるようにもなります。

所有者限定を行わないと、SQL Server によって、コンパイル コードが入力され、プロシージャで COMPILE ロックが取得されます。しかし、最終的には、新しいプランは必要ないと判断され (他の原因が存在しないことを前提とします)、修飾されていないことから、この時点ではプランの再コンパイルは発生しません。ただし、その他の手順で別途、プロシージャの COMPILE ロックが取得されると、高負荷状況でブロックによる競合が発生する可能性があります。この状況の詳細については、「サポート技術情報」 (Microsoft Knowledge Base) の「263889 SQL Blocking Due to COMPILE Locks」を参照してください。

プロシージャ呼び出しを所有者限定 (<owner>.<procedure>) とすると、コンパイル ロックを取得する必要がなくなり、競合の発生が少なくなります。

問題の特定と解決

次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。使用するシステムのパフォーマンス分析を支援するプロファイラ データのキャプチャに関する詳細が掲載されています。
224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング

プロファイラ データの表示

SQL Server プロファイラには、発生する再コンパイル数の監視に使用する SP:Recompile イベントが含まれています。実行時にストアド プロシージャが再コンパイルされるたびに、SP:Recompile イベントが発生します。
  • イベント クラス別にプロファイラ トレースをグループ化する

    1. [ファイル] メニューの [プロパティ] をクリックします。
    2. [データ列] タブで、[上へ] ボタンを使用して [EventClass] と [TextData] を [グループ] 見出しの下に移動します。最初に [イベント クラス] 見出しから移動します。[下へ] ボタンを使用して、[グループ] 見出しの下にあるそれ以外の列をすべて削除します。
    3. [実行] をクリックします。
    SP:Recompile イベントの数を確認します。

    SP:Recompile グループを展開すると、個々の発生に関する詳細を確認できます。イベントの TextData 列には、再コンパイルされたストアド プロシージャ名が示されます。複数のプロシージャで再コンパイルが発生している場合、発生数順で並べ替えられます。SP:Recompile イベントが多く、CPU の使用率が高い場合は、最大の再コンパイル数を持つプロシージャの解決に重点を置いて作業してください。特定のストアド プロシージャの 1 つのインスタンスに対する SP:Recompile イベントのシステム プロセス ID (SPID) と開始時間を記録し、次のステップを実行してください。

    SP:Recompile イベントが見当たらないにもかかわらずパフォーマンス上の問題が依然として発生する場合には、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。
    224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング
  • 再コンパイル イベントを起動したステートメントを決定する

    1. [ファイル] メニューの [プロパティ] をクリックします。
    2. [データ列] タブで、[下へ] ボタンを使用して [グループ] 見出しの下にあるその他の列をすべて削除します。
    3. [イベント] タブで、SP:Starting、SP:StmtStarting、SP:Recompile、SP:Completed 以外のすべてのイベントを削除します。SP:StmtStarting イベントをキャプチャしなかった場合は、代わりに SP:StmtCompleted を使用することができます。ただし、調査すべき情報量が 2 倍になるので、両方のイベントを含めないでください。
    4. 調査するストアド プロシージャ再コンパイルのインスタンスを既に特定している場合は、[フィルタ] タブを使用して、表示データを再コンパイルが発生した特定の SPID とタイム フレームに制限することができます。
    5. [実行] をクリックします。

    再コンパイルが発生したストアド プロシージャ ステートメントの SP:StmtStarted イベントの直後に、SP:Recompile イベントが発生します。再コンパイル イベントが終了すると、SP:StmtStarted イベントが繰り返されます。これは、新しく生成されたプランでステートメントが実行されることを示しています。

    以下の例を考えます。
    use pubsgodrop procedure RecompProc gocreate procedure RecompProc ascreate table #t (a int)select * from #tgoexec RecompProc						
    クエリ アナライザでこのコードを実行し、プロファイラ トレースで上のイベントを表示すると、次のシーケンスが表示されます。

    EventClassTextData
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP: RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    次のステートメントが、再コンパイルを発生させたステートメントであることがすぐにわかります。
    select * from #t						
    これは、SP:Recompile イベントの前後に出現しているからです。

    SP:StmtCompleted イベントだけをキャプチャし、SP:StmtStarting イベントをキャプチャしなかった場合、次に示すように、再コンパイルが発生しているステートメントの直前に SP:Recompile が示されます。

    EventClassTextData
    SP:StartingRecompProc
    SP: RecompileRecompProc
    SP: StmtCompletedselect * from #t
    SP:CompletedRecompProc


    再コンパイルを発生させた select * from #t ステートメントに対応する SP:StmtCompleted イベントの前に、SP:Recompile イベントが発生しているのが確認できます。再コンパイルに対して新しいクエリ プランを生成するまでステートメントを完了できないので、これは理にかなった動作です。この資料の以降のコード例では、すべて SP:StmtStarting イベントを使用しています。SP:StmtCompleted イベントしかキャプチャしていない場合は、前述したように、ステートメントは、SP:Recompile の後に表示されることに注意してください。

    この特定のストアド プロシージャを何度も実行する場合、SQL Server はこのプロシージャの既存のプランを再利用することに注意してください。再コンパイル イベントが確認できるのは、プロシージャの最初の実行時か、またはスクリプトを実行するたびにプロシージャを削除して作成し直した場合だけです。このような特定の場合に再コンパイルが発生する理由については、この資料の「データ定義言語 (DDL) とデータ操作言語 (DML) 操作を含むことによって発生する再コンパイル」で説明します。ここでは例を示し、コンパイルを発生させたステートメントを判別する簡単な方法について説明します。

行修正による再コンパイル

元のクエリ プランの生成以降に、ストアド プロシージャによって参照されるテーブル内で大量のデータを変更すると、SQL Server により、最新の統計データに基づいたプランを設定するようにストアド プロシージャが再コンパイルされます。一例として、次のストアド プロシージャを考えます。
drop procedure RowModifications gocreate procedure RowModifications as-- assume SomeTable exists with the same definition as #t, -- and has over 1000 rowscreate table #t (a int, b char(10))select * from #tinsert #t select * from SomeTableselect count(*) from #t  where a = 37goexec RowModificationsexec RowModifications				
RowModifications プロシージャの 2 回目の実行では、プロファイラに次のイベントが示されます。

EventClassTextData
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP: RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

: プロシージャの最初の実行で、select * from #t ステートメントに対する SP:Recompile イベントも示されます。この特定のケースで再コンパイルが発生した原因については、この資料の「データ定義言語 (DDL) とデータ操作言語 (DML) 操作を含むことによって発生する再コンパイル」で説明します。この例では、プロシージャを実行するたびに、再コンパイルが発生するという理由から、上の SP:Recompile を中心に説明します

この例では、select count(*) from #t where a = 37 によって、テーブルの作成以降に行った行数変更のために、プロシージャの再コンパイルが発生しています。行修正によって再コンパイルが発生したことは、Auto-UpdateStats イベントがあることでわかります。TextData 列には、統計情報が変更された列が示されます。

#t テーブルを作成した時点では、行数が 0 に設定されています。"select count (*)" クエリ プラン同様、元の "select * from #t" のプランをその行カウントによって作成します。ただし、"select count(*)" を実行する前に、新しい 1,000 行を #t テーブルに挿入します。大量のデータ変更があったために、オプティマイザはステートメントにとって最適なプランを選択するようにプロシージャを再コンパイルします。1,000 行の挿入は再コンパイルが発生する十分な理由と常に見なされるため、ストアド プロシージャを実行するたびに再コンパイルが発生します。

プランを再コンパイルするかどうかの判断に使用する SQL Server アルゴリズムは、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料で説明している、自動更新統計に使用するアルゴリズムと同一のものです。
195565 [INF] SQL Server 7.0 と SQL Server 2000 の Autostats の詳細な動作
上の例では、ストアド プロシージャが小規模なので、再コンパイルが発生してもパフォーマンスに大きな影響はありませんが、複数の再コンパイルを発生させる同様の処理を実行するような大規模なストアド プロシージャの場合、パフォーマンスの低下が顕著に現れます。

行修正による再コンパイルの発生を減らす方法としては、以下のものがあります。
  • sp_executesql を使用してステートメントを実行する。
    推奨されるのは、この方法です。sp_executesql ストアド プロシージャを使用して実行するステートメントは、ストアド プロシージャ プランの一部としてコンパイルされません。そのため、ステートメントを実行すると、ステートメントに対してキャッシュ内の既存のプランが使用されるのか、または、実行時に新しいプランが作成されるのかは、SQL Server によって決定されます。どちらが選択されても、呼び出し側のストアド プロシージャのプランには影響はなく、再コンパイルの必要はありません。

    EXECUTE ステートメントを使用しても同じ効果が得られますが、このステートメントを使用することはお勧めできません。EXECUTE ステートメントを使用した場合、クエリをパラメータ化できないため、sp_executesql を使用した場合のような効果は得られません

    前述した RowModifications プロシージャは、次に示すように sp_executesql を使用して記述できます。

    drop procedure RowModifications2 gocreate procedure RowModifications2 asset nocount on-- assume SomeTable exists with the same definition as #t, -- and has over 1000 rowscreate table #t (a int, b char(10))select * from #tinsert #t select * from SomeTableexec sp_executesql N'select count(*) from #t where a = @a',                    N'@a int', @a =  37goexec RowModifications2exec RowModifications2						

    RowModifications2 プロシージャの 2 回目の実行では、プロファイラに次のイベントが示されます。

    EventClassTextData
    SP:StartingRowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    RowModifications2 プロシージャに対する、SP:Recompile イベントが存在しないことに注目してください。sp_executesql 呼び出しのコンテキストに対して、SP:Starting から SP:Completed までのすべてのイベントがあり、列 a に対して Auto-UpdateStats イベントがあります。ただし、この呼び出しは、ストアド プロシージャのコンテキスト外にあるので、この場合 RowModifications2 プロシージャは再コンパイルの必要はありません。

    sp_executesql ストアド プロシージャの使用に関する詳細については、SQL Server Books Online の「sp_executesql」 (T-SQL) と「sp_executesql の使用」を参照してください。
  • サブプロシージャを使用して、再コンパイルを発生させるステートメントを実行する。
    この場合、ステートメントで依然として再コンパイルが発生する可能性がありますが、大規模な呼び出し側ストアド プロシージャを再コンパイルするのではなく、小規模なサブプロシージャだけが再コンパイルされます。
  • KEEP PLAN オプションを使用する。
    一時テーブルには、再コンパイルに関する特別なルールがありますが、場合によってはこのルールはデフォルトの再コンパイル アルゴリズムより厳しくなる可能性があります。KEEP PLAN オプションを使用すると、一時テーブルのしきい値をデフォルトのアルゴリズムに戻して緩和することができます。詳細については、この資料の「KEEP PLAN オプションを使用して再コンパイルを回避する」を参照してください。
: RowModifications プロシージャは、行修正によって再コンパイルされる非常に簡潔化されたプロシージャ例です。この例についての注意事項を以下に示します。

  • この例では一時テーブルを使用していますが、この状況は、パーマネント テーブルを参照するストアド プロシージャにも適用されます。クエリ プランの生成以降に参照テーブル内で大量のデータ変更を行った場合には、ストアド プロシージャが再コンパイルされます。再コンパイル目的に対する一時テーブルの考え方の違いについては、この資料の「KEEP PLAN オプションを使用して再コンパイルを回避する」で説明します。
  • また上の 2 つのプロシージャの初回実行時でも、一時テーブル #t からの最初の選択時に再コンパイルが発生します。この再コンパイルの発生原因については、この資料の「データ定義言語 (DDL) とデータ操作言語 (DML) 操作を含むことによって発生する再コンパイル」で説明します。
  • この例では、単純な select * from #t ステートメントではなく、select count(*) from #t ステートメントを使用しました。過度の再コンパイルを回避するために、SQL Server では、行変更による "平凡なプラン" (たとえば、テーブルからの * の選択など) の再コンパイルは考慮されません。

データ定義言語 (DDL) とデータ操作言語 (DML) 操作を含むことによって発生する再コンパイル

プロシージャまたはバッチ処理で DDL 操作を実行した場合、DDL に関係するテーブルに影響を与える最初の DML 操作に遭遇した時点で、そのプロシージャまたはバッチは再コンパイルされます。

以下のストアド プロシージャの例を考えます。
drop procedure Interleave gocreate procedure Interleave as-- DDLcreate table t1 (a int)-- DMLselect * from t1-- DDLcreate index idx_t1 on t1(a)-- DMLselect * from t1-- DDLcreate table t2 (a int)-- DMLselect * from t2goexec Interleave				
クエリ アナライザでこのコードを実行し、プロファイラ トレースで上のイベントを表示すると、次のシーケンスが表示されます。

EventClassTextData
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP: RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP: RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP: RecompileInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


この場合、ストアド プロシージャは、実行中に 3 回再コンパイルされます。オプティマイザによってこのストアド プロシージャのプランが作成された方法を考えれば、その理由が理解できます。
  1. プロシージャの初回コンパイル時には、テーブル t1 と t2 は存在しません。そのため、これらのテーブルを参照するクエリのプランは作成できません。これらテーブルは、実行時に生成する必要があります。
  2. プロシージャを最初に実行する際には、まずテーブル t1 を作成します。次にテーブル t1 からの選択を行います。ただし、テーブル t1 に対するプランは存在しません。そのため、この時点でプロシージャを再コンパイルし、SELECT ステートメントに対してプランを作成します。インデックス作成後に #t1 からの選択と同様に、t1 からの現在の選択に対するプランを生成します。この時点では依然として t2 が存在していないので、t2 からの選択に対するプランを生成することはできません。
  3. 次に、t1 上にインデックスを作成します。その後で、t1 でもう 1 つ選択を実行します。t1 には最初の再コンパイルで生成したプランがあります。ただし、プランの生成後に t1 のスキーマを変更しているので、再度プロシージャを再コンパイルして、t1 からの選択に対して新しいプランを生成する必要があります。t2 は依然として存在していないので、t からの選択に対するプランを生成することはできません。
  4. 次に、テーブル t2 を作成し、t2 からの選択を実行します。ステートメントに対するプランが存在していないので、最後にプロシージャを再コンパイルします。
これらの再コンパイルは、ストアド プロシージャを実行するたびに発生します。再コンパイルを減らすには、次に示すように、最初に DDL 操作すべてを行い、次に DML 操作を行うようにプロシージャを修正してください。
drop procedure NoInterleave gocreate procedure NoInterleave as-- All DDL firstcreate table t1 (a int)create index idx_t1 on t1(a)create table t2 (a int)-- Then DML select * from t1select * from t1select * from t2goexec NoInterleave exec NoInterleave				
NoInterleave プロシージャの最初の実行で、プロファイラ内に次のイベントが示されます。

EventClassTextData
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP: RecompileNoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


この場合、DDL ステートメントすべてをあらかじめ処理します。オプティマイザによって、このプロシージャは次のようにコンパイルされます。
  1. プロシージャの初回コンパイル時には、テーブル t1 と t2 は存在しません。そのため、これらのテーブルを参照するクエリのプランは作成できません。これらのテーブルは、実行時に生成する必要があります。
  2. 最初に、プロシージャは DDL 操作を行い、テーブル t1 と t2 を作成し、一時テーブル t1 上にインデックスを作成します。
  3. 次に、t1 からの最初の選択を行います。この SELECT ステートメントに対して使用できるプランが存在しないので、プロシージャが再コンパイルされます。オブジェクトすべてが存在するため、この時点でプロシージャ内のすべての SELECT ステートメントに対してプランが生成されます。
  4. 生成されたプランを使用して、残りのプロシージャを実行します。参照オブジェクトへの変更は行われていないので、それ以降、プロシージャの再コンパイルは不要になります。
: 2 回目およびそれ以降の実行では、既存のクエリ プランとキャッシュを使用するため、再コンパイルはまったく行われません。テーブルを作成、変更、または削除するプロシージャを修正して、すべての DDL ステートメントをプロシージャの先頭に位置づけるようにします。

特定の一時テーブル操作による再コンパイル

ストアド プロシージャ内で一時テーブルを使用すると、ストアド プロシージャは、実行のたびに再コンパイルされる場合があります。

これを回避するには、次の条件を満たすようにストアド プロシージャを変更します。
  • 一時テーブル名を含むすべてのステートメントが、同じストアド プロシージャ内で作成される一時テーブルを参照し、呼び出し側または呼び出されるストアド プロシージャ内、または、EXECUTE ステートメントまたは sp_executesql ストアド プロシージャを使用して実行される文字列内の一時テーブルを参照しないようにします。
  • 一時テーブル名を含むすべてのステートメントを、構文上、ストアド プロシージャまたはトリガ内の一時テーブルの後に指定します。
  • 一時テーブルを参照する SELECT ステートメントを持つ DECLARE CURSOR ステートメントが存在しないようにします。
  • 一時テーブル名を含むすべてのステートメントを、一時テーブルを参照する DROP TABLE ステートメントの前に配置します。

    ストアド プロシージャ内で作成される一時テーブルには、DROP TABLE ステートメントは必要ありません。これらのテーブルは、プロシージャが完了すると自動的に削除されます。
  • IF... ELSE や WHILE などのフロー制御ステートメント内に、一時テーブルを作成するステートメント (CREATE TABLE や SELECT... INTO など) が出現しないようにします。

KEEP PLAN オプションを使用して再コンパイルを回避する

ストアド プロシージャ内で一時テーブルを使用すると、クエリ オプティマイザの処理が複雑になります。テーブルの統計情報および行カウントは、ストアド プロシージャの実行時のライフタイム内で大幅に変化することもあります。一時テーブルに関係するあらゆるケースの中で最適なプランをオプティマイザが使用するように、再コンパイルをより積極的に行う特殊なアルゴリズムが作成されました。アルゴリズムを見ると、ストアド プロシージャで作成された一時テーブルが 6 回以上変更された場合、次のステートメントが一時テーブルを参照したときにこのプロシージャが再コンパイルされるのがわかります。

以下の例を考えます。
drop procedure useKeepPlan gocreate procedure useKeepPlan ascreate table #t (a int, b char(3))select * from #t-- Make greater than 6 changes to #tinsert #t values (1, 'abc')insert #t values (2, 'abc')insert #t values (3, 'abc')insert #t values (4, 'abc')insert #t values (5, 'abc')insert #t values (6, 'abc')insert #t values (7, 'abc')-- Now reference #tselect count(*) from #t --option (KEEP PLAN)goexec useKeepPlanexec useKeepPlan				
この場合、2 回目の実行でプロファイラ内に次のイベントが示されます。

EventClassTextData
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting(7 つの Insert ステートメント)
SP:StmtStartingselect count(*) from #t1
SP: RecompileuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

一時テーブル #t に行った 7 回の変更後に発生する選択に対して、プロシージャが再コンパイルされます。

このように積極的に再コンパイルを行うと、一時テーブルのデータ分布への変更がその一時テーブルを参照するステートメントの最適なクエリ プランに大きく影響する場合に有用です。ただし、頻繁に一時テーブルを変更する大規模なプロシージャの場合、それほど顕著ではありませんが再コンパイルによって全体的なパフォーマンスが低下することがあります。このような状況のために SELECT ステートメントの KEEP PLAN オプションが導入されています。

KEEP PLAN は、プロシージャ内の一時テーブルに対する 7 回以上の変更によって発生するストアド プロシージャの再コンパイルを排除し、この資料の「行修正による再コンパイル」で説明した行修正による再コンパイルの標準のアルゴリズムに戻します。KEEP PLAN は、すべての再コンパイルを回避するものではなく、単にプロシージャ内で参照される一時テーブルへの 7 回以上の変更によって発生する再コンパイルを回避するだけです。上の例で、ストアド プロシージャ内の option (KEEP PLAN) 行からコメントを削除すると、SP:Recompile イベントが生成されなくなります。

上のコードの option (KEEP PLAN) 行からコメントを削除し、このコードを実行すると、プロファイラ内に次のイベントが表示されます。

EventClassTextData
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting(7 つの Insert ステートメント)
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


SP:Recompile イベントがないことに注目してください。

特定の SET ステートメントがストアド プロシージャで実行されることによる再コンパイル

以下の 5 つの SET オプションは、デフォルトでオンに設定されています。
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
SET ステートメントを実行して、これらのオプションのいずれかをオフに設定すると、そのストアド プロシージャは実行されるたびに再コンパイルされます。その理由は、これらのオプションを変更すると、再コンパイルにつながるクエリ結果に影響を及ぼすことがあるためです。

以下のサンプル コードを考えます。
Use pubsdrop procedure test_recompilegocreate procedure test_recompile asSet ANSI_DEFAULTS OFFSelect au_lname, au_fname, au_id from authorswhere au_lname like 'L%'--Option (Keep Plan)Go				
この場合、ストアド プロシージャが実行されるたびに、SQL プロファイラ内に次のイベントが示されます。
+---------------------------------------------------+| Event Class     | Text                            | +---------------------------------------------------+| SP:Starting     | test_recompile                  | +---------------------------------------------------+| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | +---------------------------------------------------+| SP:StmtStarting | select au_lname, au_fname, au_id| +---------------------------------------------------+| SP:Recompile    | test_recompile                  | +---------------------------------------------------+| SP:StmtStarting | select au_lname, au_fname, au_id| +---------------------------------------------------+| SP:Completed    | test_recompile                  | +---------------------------------------------------+				
SET オプションを、上記の 5 つのオプションのいずれかで置き換えても、同じ結果になります。また、KEEP PLAN のオプションをここで使用しても、再コンパイルの原因は SET ステートメントに由来するので、再コンパイルを回避することはできません。

再コンパイルの回避で推奨される方法は、ストアド プロシージャで 5 つの SET ステートメントのいずれも使用しないようにすることです。追加の情報は、以下の「サポート技術情報」 (Microsoft Knowledge Base) を参照してください。
294942 [PRB] SET CONCAT_NULL_YIELDS_NULL によりストアド プロシージャが再コンパイルされることがある
ただし、(推奨されませんが) SET ステートメントを実行して、接続オプションをストアド プロシージャと同じ値に再設定すると、再コンパイルを回避できます。これは、次のように実行します。
Set ANSI_DEFAULTS OFFexec test_recompile				
SQL プロファイラのトレースに SP:Recompile イベントは表示されなくなります。

以下の表は、一般的な SET ステートメントの一覧で、SET ステートメントをストアド プロシージャで変更した場合に、再コンパイルが発生するかどうかを示しています。
SET ステートメント再コンパイルの発生の有無
Set quoted_identifier発生しない
Set arithabort発生する
Set ansi_null_dflt_on発生する
Set ansi_defaults発生する
Set ansi_warnings発生する
Set ansi_padding発生する
Set concat_null_yields_null発生する
Set numeric_roundabort発生しない
Set nocount発生しない
Set rowcount発生しない
Set xact_abort発生しない
Set implicit_transactions発生しない
Set arithignore発生しない
Set lock_timeout発生しない
Set fmtonly発生しない

参照

308737 [INF] SP:Recompile イベントで再コンパイルの原因を特定する方法

SQL Server プロファイラについては、SQL Server Books Online を参照してください。
rowcount perf bad slow poor troubleshoot recompiles
プロパティ

文書番号:243586 - 最終更新日: 12/05/2015 16:16:02 - リビジョン: 2.2

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Desktop Engine (Windows), Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbinfo KB243586
フィードバック