次の方法で共有

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

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

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

まとめ

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

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

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

  • ブロックには NULL waittype があり、状態は実行可能です。 ブロックされたセッションには、 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 は、テーブルではなくストアド プロシージャに属します。

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

次のシナリオでは、ストアド プロシージャまたはトリガーに対する排他コンパイル ロックの原因について説明します。

ストアド プロシージャが完全修飾名なしで実行される

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

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

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

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

Event Name Text
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を使用します)。所有者修飾はパフォーマンス上の理由から重要ですが、追加のキャッシュ参照を防ぐために、ストアド プロシージャをデータベース名で修飾する必要はありません。

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

ストアド プロシージャが頻繁に再コンパイルされる

再コンパイルは、ストアド プロシージャまたはトリガーのコンパイル ロックの 1 つの説明です。 ストアド プロシージャを再コンパイルする方法には、 EXECUTE... WITH RECOMPILE、 CREATE PROCEDURE ...WITH RECOMPILE、または sp_recompileの使用が含まれます。 詳細については、「ストアド プロシージャの再コンパイル」を参照してください。 この場合の解決策は、再コンパイルを減らすか排除することです。

ストアド プロシージャのプレフィックスは sp_** です。

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

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

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

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

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

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

ストアド プロシージャまたはsp_executesqlで 8 KB を超える文字列パラメーターを使用する

ストアド プロシージャまたは sp_executesql を呼び出し、8 KB を超える文字列パラメーターを渡すと、SQL Server はバイナリ ラージ オブジェクト (BLOB) データ型を使用してパラメーターを格納します。 その結果、この実行のクエリ プランはプラン キャッシュに保持されません。 そのため、ストアド プロシージャまたは sp_executesql を実行するたびに、新しいプランをコンパイルするためにコンパイル ロックを取得する必要があります。 このプランは、実行が完了すると破棄されます。 詳細については、「 Execution プランのキャッシュと再利用 8 KB を超える文字列リテラルに関するメモを参照してください。 このシナリオでコンパイル ロックを回避するには、パラメーターのサイズを 8 KB 未満に減らします。

関連情報

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