SQL Server でラッチの競合を診断および解決する

このガイドでは、ある種のワークロードで高コンカレンシー システム上の SQL Server アプリケーションを実行したときに見られるラッチの競合の問題を特定し、解決する方法について説明します。

サーバー上の CPU コアの数が増え続けると、それに関連するコンカレンシーの上昇により、データベース エンジン内で順次アクセスする必要がある競合ポイントがデータ構造内に発生する可能性があります。 これは、高スループットで高コンカレンシーのトランザクション処理 (OLTP) ワークロードに特に当てはまります。 これらの課題に取り組むためのツール、手法、方法、およびそれらを完全に回避するのに役立つ可能性のあるアプリケーションの設計に従うことができるプラクティスがいくつかあります。 この記事では、スピンロックを使用してこれらのデータ構造へのアクセスをシリアル化するデータ構造に対する特定の種類の競合について説明します。

SQL Server のラッチの競合とは

ラッチとは、インデックス、データ ページ、および B ツリー内の非リーフ ページのような内部構造など、メモリ内の構造の整合性を保証するために SQL Server エンジンによって使用される、軽量の同期プリミティブです。 SQL Server では、バッファー プール内のページを保護するためにはバッファー ラッチが使用され、バッファー プールにまだ読み込まれていないページを保護するためには I/O ラッチが使用されます。 SQL Server バッファー プール内のページのデータの書き込みまたは読み取りが行われるときは常に、ワーカー スレッドにおいて最初にそのページのバッファー ラッチを取得する必要があります。 排他ラッチ (PAGELATCH_EX) や共有ラッチ (PAGELATCH_SH) など、バッファー プール内のページにアクセスするために使用できるバッファー ラッチの種類はさまざまです。 SQL Server がバッファー プールにまだ存在しないページにアクセスしようとすると、非同期 I/O がポストされ、ページがバッファー プールに読み込まれます。 SQL Server が I/O サブシステムの応答を待機する必要がある場合は、要求の種類に応じて排他 (PAGEIOLATCH_EX) または共有 (PAGEIOLATCH_SH) I/O ラッチを待機します。これは、互換性のないラッチを使用して別のワーカー スレッドが同じページをバッファー プールに読み込むのを防ぐために行われます。 ラッチは、バッファー プール ページ以外の内部メモリ構造へのアクセスを保護するためにも使用されます。これらは、非バッファー ラッチと呼ばれます。

ページ ラッチでの競合は、マルチ CPU システムで発生する最も一般的なシナリオであるため、この記事の大部分ではこれらに焦点を当てています。

ラッチの競合は、複数のスレッドが同じメモリ内構造に対して互換性のないラッチを同時に取得しようとすると発生します。 ラッチは内部的な制御メカニズムであるため、それらを使用するタイミングは SQL エンジンによって自動的に決定されます。 ラッチの動作は決定論的であるため、スキーマの設計を含むアプリケーションの決定が、この動作に影響することがあります。 この記事の目的は、次の情報を提供することです。

• SQL Server によるラッチの使用方法に関する背景情報。
• ラッチの競合を調査するために使用されるツール。
• 観測されている競合の量が問題であるかどうかを判断する方法。

いくつかの一般的なシナリオと、競合を軽減するためにそれらを処理する最善の方法について説明します。

SQL Server によるラッチの使用方法

SQL Server のページは 8 KB で、複数の行を格納できます。 コンカレンシーとパフォーマンスを向上させるため、論理トランザクションの間保持されるロックとは異なり、バッファー ラッチが保持されるのはページに対する物理操作の間だけです。

ラッチが SQL エンジンの内部に存在し、メモリの整合性を提供するために使用されるのに対し、ロックは論理的なトランザクションの整合性を提供するために SQL Server によって使用されます。 次の表は、ラッチとロックを比較したものです。

SQL Server のラッチ モードと互換性

ある程度のラッチの競合は、SQL Server エンジンの動作の通常の部分として予想されます。 コンカレンシーの高いシステムでは、互換性が異なる複数の同時実行ラッチ要求が発生することは避けられません。 SQL Server の場合、未処理のラッチ要求が完了するまで、互換性のないラッチ要求をキューで待機させることによって、ラッチの互換性が適用されます。

ラッチは、アクセスのレベルに関連する 5 つの異なるモードのいずれかで取得されます。 SQL Server のラッチ モードは、次のようにまとめることができます。

• KP: ラッチを保持します。 参照される構造体を破棄できないようにします。 スレッドでバッファーの構造を確認する必要がある場合に使用されます。 KP ラッチは破棄 (DT) ラッチを除くすべてのラッチと互換性があるため、KP ラッチは 軽量と見なされます。つまり、使用した場合のパフォーマンスへの影響は最小限です。 KP ラッチは DT ラッチと互換性がないため、他のスレッドが参照される構造体を破棄するのを防ぎます。 たとえば、KP ラッチを使用すると、それによって参照される構造体が、レイジー ライター プロセスによって破棄されなくなります。 SQL Server バッファー ページ管理でレイジー ライター プロセスを使用する方法の詳細については、「 データベース エンジンでのページの書き込み」を参照してください。

• SH: 共有ラッチ。 参照される構造体を読み取るために必要です (データ ページの読み取りなど)。 共有ラッチの下では、複数のスレッドからリソースに同時にアクセスして読み取りを行うことができます。

• UP: ラッチを更新します。 SH (共有ラッチ) および KP と互換性がありますが、他に互換性がないため、EX ラッチを参照先の構造体に書き込むことはありません。

• EX: 排他ラッチ。 他のスレッドが参照される構造体への書き込みまたは参照先構造体からの読み取りをブロックします。 使用例の 1 つは、破損ページ保護のためにページの内容を変更する場合です。

