コンパイル ロックによるブロックの問題のトラブルシューティング

この記事では、コンパイル ロックによって発生するブロックの問題をトラブルシューティングおよび解決する方法について説明します。

元の製品バージョン:  SQL Server
元の KB 番号:   263889

まとめ

このMicrosoft SQL Server、ストアド プロシージャ プランのコピーは一般に一度に 1 つのキャッシュに格納されます。 これを実行するには、コンパイル プロセスの一部をシリアル化する必要があります。この同期は、コンパイル ロックを使用して部分的に実行されます。 多くの接続が同じストアド プロシージャを同時に実行し、そのストアド プロシージャを実行する度にコンパイル ロックを取得する必要がある場合、セッション ID (SPIDs) は、オブジェクトの排他的コンパイル ロックを取得しようとしたときに互いにブロックし始める可能性があります。

ブロック出力で確認できるコンパイル ブロックの一般的な特性を次に示します。

  • waittype ブロックおよび (通常) ブロックセッションの SPID は (排他) 形式で、ストアド プロシージャのオブジェクト LCK_M_X waitresource ID OBJECT: dbid: object_id [[COMPILE]] object_id は次の形式です。

  • ブロックには waittype NULL、状態が実行可能です。 ブロックには (排 waittype LCK_M_X 他ロック) 状態が設定されています。

  • ブロッキング インシデントの期間は長い場合があります。ただし、他の SPID を長時間ブロックしている単一の SPID はありません。 ローリング ブロックがあります。 1 つのコンパイルが完了するとすぐに、別の SPID が数秒間以下のヘッド ブロッカーの役割を引き継ぐなどです。

次の情報は、この種類のブロック中 sys.dm_exec_requests のスナップショットからの情報です。

