• [アーティクル]

ストアド プロシージャの再コンパイル

インデックスの追加や、インデックス列内のデータの変更などの操作によってデータベースを変更したときは、再コンパイルを行って、そのデータベースのテーブルへのアクセスに使用される元のクエリ プランを再度最適化する必要があります。MicrosoftSQL Server の再起動後、最初にストアド プロシージャを実行するときに、この最適化が自動的に行われます。また、ストアド プロシージャが使用する基になるテーブルが変更された場合にも最適化が行われます。ただし、ストアド プロシージャにとって有益な新しいインデックスが追加されても、SQL Server が次に再起動されてからストアド プロシージャが実行されるまでは最適化が行われません。このような状況では、次回の実行時に再コンパイルするようストアド プロシージャを設定しておくと便利です。

ストアド プロシージャの再コンパイルを強制的に行うもう 1 つの理由は、必要に応じて、ストアド プロシージャのコンパイル時に "パラメーターを見つけ出す" 動作の影響を少なくすることです。SQL Server でストアド プロシージャが実行されるとき、コンパイル時にプロシージャによって使用されるパラメーター値は、クエリ プランの生成の一部に含まれます。これらの値が、その後呼び出されるプロシージャの標準的な値を表す場合は、ストアド プロシージャのコンパイルや実行のたびに、そのクエリ プランからメリットを得ることができます。そうでない場合は、パフォーマンスが低下することがあります。

注意注意

SQL Server 2008 には、ストアド プロシージャをステートメント レベルで再コンパイルする機能が備わっています。SQL Server 2008 でストアド プロシージャを再コンパイルすると、プロシージャ全体ではなく、再コンパイルが必要なステートメントだけがコンパイルされます。そのため、SQL Server では、クエリ プランの再生成時に、再コンパイルされたステートメント内にパラメーター値が存在する場合はその値が使用されます。これらの値は、プロシージャに最初に渡した値とは異なることがあります。

ストアド プロシージャの強制再コンパイル

SQL Server では、ストアド プロシージャを強制的に再コンパイルするために、次の 3 つの方法が用意されています。

  • sp_recompile システム ストアド プロシージャを使用すると、次回実行時にストアド プロシージャが強制的に再コンパイルされます。そのために、プロシージャ キャッシュから既存のプランを削除して、プロシージャを次回実行するときに新しいプランが作成されるようにします。

  • 定義内で WITH RECOMPILE オプションを指定するストアド プロシージャを作成すると、SQL Server ではこのストアド プロシージャのプランがキャッシュされずに、ストアド プロシージャが実行されるたびに再コンパイルされます。ストアド プロシージャを実行するたびに値が大きく異なり、結果的に毎回異なる実行プランが作成されるパラメーターをストアド プロシージャが受け取る場合は、WITH RECOMPILE オプションを使用します。このオプションを使用することはめったにありません。このオプションを使用すると、実行のたびにストアド プロシージャを再コンパイルすることになるので実行速度が遅くなります。

    ストアド プロシージャ全体ではなく、ストアド プロシージャ内の個々のクエリのみを再コンパイルする場合、再コンパイルする各クエリ内で RECOMPILE クエリ ヒントを指定します。この動作は、上記の SQL Server のステートメント レベルの再コンパイルの動作と同じような働きをしますが、ストアド プロシージャが現在のパラメーター値を使用する以外に、ステートメントのコンパイル時に RECOMPILE クエリ ヒントでストアド プロシージャ内のローカル変数の値も使用されます。ストアド プロシージャに属するクエリのサブセットのみで、一般的でない値または一時的な値が使用されているときにこのオプションを使用します。詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。

  • ストアド プロシージャを実行するときに WITH RECOMPILE オプションを指定することで、ストアド プロシージャを強制的に再コンパイルできます。このオプションは、一般的ではないパラメーターを指定する場合や、ストアド プロシージャの作成後にデータが大幅に変更された場合にだけ使用します。

    注意注意

    ストアド プロシージャが参照するオブジェクトが削除されているかまたはそのオブジェクト名が変更されている場合にストアド プロシージャを実行すると、エラーが返されます。ただし、ストアド プロシージャ内で参照されているオブジェクトが、同名のオブジェクトと置き換えられている場合は、再作成しなくても、そのストアド プロシージャは動作します。

次回の実行時にストアド プロシージャを再コンパイルするには