• DT: 破棄ラッチ。 参照される構造体の内容を破棄する前に取得する必要があります。 たとえば、他のスレッドで使用できる空きバッファーのリストに追加する前に、クリーン ページを解放するため、レイジー ライター プロセスで DT ラッチを取得する必要があります。

ラッチ モードにはさまざまなレベルの互換性があります。たとえば、共有ラッチ (SH) は更新プログラム (UP) またはキープ (KP) ラッチと互換性がありますが、破棄ラッチ (DT) と互換性がありません。 ラッチに互換性がある限り、同じ構造に対して複数のラッチを同時に取得できます。 スレッドが互換性のないモードで保持されているラッチを取得しようとすると、リソースが使用可能であることを示すシグナルを待機するためにキューに配置されます。 SOS_Task 型のスピンロックは、キューに対してシリアル化されたアクセスを適用することにより、待機キューを保護するために使用されます。 項目をキューに追加するには、このスピンロックを取得する必要があります。 また、互換性のないラッチが解放されたときも、SOS_Task スピンロックによってキュー内のスレッドに通知されるため、待機中のスレッドは互換性のあるラッチを取得して操作を続行できます。 待機キューは、ラッチ要求が解放されると、先入れ先出し (FIFO) ベースで処理されます。 ラッチがこの FIFO システムに従うことにより、公平性が保証され、スレッドの枯渇が防止されます。

ラッチ モードの互換性を次の表に示します (はい は互換性を示し、 いいえは 非互換性を示します)。

SQL Server のスーパーラッチとサブラッチ

NUMA ベースの複数ソケット/マルチコア システムの存在が増える中、SQL Server 2005 では、32 以上の論理プロセッサを搭載したシステムでのみ有効なスーパーラッチ (サブラッチとも呼ばれます) が導入されました。 Superlatches は、高度に同時実行される OLTP ワークロードにおける特定の使用パターンに対する SQL エンジンの効率を向上させます。たとえば、特定のページに読み取り専用の共有 (SH) アクセスのパターンがあるが、ほとんど書き込まれでない場合などです。 このようなアクセス パターンを持つページの例として、B ツリー (つまりインデックス) ルート ページがあります。SQL エンジンでは、B ツリーの任意のレベルでページ分割が発生したときに、共有ラッチをルート ページに保持する必要があります。 挿入負荷が高くコンカレンシーの高い OLTP ワークロードでは、ページ分割の数がスループットに合わせて大きく増加し、パフォーマンスが低下する可能性があります。 スーパーラッチを使用すると、複数の同時に実行されるワーカー スレッドで SH ラッチが必要な共有ページにアクセスするためのパフォーマンスを向上させることができます。 これを実現するために、SQL Server エンジンは、このようなページのラッチをスーパーラッチに動的に昇格させます。 スーパーラッチは、1 つのラッチを 1 つのサブラッチ構造の配列 (CPU コアごとにパーティションごとに 1 つのサブラッチ) に分割します。これによって、メイン ラッチはプロキシ リダイレクターになり、読み取り専用ラッチではグローバル状態の同期は必要ありません。 その際、ワーカーは常に特定の CPU に割り当てられ、ローカル スケジューラに割り当てられた共有 (SH) サブラッチのみを取得する必要があります。

グローバルな状態を同期する必要がなくなると、ローカルな NUMA メモリにだけアクセスすればよく、パフォーマンスが大幅に向上するので、パーティション分割されていない共有ラッチより、共有 SuperLatch などの互換性のあるラッチの取得で使用されるリソースが減り、ホット ページへのアクセスのスケーリングが向上します。 逆に、排他的なスーパーラッチ (EX) の取得は、SQL がすべてのサブラッチに信号を送信する必要があるため、EX 通常のラッチを取得するよりもコストがかかります。 Superlatch において大量の EX アクセスのパターンが使用されていることが観察された場合は、ページがバッファー プールから破棄された後、SQL エンジンでそれをレベル下げできます。 次の図は、通常のラッチとパーティション分割されたスーパーラッチを示しています。

パフォーマンス モニターの SQL Server:Latches オブジェクトと関連カウンターを使用して、スーパーラッチの数、1 秒あたりのスーパーラッチ昇格、1 秒あたりのスーパーラッチ降格の数など、スーパーラッチに関する情報を収集します。 SQL Server:Latches オブジェクトおよび関連するカウンターの詳細については、「SQL Server、Latches オブジェクト」を参照してください。

ラッチ待機の種類

累積的な待機の情報は SQL Server によって追跡され、動的管理ビュー (DMW) sys.dm_os_wait_stats を使用してアクセスできます。 SQL Server では、対応する wait_type に sys.dm_os_wait_stats DMV で定義された、3 つのラッチ待機種類を使用しています。

• バッファー (BUF) ラッチ: ユーザー オブジェクトのインデックスとデータ ページの整合性を保証するために使用されます。 また、SQL Server がシステム オブジェクトに使用するデータ ページへのアクセスを保護するためにも使用されます。 たとえば、割り当てを管理するページは、バッファー ラッチによって保護されます。 これには、ページ空き領域 (PFS)、グローバル割り当てマップ (GAM)、共有グローバル割り当てマップ (SGAM)、およびインデックス割り当てマップ (IAM) ページが含まれます。 バッファー ラッチは、sys.dm_os_wait_stats で PAGELATCH_* の wait_type と共に報告されます。

• 非バッファー (Non-BUF) ラッチ: バッファー プール ページ以外のメモリ内構造体の整合性を保証するために使用されます。 非バッファー ラッチに対するすべての待機は、wait_type の LATCH_* として報告されます。

