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

  • [アーティクル]

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

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

概要

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

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

  • waittype ブロックされたセッションと (通常は) ブロッキング セッションの SPID は LCK_M_X (排他的) であり waitresource 、 は 形式 OBJECT: dbid: object_id [[COMPILE]]です。ここで object_id 、 はストアド プロシージャのオブジェクト ID です。

  • ブロックには waittype NULL があり、状態は実行可能です。 ブロックユーザーには (排他ロック) があり waittypeLCK_M_X 、状態はスリープ状態です。

  • ブロッキング インシデントの期間は長い場合がありますが、他の SPID を長時間ブロックしている SPID は 1 つもありません。 ローリング ブロッキングがあります。 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のキャッシュされたプランdbo.mystoredprocが正しく実行されることを確認できません。) SQL Serverプロシージャの排他的コンパイル ロックを取得し、プロシージャをコンパイルするための準備を行います。 これには、オブジェクト名をオブジェクト ID に解決することが含まれます。 SQL Serverプランをコンパイルする前に、SQL Serverこのオブジェクト ID を使用してプロシージャ キャッシュのより正確な検索を実行し、所有者の修飾がなくても以前にコンパイルされたプランを見つけることができます。

    既存のプランが見つかった場合、SQL Serverはキャッシュされたプランを再利用し、実際にはストアド プロシージャをコンパイルしません。 ただし、所有者修飾がないと、プログラムが既存のキャッシュされた実行プランを再利用できることを判断する前に、2 つ目のキャッシュ参照を実行し、排他コンパイル ロックを取得するSQL Serverが強制されます。 ロックを取得し、この時点に到達するために必要なルックアップやその他の作業を実行すると、ブロックにつながるコンパイル ロックの遅延が発生する可能性があります。 これは、ストアド プロシージャの所有者ではない多くのユーザーが、所有者の名前を指定せずにプロシージャを同時に実行する場合に特に当てはまります。 コンパイル ロックを待機している 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。所有者修飾はパフォーマンス上の理由から重要ですが、追加のキャッシュ参照を防ぐために、格納されているプロシージャをデータベース名で修飾する必要はありません。

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

  2. ストアド プロシージャのプレフィックスに sp_

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

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

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

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

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

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

    詳細については、オンライン ブックの 「ストアド プロシージャの 作成」の「システム ストアド プロシージャ」セクションを参照してください。

関連情報

OPEN SYMMETRIC KEY コマンドを使用すると、クエリ プランのキャッシュが禁止されます