session_id   blocking_session_id   wait_type   wait_time   waitresource ---------- ------------------- --------- --------- ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102
[[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102
[[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102
[[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102
[[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102
[[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102
[[COMPILE]]

waitresource (6:834102) では、6 はデータベース ID で834102オブジェクト ID です。 このオブジェクト ID は、テーブルではなくストアド プロシージャに属します。

詳細情報

ストアド プロシージャの再コンパイルは、ストアド プロシージャまたはトリガーのコンパイル ロックの 1 つの説明です。 この場合の解決策は、再コンパイルを減らすか、または削除します。

コンパイル ロックにつながるその他のシナリオ

  1. ストアド プロシージャは完全修飾名なしで実行されます

    • ストアド プロシージャを実行するユーザーは、プロシージャの所有者ではありません。
    • ストアド プロシージャ名は、オブジェクト所有者の名前で完全修飾されません。

    たとえば、ユーザー dbo がオブジェクトと別のユーザーを所有している場合、コマンドを使用してこのストアド プロシージャを実行すると、オブジェクトが所有者修飾されていないため、オブジェクト名による最初のキャッシュ参照は失敗します。 dbo.mystoredproc Harry exec mystoredproc (という名前の別のストアド プロシージャが存在するかどうかはまだ分 Harry.mystoredproc かっていません。 したがって、SQL Serverキャッシュされたプランが正しい実行プランであるはずはない)SQL Server は、プロシージャの排他的コンパイル ロックを取得し、プロシージャをコンパイルする準備を行います dbo.mystoredproc 。 これには、オブジェクト名をオブジェクト ID に解決する方法も含まれます。 プランSQL Serverをコンパイルする前に、SQL Server はこのオブジェクト ID を使用してプロシージャ キャッシュのより正確な検索を実行し、所有者の資格がなくても以前にコンパイルされたプランを見つける必要があります。

    既存のプランが見つかった場合、キャッシュSQL Server再利用され、ストアド プロシージャは実際にはコンパイルされません。 ただし、所有者修飾がない場合、SQL Server は 2 番目のキャッシュ参照を実行し、排他的コンパイル ロックを取得してから、プログラムが既存のキャッシュ実行計画を再利用できると判断します。 ロックを取得し、ルックアップや、この時点に到達するために必要な他の作業を実行すると、ブロックにつながるコンパイル ロックの遅延が発生する可能性があります。 これは、ストアド プロシージャの所有者ではない多くのユーザーが、所有者の名前を指定せずにプロシージャを同時に実行する場合に特に当てはまる。 SPID がコンパイル ロックを待機していない場合でも、所有者修飾が不足すると、ストアド プロシージャの実行に遅延が発生し、CPU 使用率が高い場合があります。

    次の一連のイベントは、この問題が発生SQL Server拡張イベント セッションに記録されます。

    イベント名 テキスト
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss 名前によるキャッシュ参照が失敗した場合に発生しますが、あいまいなオブジェクト名がオブジェクト ID に解決され、イベントが発生した後、キャッシュに一致するプランが最終的に見つかりました sp_cache_hit

    コンパイル ロックのこの問題の解決策は、ストアド プロシージャへの参照が所有者修飾を確実に行う方法です。 (exec の代わりに mystoredproc 、exec dbo.mystoredproc .) を使用します。所有者修飾はパフォーマンス上の理由から重要ですが、追加のキャッシュ参照を防ぐために、保存されている proc をデータベース名で修飾する必要は一方で、必要ではありません。

    コンパイル ロックによって発生するブロックは、標準的なブロックのトラブルシューティング方法を使用して検出できます。

  2. ストアド プロシージャには、プレフィックスが付sp_

    ストアド プロシージャ名がプレフィックスで始まり、マスター データベースに含されていない場合は、ストアド プロシージャを所有者修飾した場合でも、実行ごとにキャッシュ ヒットの前に sp_cache_miss が表示 sp_ されます。 これは、ストアド プロシージャがシステム ストアド プロシージャSQL Server、システム ストアド プロシージャの名前解決ルールが異なっているというプレフィックスが表示 sp_ されるためです。 (優先する場所は、マスター データベース内です)。ユーザーが作成したストアド プロシージャの名前は、で始める必要があります sp_

  3. ストアド プロシージャは、別のケース (上/下) を使用して呼び出されます。

    所有者修飾プロシージャが、作成に使用されたケースとは異なるケース (大文字または小文字) を使用して実行される場合、プロシージャは CacheMiss イベントをトリガーするか、COMPILE ロックを要求できます。 最終的に、プロシージャはキャッシュされたプランを使用し、再コンパイルされません。 ただし、コンパイル ロックの要求によって、同じプロシージャを作成するために使用されたケースとは異なるケースを使用して同じプロシージャを実行しようとしている SPID が多数ある場合、チェーンのブロックが発生する場合があります。 これは、サーバーまたはデータベースで使用されている並べ替え順序や照合順序に関係なく当てはまる。 この動作の理由は、キャッシュ内のプロシージャを検索するために使用されるアルゴリズムがハッシュ値 (パフォーマンスのために) に基づいており、ケースが異なる場合はハッシュ値が変更される可能性があるからです。

    回避策は、アプリケーションがプロシージャを実行するときに使用されるケースと同じケースを使用してプロシージャを削除して作成します。 また、正しいケース (大文字または小文字) を使用して、すべてのアプリケーションからプロシージャが実行されていることを確認できます。

  4. ストアド プロシージャが Language イベントとして呼び出される

    RPC ではなく言語イベントとしてストアド プロシージャを実行する場合、SQL Server は言語イベント クエリを解析してコンパイルし、クエリが特定のプロシージャを実行しようとしていることを判断し、そのプロシージャのプランをキャッシュで検索する必要があります。 言語イベントを解析SQL Serverコンパイルする必要があるこの状況を回避するには、クエリが RPC としてSQLしてください。

    詳細については、「Books Online」の「ストアド プロシージャの作成」の「System Stored **Procedures」**セクションを参照してください

参照

OPEN 対称キー コマンドは、クエリ プランのキャッシュを防止します。