• IO ラッチ: バッファー ラッチのサブセットであり、バッファー ラッチによって保護される同じ構造が I/O 操作でバッファー プールに読み込まれる必要があるときに、これらの構造の整合性を保証します。 IO ラッチを使用すると、別のスレッドによって、互換性のないラッチによって同じページがバッファー プールに読み込まれることが防がれます。 PAGEIOLATCH_* の wait_type に関連付けられています。

  Note

  重大な PAGEIOLATCH 待機が表示された場合は、SQL Server が I/O サブシステムで待機していることを意味します。 一定量の PAGEIOLATCH 待機が予想され、通常の動作ですが、平均 PAGEIOLATCH 待機時間が一貫して 10 ミリ秒 (ミリ秒) を超える場合は、I/O サブシステムに負荷がかかっている理由を調査する必要があります。

sys.dm_os_wait_stats DMV を調べていて、非バッファー ラッチが検出される場合は、sys.dm_os_latch_stats を調べて、非バッファー ラッチの累積待機情報の詳細な内訳を取得する必要があります。 すべてのバッファー ラッチ待機は BUFFER ラッチ クラスに分類され、残りは非バッファー ラッチを分類するために使用されます。

SQL Server のラッチの競合の現象と原因

ビジー状態の高コンカレンシー システムにおいては、SQL Server のラッチおよび他の制御メカニズムによって頻繁にアクセスされ、保護されている構造で、アクティブな競合が見られるのが普通です。 ページのラッチの取得に関連する競合と待機時間がリソース (CPU) 使用率を減らすのに十分であり、スループットを妨げる場合は、問題があると見なされます。

ラッチの競合の例

次の図で、青い線は 1 秒あたりのトランザクション数によって測定された SQL Server のスループットを表し、黒い線はページ ラッチの平均待機時間を表します。 この場合、各トランザクションでは、先頭の値を順番に増やしながら、クラスター化インデックスへの INSERT が実行されます (bigint データ型の IDENTITY 列の設定時など)。 CPU の数が 32 に増加すると、全体的なスループットが低下し、ページ ラッチの待機時間が黒線で示されているように約 48 ミリ秒に増加したことが明らかになります。 スループットとページ ラッチ待機時間の間にあるこのような逆相関関係は、簡単に診断できる一般的なシナリオです。

ラッチの競合が解決された場合のパフォーマンス

次の図に示すように、ページ ラッチ待機が SQL Server のボトルネックになることはなくなり、スループットは 1 秒あたりのトランザクション数で測定して 300% 増加します。 これは、後の「計算列でハッシュ パーティション分割を使用する」で説明する手法によって実現されました。 このパフォーマンス向上は、コア数が多く、コンカレンシー レベルの高いシステムを対象とするものです。

ラッチの競合に影響を与える要因

OLTP 環境でパフォーマンスを妨げるラッチの競合は、通常、次の 1 つ以上の要因に関連する高コンカレンシーに起因します。

SQL Server ラッチの競合を解析する

このセクションでは、SQL Server ラッチの競合を診断して、環境に問題があるかどうかを判断するための情報を提供します。

ラッチの競合を診断するためのツールと方法

ラッチの競合の診断に使用される主なツールは次のとおりです。

• SQL Server 内の CPU 使用率と待機時間を監視し、CPU 使用率とラッチ待機時間の間に関係があるかどうかを確立するパフォーマンス モニター。

• 問題の原因になっている特定のラッチの種類と、影響を受けるリソースを確認するために使用できる SQL Server DMV。

• 場合によっては、SQL Server プロセスのメモリ ダンプを取得し、Windows デバッグ ツールで分析する必要があります。

ラッチの競合を診断するための技術的なプロセスは、次の手順にまとめられます。

1. ラッチに関連している可能性のある競合が発生しているかどうかを確認します。

2. 「付録: SQL Server ラッチ競合スクリプト」に記載されている DMV ビューを使用して、影響を受けるラッチとリソースの種類を確認します。

3. 「さまざまなテーブル パターンに対するラッチの競合の処理」で説明されている手法のいずれかを使用して、競合を軽減します。

ラッチの競合のインジケーター

前に説明したように、ラッチの競合は、ページ ラッチの取得に関連する競合と待機時間が原因で、使用できる CPU リソースがあるのにスループットが高くならない場合にのみ問題になります。 許容される競合の量を決定するには、パフォーマンスとスループットの要件と、利用できる I/O および CPU リソースを併せて考慮する、包括的なアプローチが必要です。 このセクションでは、ワークロードに対するラッチ競合の影響を次のように判断する手順について説明します。

1. 代表的なテストの間の全体的な待機時間を測定します。
2. それらを順番にランク付けします。
3. ラッチに関連する待機時間の割合を決定します。

累積待機情報は、sys.dm_os_wait_stats DMV から入手できます。 最も一般的なラッチの競合の種類はバッファー ラッチ競合であり、wait_type が PAGELATCH_* であるラッチ待機時間の増加として観察されます。 バッファー以外のラッチは、LATCH* の待機タイプに分類されます。 次の図に示すように、まず、sys.dm_os_wait_stats DMV を使用してシステムの累積待機時間を取得し、バッファーまたは非バッファー ラッチによる全体的な待機時間の割合を確認する必要があります。 非バッファー ラッチが発生している場合は、sys.dm_os_latch_stats DMV も調べる必要があります。

次の図は、sys.dm_os_wait_stats および sys.dm_os_latch_stats DMV によって返される情報の関係を示したものです。

sys.dm_os_wait_stats DMV の詳細については、SQL Server ヘルプのsys.dm_os_wait_statsを参照してください。

sys.dm_os_latch_stats DMV の詳細については、SQL Server のヘルプのsys.dm_os_latch_statsを参照してください。

ラッチ待機時間の次のメジャーは、過度なラッチの競合がアプリケーションのパフォーマンスに影響を与えていることを示すインジケーターです。

