次の方法で共有

  • [アーティクル]

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

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

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

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

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

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

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

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

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

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

    注意注

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

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