• スループットと共にページ ラッチの平均待機時間が一貫して上昇し続ける: スループットと共にページ ラッチの平均待機時間が一貫して上昇し、バッファー ラッチの平均待機時間も予想されるディスク応答時間を上回る場合は、sys.dm_os_waiting_tasks DMV を使用して現在の待機中のタスクを調べる必要があります。 平均は、分離して分析すると誤解を招く可能性があるため、可能な場合は、ワークロードの特性を理解するために、システムをライブで確認することが重要です。 特に、ページ上で PAGELATCH_EX 要求や PAGELATCH_SH 要求に長い待ち時間が発生しているかどうかを確認します。 次の手順に従って、スループットに伴う平均ページ ラッチ待機時間の一貫した上昇を診断します。

  • 「sys.dm_os_waiting_tasks のクエリを実行してセッション ID の順に並べ替える」または「一定期間の待機時間を計算する」のサンプル スクリプトを使用して、現在の待機中のタスクを調べ、ラッチの平均待機時間を測定します。

  • 「バッファー記述子のクエリを実行して、ラッチ競合の原因となっているオブジェクトを特定する」のサンプル スクリプトを使用して、競合が発生しているインデックスと基になるテーブルを特定します。

  • パフォーマンス モニターのカウンター MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time を使用して、または sys.dm_os_wait_stats DMV を実行することで、平均ページ ラッチ待機時間を測定します。

  Note

  特定の待機の種類 (wt_:type としてsys.dm_os_wait_stats返される) の平均待機時間を計算するには、合計待機時間 (wait_time_ms として返されます) を待機タスクの数 (waiting_tasks_countとして返されます) で除算します。

• ピーク負荷時にラッチ待機の種類に費やされた合計待機時間の割合: アプリケーションの負荷に合わせて全体的な待機時間に対する平均ラッチ待機時間が増加した場合、ラッチの競合がパフォーマンスに影響を与える可能性があるため、調査する必要があります。

  SQL Server の Wait Statistics オブジェクト パフォーマンス カウンターを使用して、ページ ラッチ待機とページラッチ以外の待機を測定します。 その後、これらのパフォーマンス カウンターの値を、CPU、I/O、メモリ、ネットワーク スループットに関連付けられているパフォーマンス カウンターと比較します。 たとえば、トランザクション数/秒とバッチ要求数/秒は、リソース使用率の 2 つの適切な測定値です。

  Note

  各待機の種類の相対的な待機時間は、 DMV には含まれません。この DMW は、SQL Server のインスタンスが最後に開始されたとき、または累積待機統計がを使用してリセットされてからの待機時間を測定するためです。 各待機の種類の相対的な待機時間を計算するには、ピーク負荷の前と後に sys.dm_os_wait_stats のスナップショットを取得して、その差を計算します。 「一定期間の待機時間を計算する」のサンプル スクリプトを、この目的に使用できます。

  非運用環境のみの場合は、次のコマンドを使用して sys.dm_os_wait_stats DMV をクリアします。

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  同様のコマンドを実行して、sys.dm_os_latch_stats DMV をクリアできます。

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• スループットは増加せず、場合によっては、アプリケーションの負荷が増加し、SQL Server で使用できる CPU の数が増えるにつれて減少します。これは 、ラッチ競合の例に示されています。

• アプリケーションのワークロードが増加しても CPU 使用率は増加しません。アプリケーションのスループットに基づくコンカレンシーの増加に伴ってシステム上の CPU 使用率が増加しない場合、これは、SQL Server がラッチの競合の何らかの現象を待機していることを示す指標です。

根本原因を分析します。 上記の各条件が正しい場合でも、パフォーマンスの問題の根本原因が他の場所にある可能性があります。 実際、ほとんどの場合、最適でない CPU 使用率は、ロックのブロック、I/O 関連の待機、ネットワーク関連の問題など、他の種類の待機によって引き起こされます。 経験則として、より詳細な分析を続行する前に、全体的な待機時間の最大割合を表すリソース待機を解決することをお勧めします。

現在の待機バッファー ラッチを分析する

バッファー ラッチの競合は、sys.dm_os_wait_stats DMV に、PAGELATCH_* または PAGEIOLATCH_* のいずれかの wait_type のラッチ待機時間が増える場合に発生します。 システムをリアルタイムで確認するには、システムで次のクエリを実行し、sys.dm_os_wait_stats、sys.dm_exec_sessions、sys.dm_exec_requests の各 DMV を結合します。 その結果を使用して、サーバーで実行されているセッションの現在の待機の種類を特定できます。

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

このクエリによって公開される統計の説明は、次のとおりです。

次のクエリは、バッファー以外のすべてのラッチに関する情報を返します。

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

このクエリによって公開される統計の説明は、次のとおりです。

この DMV が返す値は、データベース エンジンが最後に再起動されたとき、または DMV がリセットされたときからの累積です。 データベース エンジンが最後に起動された時刻を調べるには、sys.dm_os_sys_info の sqlserver_start_time を使用します。 長時間実行されているシステムでは、これは max_wait_time_ms などの一部の統計があまり有用ではないことを意味します。 次のコマンドを使用して、この DMV の待機統計をリセットできます。

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

SQL Server におけるラッチの競合のシナリオ

以下のシナリオは、過度なラッチ競合の原因として観察されたものです。

最後のページまたは後続のページでの挿入の競合

ID 列または日付列に対するクラスター化インデックスの作成は、OLTP での一般的なプラクティスです。 これはインデックスの物理的な編成の維持に役立ち、インデックスの読み取りと書き込み両方のパフォーマンスを大幅に向上させることができます。 ただし、このスキーマの設計により、誤ってラッチの競合が発生する可能性があります。 この問題が最もよく見られるのは、小さい行が含まれる大きいテーブルでの、昇順の整数や日時キーなどの順番に増加するキー列が先頭に含まれるインデックスへの挿入です。 このシナリオの場合、アプリケーションで更新または削除が実行されることはめったにありませんが、アーカイブ操作は例外です。

次の例では、スレッド 1 とスレッド 2 の両方で、ページ 299 に格納されるレコードの挿入が実行されます。 論理ロックの観点からは、行レベルのロックが使用され、同じページ上の両方のレコードの排他ロックを同時に保持できるため、問題はありません。 ただし、物理メモリの整合性を保証するため、排他的ラッチを取得できるのは一度に 1 つのスレッドのみなので、ページへのアクセスはシリアル化され、メモリ内の更新は失われません。 この場合、スレッド 1 が排他ラッチを取得し、スレッド 2 は待機します。これにより、このリソースの PAGELATCH_EX 待機が待機統計に登録されます。 これは、wait_type DMV の sys.dm_os_waiting_tasks 値を通して表示されます。

この競合は、次の図に示すように、B ツリーの右端で発生するため、"最終ページの挿入" 競合と呼ばれることがよくあります。

この種類のラッチの競合は、次のように説明できます。 新しい行がインデックスに挿入されると、SQL Server は次のアルゴリズムを使用して変更を実行します。

1. B ツリーを走査して、新しいレコードを保持する正しいページを見つけます。

2. PAGELATCH_EXを使用してページをラッチし、他のユーザーが変更できないようにし、すべての非リーフ ページで共有ラッチ (PAGELATCH_SH) を取得します。

   Note

   場合によっては、SQL エンジンでは、非リーフ B ツリー ページでも EX ラッチを取得する必要があります。 たとえば、ページ分割が発生した場合、直接影響を受けるすべてのページを排他的にラッチする必要があります (PAGELATCH_EX)。

3. 行が変更されたことを示すログ エントリを記録します。

4. 行をページに追加し、ページをダーティとしてマークします。

5. すべてのページのラッチを解除します。

テーブル インデックスが順番に増加するキーに基づいている場合、新しい挿入のたびに、そのページがいっぱいになるまで、B ツリーの末尾にある同じページに移動します。 コンカレンシーの高いシナリオでは、B ツリーの右端で競合が発生し、クラスター化インデックスと非クラスター化インデックスで発生する可能性があります。 この種の競合の影響を受けるテーブルは、主に INSERT クエリを受け入れます。問題のあるインデックスのページは通常、比較的高密度です (たとえば、行サイズが最大 165 バイト (行オーバーヘッドを含む) は、1 ページあたり最大 49 行になります)。 この挿入が多い例では、 PAGELATCH_EX/PAGELATCH_SH 待機が発生することが想定されており、これが一般的な観察です。 ページ ラッチ待機とツリー ページ ラッチ待機を調べるには、sys.dm_db_index_operational_stats DMV を使用します。

次の表は、この種類のラッチの競合によって見られる主な要素をまとめたものです。

非クラスター化インデックスとランダムな挿入を使用する小さなテーブルでのラッチの競合 (キュー テーブル)

このシナリオは、通常、SQL テーブルが一時的なキューとして使用される場合に見られます (たとえば、非同期メッセージング システムの場合)。

このシナリオでは、排他的 (EX) と共有 (SH) ラッチの競合は、次の条件下で発生する可能性があります。

• 挿入、選択、更新、または削除の操作は、コンカレンシーが高い場合に発生します。
• 行のサイズは比較的小さいものです (高密度のページになります)。
• テーブル内の行数は比較的少なく、2 つまたは 3 つのインデックスの深さで定義された浅い B ツリーになります。

連続していない列がクラスター化インデックスの先頭キーであるときなど、アクセスが B ツリー全体でランダムであっても、ラッチの競合が発生する可能性があります。 次のスクリーンショットは、この種類のラッチの競合が発生しているシステムのものです。 この例では、行のサイズが小さく B ツリーが比較的浅いことによるページの密度が原因で、競合が発生しています。 GUID がインデックスの先頭の列であるため、コンカレンシーが高くなると、挿入が B ツリー全体に対してランダムであっても、ページでラッチの競合が発生します。

次のスクリーンショットでは、バッファー データ ページとページ空き領域 (PFS) ページの両方で、待機が発生しています。 データ ファイルの数が増えた場合でも、バッファー データ ページではラッチの競合が発生していました。

次の表は、この種類のラッチの競合によって見られる主な要素をまとめたものです。

浅い B ツリーとインデックス全体へのランダムな挿入が組み合わさると、B ツリーでページ分割が発生しやすくなります。 ページ分割を実行するには、SQL Server はすべてのレベルで共有 (SH) ラッチを取得し、ページ分割に関係する B ツリー内のページで排他的 (EX) ラッチを取得する必要があります。 また、コンカレンシーが高く、データが継続的に挿入および削除されると、B ツリー ルート分割が発生する可能性があります。 この場合、他の挿入は、B ツリーで取得された非バッファー ラッチを待機することが必要になる場合があります。 これは、sys.dm_os_latch_stats DMV で観察される ACCESS_METHODS_HOBT_VIRTUAL_ROOT ラッチの種類での多数の待機として示されます。

次のスクリプトを変更して、影響を受けたテーブルでのインデックスの B ツリーの深さを確認できます。

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

ページ空き領域 (PFS) ページでのラッチの競合

PFS はページ空き領域の略で、SQL Server は各データベース ファイルの 8088 ページ ( PageID = 1 以降) ごとに 1 つの PFS ページを割り当てます。 PFS ページの各バイトには、ページ上の空き領域の量、割り当てられているかどうか、ページにゴースト レコードが格納されているかどうかなどの情報が記録されます。 PFS ページには、挿入操作または更新操作で新しいページが必要なときに割り当てに使用できるページに関する情報が含まれます。 PFS ページは、割り当てや割り当て解除が発生した場合など、いくつかのシナリオで更新する必要があります。 PFS ページを保護するには更新 (UP) ラッチを使用する必要があるため、ファイル グループ内のデータ ファイルの数が比較的少なく、CPU コアの数が多い場合、PFS ページでラッチの競合が発生する可能性があります。 これを解決する簡単な方法は、ファイル グループごとのファイルの数を増やすことです。

tempdbの PFS ページまたは SGAM ページに対して多数のPAGELATCH_UP待機が発生した場合は、次の手順を実行して、このボトルネックを解消します。

1. tempdb データ ファイルの数がサーバー内のプロセッサ コアの数と同じになるように、データ ファイルを tempdb に追加します。

2. SQL Server トレース フラグ 1118 を有効にします。

システム ページの競合によって発生する割り当てのボトルネックの詳細については、ブログ記事「割り当てのボトルネックとは」を参照してください。

tempdb でのテーブル値関数とラッチの競合

割り当て競合以外にも、クエリ内での TVF の使用が多いなど、 tempdbでラッチの競合を引き起こす可能性がある他の要因があります。

さまざまなテーブル パターンに対するラッチの競合を処理する

以下のセクションでは、過度なラッチの競合に関連するパフォーマンスの問題に対処するために使用できる手法について説明します。

連続していない先頭のインデックス キーを使用する

ラッチの競合を処理する方法のひとつは、連続したインデックス キーを連番でないキーに置き換えて、インデックスの範囲に挿入を均等に配置することです。

通常、これは、ワークロードを比例的に分散するインデックスの先頭列を持つことによって行われます。 これには、次の 2 つのオプションがあります。

オプション: テーブル内の列を使用して、インデックス キーの範囲全体に値を分散させる

キーの範囲に挿入を分散させるために使用できる自然な値でワークロードを評価します。 たとえば、1 人の顧客が一度に 1 つの ATM しか使用できないので、 ATM_ID が出金のためにトランザクション テーブルに挿入を分散するのに適した候補となる可能性がある ATM バンキング シナリオを考えてみましょう。 同様に、販売時点管理システムでは、おそらく Checkout_ID またはストア ID は、キー範囲全体に挿入を分散するために使用できる自然な値になります。 この手法では、先頭のキー列が識別された列の値か、その値のハッシュを 1 つ以上の追加の列と組み合わせて一意性を提供する複合インデックス キーを作成する必要があります。 多くの場合、値のハッシュが最適に機能するのは、個別の値が多すぎると物理的な編成が悪くなるためです。 たとえば、販売時点管理システムの場合は、何らかの剰余 (これは、CPU コアの数に合わせます) である Store ID からハッシュを作成できます。 この手法を使用すると、テーブル内の範囲の数は比較的少なくなりますが、ラッチの競合が回避されるように挿入を分散させるには十分です。 次の図は、この手法を示したものです。

このパターンをパフォーマンス ラボのエンゲージメントの間に実装したところ、32 個の物理 CPU コアを搭載したシステムでラッチの競合が解決されました。 テーブルは、トランザクション終了時の残高を格納するために使用されました。各ビジネス トランザクションで、テーブルへの 1 回の挿入が実行されました。

元のテーブルの定義

元のテーブルの定義を使用すると、クラスター化インデックス pk_table1 で過剰なラッチの競合が発生することが観察されました。

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

並べ替えられたインデックスの定義

主キーにおいて UserID を先頭列としてインデックスのキー列を並べ替えると、挿入がデータページ全体にほぼランダムに分布されました。 すべてのユーザーが同時にオンラインになっているわけではないため、結果の分布は 100% ランダムではありませんでしたが、分布は過剰なラッチ競合を軽減するのに十分なランダムでした。 インデックス定義を並べ替える場合の 1 つの注意事項は、このテーブルに対するすべての選択クエリを、等値述語として UserID と TransactionID の両方を使用するように変更する必要があるということです。

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

主キーの先頭列としてハッシュ値を使用する

次のテーブルの定義を使用すると、CPU の数と一致する剰余を生成できます。B ツリー全体に均等に分散されるよう、HashValue は連続して増加する値 TransactionID を使用して生成されます。

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

オプション: インデックスの先頭キー列として GUID を使用する

自然な区切り記号がない場合は、インデックスの先頭キー列として GUID 列を使用して、挿入の均一な分散を確保できます。 インデックス キーの先頭列として GUID を使用すると、他の機能に対してパーティション分割を使用できるようになりますが、この手法を使用すると、より多くのページ分割、物理的な編成の不備、低ページ密度による潜在的な欠点が生じる可能性もあります。

計算列でハッシュ パーティション分割を使用する

SQL Server でテーブルのパーティション分割を使用すると、過度なラッチの競合を軽減できます。 パーティション テーブルの計算列でハッシュ パーティション分割の構成を作成することは、次の手順で実行できる一般的な方法です。

1. 新しいファイル グループを作成するか、既存のファイル グループを使用して、パーティションを保持します。

2. 新しいファイル グループを使用する場合は、最適なレイアウトを使用するように注意して、LUN に対して個別のファイルを均等に分散させます。 挿入の割合が高いアクセス パターンの場合は、SQL Server コンピューターの物理 CPU コアと同じ数のファイルを作成するようにします。

3. CREATE PARTITION FUNCTION コマンドを使用して、テーブルを X パーティションにパーティション分割します。X は SQL Server コンピューター上の物理 CPU コアの数です。 (少なくとも 32 個のパーティション)

   Note

   パーティションの数と CPU コアの数を 1 対 1 で調整する必要はありません。 多くの場合、これは CPU コアの数よりも少ない値になる可能性があります。 パーティションを増やすと、すべてのパーティションを検索する必要があるクエリのオーバーヘッドが増える可能性があり、このような場合に役立つパーティションが少なくなります。 実際の顧客ワークロードを使用した 64 および 128 個の論理 CPU システムによる SQLCAT テストの場合、過剰なラッチの競合を解決してスケール ターゲットを達成するのに、32 個のパーティションで十分でした。 最終的に、パーティションの最適な数はテストによって決定する必要があります。

4. CREATE PARTITION SCHEME コマンドを使用します。

   • パーティション関数をファイル グループにバインドします。
   • tinyint 型または smallint 型のハッシュ列をテーブルに追加します。
   • 適切なハッシュ分散を計算します。 たとえば、HASHBYTES を剰余や BINARY_CHECKSUM と共に使用します。

次のサンプル スクリプトをカスタマイズして実装に利用できます。

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

このスクリプトを使用すると、最後のページまたは後続のページでの挿入の競合によって引き起こされる問題が発生しているテーブルをハッシュ パーティションできます。 この手法を使用すると、テーブルがパーティション分割され、ハッシュ値剰余演算でテーブル パーティション全体に挿入が分散されることによって、最後のページから競合が移動されます。

計算列でのハッシュ パーティション分割によって行われること

次の図に示すように、この手法を使用すると、ハッシュ関数でインデックスが再構築され、SQL Server コンピューター上の物理 CPU コアと同じ数のパーティションが作成されることによって、最後のページから競合が移動されます。 挿入は引き続き論理範囲 (順番に増加する値) の最後に送られますが、ハッシュ値の剰余演算によって、挿入が異なる B ツリーに分割されることが保証されるため、ボトルネックは軽減されます。 次の図はこれを示したものです。

ハッシュ パーティション分割を使用するときのトレードオフ

ハッシュ パーティション分割によって挿入での競合を解消できますが、この手法を使用するかどうかを決定するときは、いくつかのトレードオフについて検討する必要があります。

• ほとんどの場合、選択クエリは、述語にハッシュ パーティションを含むように変更する必要があり、これらのクエリの発行時にパーティションの削除を提供しないクエリ プランになります。 次のスクリーンショットでは、ハッシュ パーティション分割が実装された後でパーティションの除去が行われない不適切なプランを示します。

  

• 範囲ベースのレポートなど、他の特定のクエリでのパーティションの除去の可能性はなくなります。

• ハッシュパーティションテーブルを別のテーブルに結合する場合、パーティションを削除するには、2 番目のテーブルを同じキーでハッシュパーティション分割する必要があり、ハッシュキーは結合条件の一部である必要があります。

• ハッシュ パーティション分割を使用すると、スライディング ウィンドウ アーカイブやパーティション切り替え機能などの他の管理機能で、パーティション分割を使用できません。

ハッシュ パーティション分割は、挿入時の競合が軽減されることでシステム全体のスループットが向上するため、過剰なラッチの競合を軽減するための効果的な方法です。 いくつかのトレードオフが関係しているため、一部のアクセス パターンに最適なソリューションではない可能性があります。

ラッチの競合の解決に使用される手法のまとめ

次の 2 つのセクションでは、過剰なラッチの競合に対処するために使用できる手法をまとめます。

連続していないキーとインデックス

長所:

• スライディング ウィンドウ スキームやパーティション切り替え機能を使用するデータのアーカイブなど、他のパーティション分割機能を使用できます。

短所:

• キーまたはインデックスを選択して常に挿入の分散を "ほぼ" 均等にするときに問題が発生する可能性があります。
• GUID を先頭列として使用することで均等な分散を保証できますが、ページ分割操作が過剰になる可能性があることに注意します。
• B ツリー全体にランダムに挿入すると、ページ分割操作が多くなり、非リーフ ページでラッチの競合が発生する可能性があります。

計算列を用いたハッシュ パーティション分割

長所:

• 挿入に対して透過的。

短所:

• パーティション分割は、パーティション切り替えオプションを使用したデータのアーカイブなどの目的の管理機能には使用できません。
• 個別および範囲ベースの選択や更新を含むクエリ、および結合を実行するクエリの場合、パーティション除去の問題が発生する可能性があります。
• 保存される計算列の追加はオフライン操作です。

チュートリアル: ラッチの競合を診断する

次のチュートリアルでは、「SQL Server のラッチの競合の診断」と「さまざまなテーブル パターンに対するラッチの競合の処理」で説明されているツールと手法を使用して、実際のシナリオで問題を解決する方法を示します。 このシナリオでは、8 ソケット、32 物理コア システム、256 GB のメモリで実行されている SQL Server アプリケーションに対してトランザクションを実行する約 8,000 ストアをシミュレートした、販売時点管理システムのロード テストを実行する顧客エンゲージメントについて説明します。

次の図は、販売時点管理システムのテストに使用されるハードウェアの詳細を示したものです。

症状: ホット ラッチ

この例では、普通平均 1 ミリ秒を超えるものとして高く定義される PAGELATCH_EX に対して高い待機が観察されました。 この例では、20 ミリ秒を超える待機を常に観察しました。

ラッチの競合が問題であると判断した後、ラッチの競合の原因の特定を始めました。

ラッチ競合の原因となっているオブジェクトを分離する

次のスクリプトでは、resource_description列を使用して、 PAGELATCH_EX 競合の原因となったインデックスを特定します。

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

次に示すように、競合はテーブル LATCHTEST とインデックス名 CIX_LATCHTESTにあります。 ワークロードを匿名にするため、名前は変更されていることに注意してください。

繰り返しポーリングを行い、一時テーブルを使用して構成可能な期間に対する合計待機時間を決定する、さらに高度なスクリプトについては、「付録」の「バッファー記述子のクエリを実行して、ラッチ競合の原因となっているオブジェクトを特定する」を参照してください。

ラッチ競合の原因となるオブジェクトを分離する別の方法

sys.dm_os_buffer_descriptors にクエリを実行するのは実用的 でない場合があります。 バッファー プールに使用できるシステムのメモリが増えると、この DMV を実行するために必要な時間も長くなります。 256 GB のシステムでは、この DMV が実行されるまでに最大 10 分以上かかる場合があります。 以下では、使用可能な別の手法を、ラボで別のワークロードを使用して実行した場合の概要を説明します。

1. 付録の「sys.dm_os_waiting_tasks のクエリを実行して待機時間の順に並べ替える」のスクリプトを使用して、現在待機中のタスクのクエリを実行します。

2. コンボイが観察されているキー ページを特定します。これは、複数のスレッドが同じページで競合している場合に発生します。 この例では、挿入を実行するスレッドが B ツリーの末尾のページで競合し、 EX ラッチを取得できるようになるまで待ちます。 これは、最初のクエリのresource_description (ここでは 8:1:111305) によって示されます。

3. トレース フラグ 3604 を有効にします。 DBCC PAGE を使用してページに関する詳細情報を次の構文で公開します。resource_descriptionで取得した値をかっこ内の値に置き換えます。

   トレース フラグ 3604 を有効にして、コンソール出力を有効にします。

   DBCC TRACEON (3604);
   

   ページの詳細を確認します。

   DBCC PAGE (8, 1, 111305, -1);
   

4. DBCC の出力を調べます。 この例では、メタデータ ObjectID が関連付けられている必要があります 78623323。

   

5. これで、次のコマンドを実行して、競合の原因となっているオブジェクトの名前を特定できます。これは予想どおり LATCHTEST。

   Note

   正しいデータベース コンテキストにあることを確認します。それ以外の場合、クエリは NULL返します。

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

まとめと結果

上記の手法を使用することで、最も多くの挿入を受け取ったテーブルの、順番に増加するキー値のクラスター化インデックスで、競合が発生していることを確認できました。 この種の競合は、datetime、ID、アプリケーションによって生成された TransactionIDなど、キー値が順番に増加するインデックスでは珍しくありません。

この問題を解決するため、計算列でハッシュ パーティション分割を使用し、パフォーマンスが 690% 向上することを観察しました。 次の表は、計算列でハッシュ パーティション分割を実装する前と後の、アプリケーションのパフォーマンスをまとめたものです。 ラッチの競合のボトルネックが解消された後、CPU の使用率は、予想どおりスループットと共に大きくなります。

前の表からわかるように、過剰なページ ラッチの競合によって発生するパフォーマンスの問題を正しく特定して解決すると、アプリケーションの全体的なパフォーマンスにプラスの影響を与える可能性があります。

付録: 代替手法

過剰なページラッチの競合を回避するための方法の1つは、char列で各行に余白を付け加え、各行が1つの完全なページを使用するようにすることです。 この方法は、全体的なデータ サイズが小さく、次の要因の組み合わせによって引き起こされるEXページ ラッチの競合に対処する必要がある場合のオプションです。

• 小さい行サイズ
• 浅い B ツリー
• ランダムな挿入、選択、更新、削除操作の割合が高いアクセス パターン
• 小さいテーブル (一時キュー テーブルなど)

ページ全体を占める行を埋め込むことにより、より多くのページを割り当てるよう SQL に要求し、挿入に使用できるページ数を増やして、EX ページ ラッチの競合を減らします。

各行が完全に1ページを占有するように行を埋める

行を埋め込んでページ全体を占めるには、次のようなスクリプトを使用できます。

ALTER TABLE mytable ADD Padding CHAR(5000) DEFAULT NOT NULL ('X');

完全を期すため、この手法について説明します。実際の SQLCAT においては、1 回のパフォーマンス エンゲージメントが 10,000 行の小さいテーブルでのみ、これを使用しました。 テーブルが大きい場合、SQL Server でのメモリ負荷が増加し、非リーフ ページで非バッファー ラッチの競合が発生する可能性があるため、この手法の適用には制限があります。 追加のメモリ負荷は、この手法を適用するための重要な制限要因になる可能性があります。 最新のサーバーでは使用可能なメモリ量が多いので、通常、OLTP ワークロードのワーキング セットの大部分はメモリに保持されます。 データ セットがメモリに収まらなくなったサイズに増えると、パフォーマンスが大幅に低下します。 したがって、この手法は、小さなテーブルに対してのみ適用できます。 SQLCAT は、大きなテーブルの最後のページや末尾ページへの挿入競合といったシナリオでは、この手法を使用しません。

付録: SQL Server ラッチ競合スクリプト

このセクションには、ラッチの競合の問題の診断とトラブルシューティングに使用できるスクリプトが含まれています。

sys.dm_os_waiting_tasks のクエリを実行してセッション ID の順に並べ替える

次のサンプル スクリプトを使用すると、sys.dm_os_waiting_tasks にクエリが実行され、セッション ID の順に並べ替えられたラッチ待機が返されます。

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

sys.dm_os_waiting_tasks のクエリを実行して待機時間の順に並べ替える

以下のサンプル スクリプト クエリは sys.dm_os_waiting_tasks を実行し、待機時間の順にラッチ待機を返します。

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

一定期間の待機時間を計算する

次のスクリプトを使用すると、一定期間におけるラッチ待機が計算されて返されます。

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

バッファー記述子のクエリを実行して、ラッチ競合の原因となっているオブジェクトを特定する

次のスクリプトを使用すると、バッファー記述子のクエリが実行されて、最も長いラッチ待機時間に関連付けられているオブジェクトが特定されます。

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

ハッシュ パーティション分割のスクリプト

このスクリプトの使用方法については、「計算列でハッシュ パーティション分割を使用する」で説明されており、実装するときはカスタマイズする必要があります。

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

関連コンテンツ

• パフォーマンス監視およびチューニング ツール