SQL Server でラッチの競合を診断および解決するDiagnose and resolve latch contention on SQL Server

このガイドでは、ある種のワークロードで高コンカレンシー システム上の SQL Server アプリケーションを実行したときに見られるラッチの競合の問題を特定し、解決する方法について説明します。This guide describes how to identify and resolve latch contention issues observed when running SQL Server applications on high concurrency systems with certain workloads.

サーバー上の CPU コアの数が増え続けると、それに関連するコンカレンシーの上昇により、データベース エンジン内で順次アクセスする必要がある競合ポイントがデータ構造内に発生する可能性があります。As the number of CPU cores on servers continues to increase, the associated increase in concurrency can introduce contention points on data structures that must be accessed in a serial fashion within the database engine. これは、高スループットで高コンカレンシーのトランザクション処理 (OLTP) ワークロードに特に当てはまります。This is especially true for high throughput/high concurrency transaction processing (OLTP) workloads. これらの課題に対処するためのさまざまなツール、手法、手段と、それらを完全に回避するのに役立つ場合がある、アプリケーションの設計で従うことができるプラクティスがあります。There are a number of tools, techniques, and ways to approach these challenges as well as practices that can be followed in designing applications which may help to avoid them altogether. この記事では、スピンロックを使用してこれらのデータ構造へのアクセスをシリアル化するデータ構造での特定の種類の競合について説明します。This article will discuss a particular type of contention on data structures that use spinlocks to serialize access to these data structures.

注意

このコンテンツは、Microsoft SQL Server Customer Advisory Team (SQLCAT) チームにより、高コンカレンシー システム上の SQL Server アプリケーションにおけるページ ラッチの競合に関連する問題の特定と解決についてのそのプロセスに基づいて作成されたものです。This content was written by the Microsoft SQL Server Customer Advisory Team (SQLCAT) team based on their process for identifying and resolving issues related to page latch contention in SQL Server applications on high-concurrency systems. ここに記載されている推奨事項とベスト プラクティスは、実際の OLTP システムの開発と展開における実際の経験に基づいています。The recommendations and best practices documented here are based on real-world experience during the development and deployment of real-world OLTP systems.

SQL Server のラッチの競合とはWhat is SQL Server latch contention?

ラッチとは、インデックス、データ ページ、および B ツリー内の非リーフ ページのような内部構造など、メモリ内の構造の整合性を保証するために SQL Server エンジンによって使用される、軽量の同期プリミティブです。Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages, and internal structures, such as non-leaf pages in a B-Tree. SQL Server では、バッファー プール内のページを保護するためにはバッファー ラッチが使用され、バッファー プールにまだ読み込まれていないページを保護するためには I/O ラッチが使用されます。SQL Server uses buffer latches to protect pages in the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool. SQL Server バッファー プール内のページのデータの書き込みまたは読み取りが行われるときは常に、ワーカー スレッドにおいて最初にそのページのバッファー ラッチを取得する必要があります。Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. バッファー プール内のページにアクセスするために使用できるバッファー ラッチには、排他的ラッチ (PAGELATCH_EX) や共有ラッチ (PAGELATCH_SH) など、さまざまな種類があります。There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH). SQL Server によってバッファー プールにまだ存在しないページへのアクセスが試みられると、そのページをバッファー プールに読み込むための非同期 I/O がポストされます。When SQL Server attempts to access a page that is not already present in the buffer pool, an asynchronous I/O is posted to load the page into the buffer pool. SQL Server で I/O サブシステムの応答を待機する必要がある場合は、要求の種類に応じて、排他 (PAGEIOLATCH_EX) または共有 (PAGEIOLATCH_SH) の I/O ラッチで待機が行われます。これは、別のワーカー スレッドにより互換性のないラッチを使用して同じページがバッファー プールに読み込まれるのを防ぐために行われます。If SQL Server needs to wait for the I/O subsystem to respond it will wait on an exclusive (PAGEIOLATCH_EX) or shared (PAGEIOLATCH_SH) I/O latch depending on the type of request; this is done to prevent another worker thread from loading the same page into the buffer pool with an incompatible latch. ラッチは、バッファー プール ページ以外の内部メモリ構造へのアクセスを保護するためにも使用されます。これらは、非バッファー ラッチと呼ばれます。Latches are also used to protect access to internal memory structures other than buffer pool pages; these are known as Non-Buffer latches.

ページ ラッチでの競合は、マルチ CPU システムで発生する最も一般的なシナリオであるため、この記事の大部分でこれらに焦点を当てています。Contention on page latches is the most common scenario encountered on multi-CPU systems and so most of this article will focus on these.

ラッチの競合は、複数のスレッドが同じメモリ内構造に対して互換性のないラッチを同時に取得しようとすると発生します。Latch contention occurs when multiple threads concurrently attempt to acquire incompatible latches to the same in-memory structure. ラッチは内部的な制御メカニズムであるため、それらを使用するタイミングは SQL エンジンによって自動的に決定されます。As a latch is an internal control mechanism; the SQL engine automatically determines when to use them. ラッチの動作は決定論的であるため、スキーマの設計を含むアプリケーションの決定が、この動作に影響することがあります。Because the behavior of latches is deterministic, application decisions including schema design can affect this behavior. この記事の目的は、次の情報を提供することです。This article aims to provide the following information:

  • SQL Server によるラッチの使用方法に関する背景情報。Background information on how latches are used by SQL Server.
  • ラッチの競合を調査するために使用されるツール。Tools used to investigate latch contention.
  • 観測されている競合の量が問題であるかどうかを判断する方法。How to determine if the amount of contention being observed is problematic.

いくつかの一般的なシナリオと、競合を軽減するための最適な対処方法について説明します。We will discuss some common scenarios and how best to handle them to alleviate contention.

SQL Server によるラッチの使用方法How does SQL Server use latches?

SQL Server のページは 8 KB で、複数の行を格納できます。A page in SQL Server is 8 KB and can store multiple rows. コンカレンシーとパフォーマンスを向上させるため、論理トランザクションの間保持されるロックとは異なり、バッファー ラッチが保持されるのはページに対する物理操作の間だけです。To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks, which are held for the duration of the logical transaction.

ラッチが SQL エンジンの内部に存在し、メモリの整合性を提供するために使用されるのに対し、ロックは論理的なトランザクションの整合性を提供するために SQL Server によって使用されます。Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency. 次の表は、ラッチとロックを比較したものです。The following table compares latches to locks:

構造Structure 目的Purpose 何によって制御されるかControlled by パフォーマンス コストPerformance cost 何によって公開されるかExposed by
ラッチLatch メモリ内の構造の整合性を保証します。Guarantee consistency of in-memory structures. SQL Server エンジンのみ。SQL Server engine only. パフォーマンス コストは低いです。Performance cost is low. 最大限のコンカレンシーを可能にし、最大のパフォーマンスを提供するため、論理トランザクションの間保持されるロックとは異なり、ラッチが保持されるのはメモリ内の構造に対する物理操作の間だけです。To allow for maximum concurrency and provide maximum performance, latches are held only for the duration of the physical operation on the in-memory structure, unlike locks, which are held for the duration of the logical transaction. sys.dm_os_wait_stats (Transact-SQL) - PAGELATCH、PAGEIOLATCH、LATCH の各待機の種類に関する情報が提供されます (LATCH_EX、LATCH_SH はすべてのバッファー ラッチ以外の待機をグループ化するために使用されます)。sys.dm_os_wait_stats (Transact-SQL) - Provides information on PAGELATCH, PAGEIOLATCH, and LATCH wait types (LATCH_EX, LATCH_SH is used to group all non-buffer latch waits).
sys.dm_os_latch_stats (Transact-SQL) – バッファー ラッチ以外の待機に関する詳細情報が提供されます。sys.dm_os_latch_stats (Transact-SQL) – Provides detailed information about non-buffer latch waits.
sys.dm_os_latch_stats (Transact-SQL) - この DMV により、各インデックスの集計された待機が提供されます。これは、ラッチ関連のパフォーマンスの問題のトラブルシューティングに役立ちます。sys.dm_os_latch_stats (Transact-SQL) - This DMV provides aggregated waits for each index, which is useful for troubleshooting latch-related performance issues.
[Lock] (ロック)Lock トランザクションの整合性が保証されます。Guarantee consistency of transactions. ユーザーが制御できます。Can be controlled by user. ロックはトランザクションの期間保持する必要があるため、ラッチに比べてパフォーマンス コストは高くなります。Performance cost is high relative to latches as locks must be held for the duration of the transaction. sys.dm_tran_locks (Transact-SQL)sys.dm_tran_locks (Transact-SQL).
sys.dm_exec_sessions (Transact-SQL)sys.dm_exec_sessions (Transact-SQL).

SQL Server のラッチ モードと互換性SQL Server latch modes and compatibility

ある程度のラッチの競合は、SQL Server エンジンの動作の通常の部分として予想されます。Some latch contention is to be expected as a normal part of the operation of the SQL Server engine. さまざまな互換性の複数のラッチ要求が同時に発生することは、高コンカレンシー システムでは避けられないことです。It is inevitable that multiple concurrent latch requests of varying compatibility will occur on a high concurrency system. SQL Server の場合、未処理のラッチ要求が完了するまで、互換性のないラッチ要求をキューで待機させることによって、ラッチの互換性が適用されます。SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed.

ラッチは、アクセスのレベルに関連する 5 つの異なるモードのいずれかで取得されます。Latches are acquired in one of five different modes, which relate to level of access. SQL Server のラッチ モードは、次のようにまとめることができます。SQL Server latch modes can be summarized as follows:

  • KP -- 保持ラッチ (Keep Latch)。参照されている構造を破棄できないようにします。KP -- Keep latch, ensures that the referenced structure cannot be destroyed. スレッドでバッファーの構造を確認する必要がある場合に使用されます。Used when a thread wants to look at a buffer structure. KP ラッチは、破棄 (DT) ラッチを除くすべてのラッチと互換性があるため、KP ラッチは "軽量" と見なされます。つまり、使用したときのパフォーマンスへの影響が最小限です。Because the KP latch is compatible with all latches except for the destroy (DT) latch, the KP latch is considered to be "lightweight", meaning that the impact on performance when using it is minimal. KP ラッチは DT ラッチと互換性がないため、参照されている構造体が他のスレッドによって破棄されることはありません。Since the KP latch is incompatible with the DT latch, it will prevent any other thread from destroying the referenced structure. たとえば、KP ラッチを使用すると、それによって参照される構造体が、レイジーライター プロセスによって破棄されなくなります。For example, a KP latch will prevent the structure it references from being destroyed by the lazywriter process. SQL Server のバッファー ページ管理でレイジーライター プロセスを使用する方法の詳細については、「ページの書き込み」を参照してください。For more information about how the lazywriter process is used with SQL Server buffer page management, see Writing Pages.

  • SH -- 共有ラッチ。参照されている構造体を読み取るために必要です (データ ページの読み取りなど)。SH -- Shared latch, required to read the referenced structure (e.g. read a data page). 共有ラッチの下では、複数のスレッドからリソースに同時にアクセスして読み取りを行うことができます。Multiple threads can simultaneously access a resource for reading under a shared latch.

  • UP -- 更新ラッチ (Update Latch)。SH (共有ラッチ) および KP とは互換性がありますが、それ以外とはないので、参照されている構造体に EX ラッチで書き込むことはできません。UP -- Update latch, is compatible with SH (Shared latch) and KP, but no others and therefore will not allow an EX latch to write to the referenced structure.

  • EX - 排他ラッチ (Exclusive Latch)。参照されている構造体に対する他のスレッドによる書き込みまたは読み取りをブロックします。EX -- Exclusive latch, blocks other threads from writing to or reading from the referenced structure. 使用例の 1 つは、破損ページ保護のためにページの内容を変更する場合です。One example of use would be to modify contents of a page for torn page protection.

  • DT -- 破棄ラッチ (Destroy Latch)。参照されている構造の内容を破棄する前に取得する必要があります。DT -- Destroy latch, must be acquired before destroying contents of referenced structure. たとえば、他のスレッドで使用できる空きバッファーのリストに追加する前に、クリーン ページを解放するため、レイジーライター プロセスで DT ラッチを取得する必要があります。For example, a DT latch must be acquired by the lazywriter process to free up a clean page before adding it to the list of free buffers available for use by other threads.

ラッチ モードにはさまざまな互換性レベルがあります。たとえば、共有ラッチ (SH) は、更新ラッチ (UP) または保持ラッチ (KP) とは互換性がありますが、破棄ラッチ (DT) とは互換性がありません。Latch modes have different levels of compatibility, for example, a shared latch (SH) is compatible with an update (UP) or keep (KP) latch but incompatible with a destroy latch (DT). ラッチに互換性がある限り、同じ構造に対して複数のラッチを同時に取得できます。Multiple latches can be concurrently acquired on the same structure as long as the latches are compatible. 互換性のないモードで保持されるラッチの取得をスレッドが試みると、それはキューに配置されて、リソースが使用可能であることを示すシグナルを待機します。When a thread attempts to acquire a latch held in a mode that is not compatible, it is placed into a queue to wait for a signal indicating the resource is available. SOS_Task 型のスピンロックは、キューに対してシリアル化されたアクセスを適用することにより、待機キューを保護するために使用されます。A spinlock of type SOS_Task is used to protect the wait queue by enforcing serialized access to the queue. 項目をキューに追加するには、このスピンロックを取得する必要があります。This spinlock must be acquired to add items to the queue. また、互換性のないラッチが解放されたときも、SOS_Task スピンロックによってキュー内のスレッドに通知されるため、待機中のスレッドは互換性のあるラッチを取得して操作を続行できます。The SOS_Task spinlock also signals threads in the queue when incompatible latches are released, allowing the waiting threads to acquire a compatible latch and continue working. 待機キューは、ラッチ要求が解放されると、先入れ先出し (FIFO) ベースで処理されます。The wait queue is processed on a first in, first out (FIFO) basis as latch requests are released. ラッチがこの FIFO システムに従うことにより、公平性が保証され、スレッドの枯渇が防止されます。Latches follow this FIFO system to ensure fairness and to prevent thread starvation.

ラッチ モードの互換性を次の表に示します (Y は互換性があることを示し、N は互換性がないことを示します)。Latch mode compatibility is listed in the following table (Y indicates compatibility and N indicates incompatibility):

ラッチ モードLatch mode KPKP SHSH UPUP EXEX DTDT
KPKP YY YY YY YY NN
SHSH YY YY YY NN NN
UPUP YY YY NN NN NN
EXEX YY NN NN NN NN
DTDT NN NN NN NN NN

SQL Server の SuperLatch とサブラッチSQL Server superLatches and sublatches

NUMA ベースの複数ソケットおよびマルチコア システムの増加に伴い、SQL Server 2005 では SuperLatch (サブラッチとも呼ばれます) が導入されました。これは、32 個以上の論理プロセッサを搭載したシステムでのみ有効です。With the increasing presence of NUMA based multiple socket / multi-core systems, SQL Server 2005 introduced SuperLatches, also known as sublatches, which are effective only on systems with 32 or more logical processors. SuperLatch を使用すると、高コンカレンシーの OLTP ワークロードにおける特定の使用パターンで、SQL エンジンの効率が向上します。たとえば、特定のページのパターンが、読み取り専用の共有 (SH) アクセスは多くても、書き込みはほとんど行われないような場合です。Superlatches improve efficiency of the SQL engine for certain usage patterns in highly concurrent OLTP workloads; for example, when certain pages have a pattern of heavy read-only shared (SH) access, but are written to rarely. このようなアクセス パターンを持つページの例としては、B ツリー (つまり、インデックス) のルート ページがあります。SQL エンジンでは、B ツリーの任意のレベルでページ分割が発生するとき、ルート ページで共有ラッチが保持されている必要があります。An example of a page with such an access pattern is a B-tree (i.e. index) root page; the SQL engine requires that a shared latch is held on the root page when a page-split occurs at any level in the B-tree. 挿入量の多い高コンカレンシー OLTP ワークロードでは、スループットに合わせてページ分割の数が大きく増加し、パフォーマンスが低下する可能性があります。In an insert-heavy and high-concurrency OLTP workload, the number of page splits will increase broadly in line with throughput, which can degrade performance. SuperLatch を使用すると、同時に実行されている複数のワーカー スレッドで SH ラッチが必要な場合に、共有ページへのアクセスのパフォーマンスが向上します。SuperLatches can enable increased performance for accessing shared pages where multiple concurrently running worker threads require SH latches. これを実現するため、SQL Server エンジンによりそのようなページのラッチが SuperLatch に動的にレベル上げされます。To accomplish this, the SQL Server Engine will dynamically promote a latch on such a page to a SuperLatch. SuperLatch を使用すると、1 つのラッチがサブラッチ構造 (CPU コアのパーティションごとに 1 つのサブラッチ) の配列にパーティション分割されることにより、メイン ラッチがプロキシ リダイレクターになり、読み取り専用のラッチに対するグローバル状態の同期が必要なくなります。A SuperLatch partitions a single latch into an array of sublatch structures, one sublatch per partition per CPU core, whereby the main latch becomes a proxy redirector and global state synchronization is not required for read-only latches. このようにすると、ワーカーは常に特定の CPU に割り当てられ、ローカル スケジューラに割り当てられた共有 (SH) サブラッチを取得することだけが必要です。In doing so, the worker, which is always assigned to a specific CPU, only needs to acquire the shared (SH) sublatch assigned to the local scheduler.

グローバルな状態を同期する必要がなくなると、ローカルな NUMA メモリにだけアクセスすればよく、パフォーマンスが大幅に向上するので、パーティション分割されていない共有ラッチより、共有 SuperLatch などの互換性のあるラッチの取得で使用されるリソースが減り、ホット ページへのアクセスのスケーリングが向上します。Acquisition of compatible latches, such as a shared Superlatch uses fewer resources and scales access to hot pages better than a non-partitioned shared latch because removing the global state synchronization requirement significantly improves performance by only accessing local NUMA memory. 逆に、排他 (EX) SuperLatch の取得の場合は、SQL ですべてのサブラッチに通知する必要があるため、標準的な EX ラッチの取得よりコストが高くなります。Conversely, acquiring an exclusive (EX) SuperLatch is more expensive than acquiring an EX regular latch as SQL must signal across all sublatches. SuperLatch において大量の EX アクセスのパターンが使用されていることが観察された場合は、ページがバッファー プールから破棄された後、SQL エンジンでそれをレベル下げできます。When a SuperLatch is observed to use a pattern of heavy EX access, the SQL Engine can demote it after the page is discarded from the buffer pool. 次の図は、通常のラッチとパーティション分割された SuperLatch を示したものです。The following diagram depicts a normal latch and a partitioned SuperLatch:

SQL Server の SuperLatch

SuperLatch の数、1 秒あたりの SuperLatch のレベル上げ数、1 秒あたりの SuperLatch のレベル下げ数など、SuperLatch に関する情報を収集するには、パフォーマンス モニターで SQL Server:Latches オブジェクトとそれに関連付けられたカウンターを使用します。Use the SQL Server:Latches object and associated counters in Performance Monitor to gather information about SuperLatches, including the number of SuperLatches, SuperLatch promotions per second, and SuperLatch demotions per second. SQL Server:Latches オブジェクトおよび関連付けられているカウンターの詳細については、「SQL Server の Latches オブジェクト」を参照してください。For more information about the SQL Server:Latches object and associated counters, see SQL Server, Latches Object.

ラッチ待機の種類Latch wait types

累積的な待機の情報は SQL Server によって追跡され、動的管理ビュー (DMW) sys.dm_os_wait_stats を使用してアクセスできます。Cumulative wait information is tracked by SQL Server and can be accessed using the Dynamic Management View (DMW) sys.dm_os_wait_stats. SQL Server で使用されている 3 つのラッチ待機種類は、sys.dm_os_wait_stats DMV の対応する "wait_type" によって定義されています。SQL Server employs three latch wait types as defined by the corresponding "wait_type" in the sys.dm_os_wait_stats DMV:

  • バッファー (BUF) ラッチ: ユーザー オブジェクトのインデックスとデータ ページの整合性を保証するために使用されます。Buffer (BUF) latch: used to guarantee consistency of index and data pages for user objects. また、SQL Server によってシステム オブジェクト用に使用されるデータ ページへのアクセスを保護するためにも使用されます。They are also used to protect access to data pages that SQL Server uses for system objects. たとえば、割り当てを管理するページは、バッファー ラッチによって保護されます。For example, pages that manage allocations are protected by buffer latches. これらには、Page Free Space (PFS)、Global Allocation Map (GAM)、Shared Global Allocation Map (SGAM)、Index Allocation Map (IAM) などのページが含まれます。These include the Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) and Index Allocation Map (IAM) pages. バッファー ラッチは、wait_typePAGELATCH_*sys.dm_os_wait_stats でレポートされます。Buffer latches are reported in sys.dm_os_wait_stats with a wait_type of PAGELATCH_*.

  • 非バッファー (非 BUF) ラッチ: バッファー プールページ以外のメモリ内構造の整合性を保証するために使用されます。Non-buffer (Non-BUF) latch: used to guarantee consistency of any in-memory structures other than buffer pool pages. 非バッファー ラッチに対するすべての待機は、LATCH_*wait_type としてレポートされます。Any waits for non-buffer latches will be reported as a wait_type of LATCH_*.

  • IO ラッチ: バッファー ラッチのサブセットであり、バッファー ラッチによって保護される同じ構造が I/O 操作でバッファー プールに読み込まれる必要があるときに、これらの構造の整合性を保証します。IO latch: a subset of buffer latches that guarantee consistency of the same structures protected by buffer latches when these structures require loading into the buffer pool with an I/O operation. IO ラッチを使用すると、別のスレッドによって、互換性のないラッチによって同じページがバッファー プールに読み込まれることが防がれます。IO latches prevent another thread loading the same page into the buffer pool with an incompatible latch. PAGEIOLATCH_*wait_type に関連付けられています。Associated with a wait_type of PAGEIOLATCH_*.

    注意

    PAGEIOLATCH 待機が大量に発生する場合は、SQL Server が I/O サブシステムで待機していることを意味します。If you see significant PAGEIOLATCH waits, it means that SQL Server is waiting on the I/O subsystem. ある程度の量の PAGEIOLATCH 待機が発生することは想定され、通常の動作ですが、PAGEIOLATCH 待機の平均時間が常に 10 ミリ秒 (ms) を超えている場合は、I/O サブシステムに負荷がかかっている原因を調査する必要があります。While a certain amount of PAGEIOLATCH waits is expected and normal behavior, if the average PAGEIOLATCH wait times are consistently above 10 milliseconds (ms) you should investigate why the I/O subsystem is under pressure.

sys.dm_os_wait_stats DMV を調べていて、非バッファー ラッチが検出される場合は、sys.dm_os_latch_waits を調べて、非バッファー ラッチの累積待機情報の詳細な内訳を取得する必要があります。If when examining the sys.dm_os_wait_stats DMV you encounter non-buffer latches, sys.dm_os_latch_waits must be examined to obtain a detailed breakdown of cumulative wait information for non-buffer latches. すべてのバッファー ラッチ待機は BUFFER ラッチ クラスに分類され、残りは非バッファー ラッチを分類するために使用されます。All buffer latch waits are classified under the BUFFER latch class, the remaining are used to classify non-buffer latches.

SQL Server のラッチの競合の現象と原因Symptoms and causes of SQL Server latch contention

ビジー状態の高コンカレンシー システムにおいては、SQL Server のラッチおよび他の制御メカニズムによって頻繁にアクセスされ、保護されている構造で、アクティブな競合が見られるのが普通です。On a busy high-concurrency system, it is normal to see active contention on structures that are frequently accessed and protected by latches and other control mechanisms in SQL Server. ページのラッチの取得に関連する競合と待機時間が、リソース (CPU) の使用率低下をもたらすほど大きく、それによりスループットに悪影響があるときは、問題であると考えられます。It is considered problematic when the contention and wait time associated with acquiring latch for a page is enough to reduce resource (CPU) utilization, which hinders throughput.

ラッチの競合の例Example of latch contention

次の図で、青い線は 1 秒あたりのトランザクション数によって測定された SQL Server のスループットを表し、黒い線はページ ラッチの平均待機時間を表します。In the following diagram, the blue line represents the throughput in SQL Server, as measured by Transactions per second; the black line represents average page latch wait time. この場合、各トランザクションでは、先頭の値を順番に増やしながら、クラスター化インデックスへの INSERT が実行されます (bigint データ型の IDENTITY 列の設定時など)。In this case, each transaction performs an INSERT into a clustered index with a sequentially increasing leading value, such as when populating an IDENTITY column of data type bigint. CPU の数が 32 まで増えるのに伴い、全体のスループットは低下し、黒い線でわかるようにページ ラッチ待機時間は約 48 ミリ秒に増加していることが明らかです。As the number of CPUs increase to 32 it is evident that the overall throughput has decreased and the page latch wait time has increased to approximately 48 milliseconds as evidenced by the black line. スループットとページ ラッチ待機時間の間にあるこのような逆相関関係は、簡単に診断できる一般的なシナリオです。This inverse relationship between throughput and page latch wait time is a common scenario that is easily diagnosed.

コンカレンシーが増加するとスループットが低下する

ラッチの競合が解決された場合のパフォーマンスPerformance when latch contention is resolved

次の図に示すように、ページ ラッチ待機が SQL Server のボトルネックになることはなくなり、スループットは 1 秒あたりのトランザクション数で測定して 300% 増加します。As the following diagram illustrates, SQL Server is no longer bottle-necked on page latch waits and throughput is increased by 300% as measured by transactions per second. これは、後の「計算列でハッシュ パーティション分割を使用する」で説明する手法によって実現されました。This was accomplished with the Use Hash Partitioning with a Computed Column technique described later in this article. このパフォーマンス向上は、コア数が多く、コンカレンシー レベルの高いシステムを対象とするものです。This performance improvement is directed at systems with high numbers of cores and a high level of concurrency.

ハッシュ パーティション分割によって実現されるスループットの向上

ラッチの競合に影響を与える要因Factors affecting latch contention

OLTP 環境でパフォーマンスを妨げるラッチの競合は、通常、次の 1 つ以上の要因に関連する高コンカレンシーに起因します。Latch contention that hinders performance in OLTP environments is typically caused by high concurrency related to one or more of the following factors:

要素Factor 詳細Details
SQL Server によって使用されている論理 CPU の数が多いHigh number of logical CPUs used by SQL Server ラッチの競合は、すべてのマルチコア システムで発生する可能性があります。Latch contention can occur on any multi-core system. 許容できるレベルを超えてアプリケーションのパフォーマンスに影響を与える過度なラッチの競合が発生した SQLCAT では、CPU コアの数が 16 以上のシステムで最もよく検出され、使用できるコアの数が増えると増加する可能性があります。In SQLCAT experience excessive latch contention, which impacts application performance beyond acceptable levels, has most commonly been observed on systems with 16+ CPU cores and may increase as additional cores are made available.
スキーマの設計とアクセス パターンSchema design and access patterns B ツリーの深さ、クラスター化および非クラスター化インデックスの設計、ページごとの行のサイズと密度、およびアクセス パターン (読み取り、書き込み、削除アクティビティ) は、ページ ラッチの過剰な競合に寄与する可能性のある要因です。Depth of B-tree, clustered and non-clustered index design, size and density of rows per page, and access patterns (read/write/delete activity) are factors that can contribute to excessive page latch contention.
アプリケーション レベルでの高いコンカレンシーHigh degree of concurrency at the application level ページ ラッチの過剰な競合は、通常、アプリケーション層からの高いレベルの同時要求と共に発生します。Excessive page latch contention typically occurs in conjunction with a high level of concurrent requests from the application tier. やはり特定のページに対して多数の要求が発生する可能性のあるプログラミング プラクティスもあります。There are certain programming practices that can also introduce a high number of requests for a specific page.
SQL Server データベースによって使用される論理ファイルのレイアウトLayout of logical files used by SQL Server databases 論理ファイルのレイアウトが、Page Free Space (PFS)、Global Allocation Map (GAM)、Shared Global Allocation Map (SGAM)、Index Allocation Map (IAM) ページなどの割り当て構造によって発生するページ ラッチの競合のレベルに影響を与える可能性があります。Logical file layout can affect the level of page latch contention caused by allocation structures such as Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) and Index Allocation Map (IAM) pages. 詳細については、「TempDB の監視とトラブルシューティング: 割り当てのボトルネック」を参照してください。For more information, see TempDB Monitoring and Troubleshooting: Allocation Bottleneck.
I/O サブシステムのパフォーマンスI/O subsystem performance 大量の PAGEIOLATCH 待機は、SQL Server が I/O サブシステムで待機していることを示します。Significant PAGEIOLATCH waits indicate SQL Server is waiting on the I/O subsystem.

SQL Server のラッチの競合の診断Diagnosing SQL Server latch contention

このセクションでは、環境に問題があるかどうかを判断するための SQL Server のラッチの競合の診断について説明します。This section provides information for diagnosing SQL Server latch contention to determine if it is problematic to your environment.

ラッチの競合を診断するためのツールと方法Tools and methods for diagnosing latch contention

ラッチの競合の診断に使用される主なツールは次のとおりです。The primary tools used to diagnose latch contention are:

  • SQL Server 内で CPU 使用率と待機時間を監視し、CPU 使用率とラッチ待機時間の間に関係があるかどうかを確認するためのパフォーマンス モニター。Performance Monitor to monitor CPU utilization and wait times within SQL Server and establish whether there is a relationship between CPU utilization and latch wait times.

  • 問題の原因になっている特定のラッチの種類と、影響を受けるリソースを確認するために使用できる SQL Server DMV。The SQL Server DMVs, which can be used to determine the specific type of latch that is causing the issue and the affected resource.

  • 場合によっては、SQL Server プロセスのメモリ ダンプを取得し、Windows デバッグ ツールで分析する必要があります。In some cases memory dumps of the SQL Server process must be obtained and analyzed with Windows debugging tools.

注意

このレベルの高度なトラブルシューティングは、通常、非バッファー ラッチの競合のトラブルシューティングを行う場合にのみ必要です。This level of advanced troubleshooting is typically only required if troubleshooting non-buffer latch contention. この種の高度なトラブルシューティングについては、マイクロソフト製品サポート サービスへの問い合わせが必要になる場合があります。You may wish to engage Microsoft Product Support Services for this type of advanced troubleshooting.

ラッチの競合を診断するための技術的なプロセスは、次の手順にまとめられます。The technical process for diagnosing latch contention can be summarized in the following steps:

  1. ラッチに関連している可能性のある競合が発生しているかどうかを確認します。Determine that there is contention that may be latch-related.

  2. DMV ビューを使用して (「付録: SQL Server ラッチ競合スクリプト」で提供されているもの)、ラッチの種類と影響を受けたリソースを特定します。Use the DMV views provided in Appendix: SQL Server Latch Contention Scripts to determine the type of latch and resource(s) affected.

  3. さまざまなテーブル パターンに対するラッチの競合の処理」で説明されている手法のいずれかを使用して、競合を軽減します。Alleviate the contention using one of the techniques described in Handling Latch Contention for Different Table Patterns.

ラッチの競合のインジケーターIndicators of latch contention

前に説明したように、ラッチの競合は、ページ ラッチの取得に関連する競合と待機時間が原因で、使用できる CPU リソースがあるのにスループットが高くならない場合にのみ問題になります。As stated previously, latch contention is only problematic when the contention and wait time associated with acquiring page latches prevents throughput from increasing when CPU resources are available. 許容される競合の量を決定するには、パフォーマンスとスループットの要件と、利用できる I/O および CPU リソースを併せて考慮する、包括的なアプローチが必要です。Determining an acceptable amount of contention requires a holistic approach that considers performance and throughput requirements together with available I/O and CPU resources. このセクションでは、次のようにようにして、ワークロードに対するラッチの競合の影響を判断する方法について説明します。This section will walk you through determining the impact of latch contention on workload as follows:

  1. 代表的なテストの間の全体的な待機時間を測定します。Measure overall wait times during a representative test.

  2. それらを順番にランク付けします。Rank them in order.

  3. ラッチに関係があるものの割合を判断します。Determine the proportion of those that are related to latches.

累積待機情報は、sys.dm_os_wait_stats DMV から入手できます。Cumulative wait information is available from the sys.dm_os_wait_stats DMV. 最も一般的なラッチの競合の種類はバッファー ラッチ競合であり、wait_type が *PAGELATCH_* _ であるラッチ待機時間の増加として観察されます。The most common type of latch contention is buffer latch contention, observed as an increase in wait times for latches with a wait_type of *PAGELATCH_* _. 非バッファー ラッチは、待機の種類 LATCH* の下にグループ化されています。Non-buffer latches are grouped under the LATCH* wait type. 次の図に示すように、まず、_sys.dm_os_wait_stats* DMV を使用してシステムの累積待機時間を取得し、バッファーまたは非バッファー ラッチによる全体的な待機時間の割合を確認する必要があります。As the following diagram illustrates, you should first take a cumulative look at system waits using the _sys.dm_os_wait_stats* DMV to determine the percentage of the overall wait time caused by buffer or non-buffer latches. 非バッファー ラッチが発生している場合は、sys.dm_os_latch_stats DMV も調べる必要があります。If you encounter non-buffer latches, the sys.dm_os_latch_stats DMV must also be examined.

次の図は、sys.dm_os_wait_statssys.dm_os_latch_stats DMV によって返される情報の関係を示したものです。The following diagram describes the relationship between the information returned by the sys.dm_os_wait_stats and sys.dm_os_latch_stats DMVs.

ラッチの待機

sys.dm_os_wait_stats DMV の詳細については、SQL Server のヘルプの「sys.dm_os_wait_stats (Transact-SQL)」を参照してください。For more information about the sys.dm_os_wait_stats DMV, see sys.dm_os_wait_stats (Transact-SQL) in SQL Server help.

sys.dm_os_latch_stats DMV の詳細については、SQL Server のヘルプの「sys.dm_os_latch_stats (Transact-SQL)」を参照してください。For more information about the sys.dm_os_latch_stats DMV, see sys.dm_os_latch_stats (Transact-SQL) in SQL Server help.

ラッチ待機時間の次のメジャーは、過度なラッチの競合がアプリケーションのパフォーマンスに影響を与えていることを示すインジケーターです。The following measures of latch wait time are indicators that excessive latch contention is affecting application performance:

  • スループットと共にページ ラッチの平均待機時間が一貫して上昇する: スループットと共にページ ラッチの平均待機時間が一貫して上昇し、バッファー ラッチの平均待機時間も予想されるディスク応答時間を上回る場合は、sys.dm_os_waiting_tasks DMV を使用して現在の待機中のタスクを調べる必要があります。Average page latch wait time consistently increases with throughput: If average page latch wait times consistently increase with throughput and if average buffer latch wait times also increase above expected disk response times, you should examine current waiting tasks using the sys.dm_os_waiting_tasks DMV. 平均は、それだけで分析すると誤って解釈される可能性があるため、可能な場合はシステムをライブで確認し、ワークロードの特性を理解することが重要です。Averages can be misleading if analyzed in isolation so it is important to look at the system live when possible to understand workload characteristics. 特に、任意のページの PAGELATCH_EX 要求や PAGELATCH_SH 要求で長い待機時間が発生しているかどうかを確認します。In particular, check whether there are high waits on PAGELATCH_EX and/or PAGELATCH_SH requests on any pages. 次の手順に従って、スループットに伴う平均ページ ラッチ待機時間の一貫した上昇を診断します。Follow these steps to diagnose increasing average page latch wait times with throughput:

    注意

    特定の待機の種類 (sys.dm_os_wait_stats によって wt_:type として返されます) に対する平均待機時間を計算するには、合計待機時間 (wait_time_ms として返されます) を待機中のタスクの数 (waiting_tasks_count として返されます) で除算します。To calculate the average wait time for a particular wait type (returned by sys.dm_os_wait_stats as wt_:type), divide total wait time (returned as wait_time_ms) by the number of waiting tasks (returned as waiting_tasks_count).

  • ピーク負荷の間にラッチ待機の種類で費やされた合計待機時間の割合: 総待機時間の割合としての平均ラッチ待機時間が、アプリケーションの負荷に従って増加する場合、ラッチの競合がパフォーマンスに影響を与えている可能性があり、調査する必要があります。Percentage of total wait time spent on latch wait types during peak load: If the average latch wait time as a percentage of overall wait time increases in line with application load, then latch contention may be affecting performance and should be investigated.

    SQLServer:Wait Statistics Object パフォーマンス カウンターを使用して、ページ ラッチ待機と非ページ ラッチ待機を測定します。Measure page latch waits and non-page latch waits with the SQLServer:Wait Statistics Object performance counters. その後、これらのパフォーマンス カウンターの値を、CPU、I/O、メモリ、ネットワーク スループットに関連付けられているパフォーマンス カウンターと比較します。Then compare the values for these performance counters to performance counters associated with CPU, I/O, memory, and network throughput. たとえば、トランザクション数/秒とバッチ要求数/秒は、リソース使用率の 2 つの適切な測定値です。For example, transactions/sec and batch requests/sec are two good measures of resource utilization.

    注意

    各待機の種類に対する相対待機時間は、sys.dm_os_wait_stats DMV には含まれません。この DMW を使用すると、SQL Server のインスタンスが最後に開始されてから、または DBCC SQLPERF を使用して累積待機統計がリセットてからの待機時間が、測定されるためです。Relative wait time for each wait type is not included in the sys.dm_os_wait_stats DMV because this DMW measures wait times since the last time that the instance of SQL Server was started or the cumulative wait statistics were reset using DBCC SQLPERF. 各待機の種類の相対的な待機時間を計算するには、ピーク負荷の前と後に sys.dm_os_wait_stats のスナップショットを取得して、その差を計算します。To calculate the relative wait time for each wait type take a snapshot of sys.dm_os_wait_stats before peak load, after peak load, and then calculate the difference. 一定期間の待機時間を計算する」のサンプル スクリプトを、この目的に使用できます。The sample script Calculate Waits Over a Time Period can be used for this purpose.

    非運用環境 のみの場合は、次のコマンドを使用して sys.dm_os_wait_stats DMV をクリアします。For a non-production environment only, clear the sys.dm_os_wait_stats DMV with the following command:

    dbcc SQLPERF ('sys.dm_os_wait_stats', 'CLEAR')
    

    同様のコマンドを実行して、sys.dm_os_latch_stats DMV をクリアできます。A similar command can be run to clear the sys.dm_os_latch_stats DMV:

    dbcc SQLPERF ('sys.dm_os_latch_stats', 'CLEAR')
    
  • アプリケーションの負荷が増加して、SQL Server で使用可能な CPU の数が増えても、スループットが上昇せず、場合によっては低下する: これは、「ラッチの競合の例」で示したものです。Throughput does not increase, and in some case decreases, as application load increases and the number of CPUs available to SQL Server increases: This was illustrated in Example of Latch Contention.

  • アプリケーションのワークロードが増えても、CPU 使用率が増加しない: アプリケーションのスループットによってコンカレンシーが上昇しても、システムの CPU 使用率が増加しない場合、これは SQL Server が何かを待機していることを示し、ラッチの競合の兆候です。CPU Utilization does not increase as application workload increases: If the CPU utilization on the system does not increase as concurrency driven by application throughput increases, this is an indicator that SQL Server is waiting on something and symptomatic of latch contention.

根本原因を分析します。Analyze root cause. 前記の各条件が当てはまる場合でも、パフォーマンスの問題の根本原因が他にある可能性があります。Even if each of the preceding conditions is true it is still possible that the root cause of the performance issues lies elsewhere. 実際、CPU の使用率が最適にならない場合の最大の原因は、ロックでのブロック、I/O 関連の待機、ネットワーク関連の問題など、他の種類の待機です。In fact, in the majority of cases sub-optimal CPU utilization is caused by other types of waits such as blocking on locks, I/O related waits or network-related issues. 経験則として、より詳細な分析を進める前に、待機時間全体に対する割合が最大のリソース待機を解決するのが、常に最善の方法です。As a rule of thumb it is always best to resolve the resource wait that represents the greatest proportion of overall wait time before proceeding with more in-depth analysis.

現在の待機バッファー ラッチの分析Analyzing current wait buffer latches

バッファー ラッチの競合は、_sys.dm_os_wait_stats* DMV に表示される wait_type が *PAGELATCH_* _ または *PAGEIOLATCH_** であるラッチ待機時間の増加として示されます。Buffer latch contention manifests as an increase in wait times for latches with a wait_type of either PAGELATCH_* _ or PAGEIOLATCH_* as displayed in the _sys.dm_os_wait_stats DMV. システムをリアルタイムで確認するには、システムに対して次のクエリを実行し、sys.dm_os_wait_statssys.dm_exec_sessionssys.dm_exec_requests の各 DMV を結合します。To look at the system in real-time run the following query on a system to join the sys.dm_os_wait_stats, sys.dm_exec_sessions and sys.dm_exec_requests DMVs. その結果を使用して、サーバーで実行されているセッションの現在の待機の種類を特定できます。The results can be used to determine the current wait type for sessions executing on the server.

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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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

実行中のセッションの待機の種類

このクエリによって公開される統計の説明は、次のとおりです。The statistics exposed by this query are described as follows:

統計Statistic 説明Description
Session_idSession_id タスクに関連付けられているセッションの ID。ID of the session associated with the task.
Wait_typeWait_type SQL Server によってエンジンに記録された待機の種類。これにより、現在の要求の実行が妨げられています。The type of wait that SQL Server has recorded in the engine, which is preventing a current request from being executed.
Last_wait_typeLast_wait_type 要求がブロックされていた場合の最後の待機の種類。If this request has previously been blocked, this column returns the type of the last wait. NULL 値は許可されません。Is not nullable.
Wait_duration_msWait_duration_ms SQL Server インスタンスが開始された後、または累積待機統計がリセットされた後に、この待機の種類での待機に費やされた合計待機時間 (ミリ秒単位)。The total wait time in milliseconds spent waiting on this wait type since SQL Server instance was started or since cumulative wait statistics were reset.
Blocking_session_idBlocking_session_id 要求をブロックしているセッションの ID。ID of the session that is blocking the request.
Blocking_exec_context_idBlocking_exec_context_id タスクに関連付けられている実行コンテキストの ID。ID of the execution context associated with the task.
Resource_descriptionResource_description resource_description 列には、待機中の正確なページが次の形式で一覧表示されます: <database_id>:<file_id>:<page_id>The resource_description column lists the exact page being waited for in the format: <database_id>:<file_id>:<page_id>

次のクエリを実行すると、すべての非バッファー ラッチに関する情報が返されます。The following query will return information for all non-buffer latches:

select * from sys.dm_os_latch_stats where latch_class <> 'BUFFER' order by wait_time_ms desc

クエリの出力

このクエリによって公開される統計の説明は、次のとおりです。The statistics exposed by this query are described as follows:

統計Statistic 説明Description
Latch_classLatch_class SQL Server によってエンジンに記録されたラッチの種類。これにより、現在の要求の実行が妨げられています。The type of latch that SQL Server has recorded in the engine, which is preventing a current request from being executed.
Waiting_requests_countWaiting_requests_count SQL Server が再起動されてからの、このクラスのラッチでの待機の数。Number of waits on latches in this class since SQL Server restarted. このカウンターは、ラッチ待機の開始時に増分されます。This counter is incremented at the start of a latch wait.
Wait_time_msWait_time_ms このラッチの種類での待機に費やされた合計待機時間 (ミリ秒単位)。The total wait time in milliseconds spent waiting on this latch type.
Max_wait_time_msMax_wait_time_ms 任意の要求で、このラッチの種類での待機に費やされた最大時間 (ミリ秒単位)。Maximum time in milliseconds any request spent waiting on this latch type.

注意

この DMV によって返される値は、サーバーが最後に再起動されたとき、または DMV がリセットされたときからの累積です。The values returned by this DMV are cumulative since last time the server was restarted or the DMV was reset. 長時間実行されているシステムの場合、これは Max_wait_time_ms などの一部の統計があまり役に立たないことを意味します。On a system that has been running a long time this means some statistics such as Max_wait_time_ms are rarely useful. 次のコマンドを使用して、この DMV の待機統計をリセットできます。The following command can be used to reset the wait statistics for this DMV:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR)

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

以下のシナリオは、過度なラッチ競合の原因として観察されたものです。The following scenarios have been observed to cause excessive latch contention.

最後のページまたは後続のページでの挿入の競合Last page/trailing page insert contention

ID 列または日付列に対するクラスター化インデックスの作成は、OLTP での一般的なプラクティスです。A common OLTP practice is to create a clustered index on an identity or date column. これはインデックスの物理的な編成の維持に役立ち、インデックスの読み取りと書き込み両方のパフォーマンスを大幅に向上させることができます。This helps maintain good physical organization of the index, which can greatly benefit performance of both reads and writes to the index. ただし、このスキーマの設計により、誤ってラッチの競合が発生する可能性があります。This schema design can inadvertently lead to latch contention however. この問題が最もよく見られるのは、小さい行が含まれる大きいテーブルでの、昇順の整数や日時キーなどの順番に増加するキー列が先頭に含まれるインデックスへの挿入です。This issue is most commonly seen with a large table, with small rows; and inserts into an index containing a sequentially increasing leading key column such as ascending integer or datetime key. このシナリオの場合、アプリケーションで更新または削除が実行されることはめったにありませんが、アーカイブ操作は例外です。In this scenario, the application rarely if ever performs updates or deletes, the exception being for archiving operations.

次の例では、スレッド 1 とスレッド 2 の両方で、ページ 299 に格納されるレコードの挿入が実行されます。In the following example, thread one and thread two both want to perform an insert of a record that will be stored on page 299. 論理的なロックの観点からは、行レベルのロックが使用され、同じページ上の両方のレコードに対して排他ロックを同時に保持できるため、問題はありません。From a logical locking perspective, there is no problem as row level locks will be used and exclusive locks on both records on the same page can be held at the same time. ただし、物理メモリの整合性を保証するため、排他的ラッチを取得できるのは一度に 1 つのスレッドのみなので、ページへのアクセスはシリアル化され、メモリ内の更新は失われません。However to ensure integrity of physical memory only one thread at a time can acquire an exclusive latch so access to the page is serialized to prevent lost updates in memory. この場合、スレッド 1 が排他ラッチを取得し、スレッド 2 は待機します。これにより、このリソースの PAGELATCH_EX 待機が待機統計に登録されます。In this case, thread 1 acquires the exclusive latch; and thread 2 waits, which registers a PAGELATCH_EX wait for this resource in the wait statistics. これは、sys.dm_os_waiting_tasks DMV の wait_type 値によって表示されます。This is displayed through the wait_type value in the sys.dm_os_waiting_tasks DMV.

最後の行での排他ページ ラッチ

この競合は、次の図に示すように、B ツリーの右端で発生するため、"最終ページの挿入" 競合と呼ばれることがよくあります。This contention is commonly referred to as "Last Page Insert" contention because it occurs on the right-most edge of the B-tree as displayed in the following diagram:

最終ページの挿入競合

この種類のラッチの競合は、次のように説明できます。This type of latch contention can be explained as follows. 新しい行がインデックスに挿入されると、SQL Server により次のアルゴリズムを使用して変更が実行されます。When a new row is inserted into an index, SQL Server will use the following algorithm to execute the modification:

  1. B ツリーを走査して、新しいレコードを保持する正しいページを見つけます。Traverse the B-tree to locate the correct page to hold the new record.

  2. PAGELATCH_EX でページをラッチして他から変更できないようにし、すべての非リーフ ページに対する共有ラッチ (PAGELATCH_SH) を取得します。Latch the page with PAGELATCH_EX, preventing others from modifying it, and acquire shared latches (PAGELATCH_SH) on all the non-leaf pages.

    注意

    場合によっては、SQL エンジンで非リーフ B ツリーページに対する EX ラッチも取得する必要があります。In some cases the SQL Engine requires EX latches to be acquired on non-leaf B-tree pages as well. たとえば、ページ分割が発生する場合は、直接影響を受けるページを排他的にラッチする必要があります (PAGELATCH_EX)。For example, when a page-split occurs any pages that will be directly impacted need to be exclusively latched (PAGELATCH_EX).

  3. 行が変更されたことを示すログ エントリを記録します。Record a log entry that the row has been modified.

  4. 行をページに追加し、ページをダーティとしてマークします。Add the row to the page and mark the page as dirty.

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

テーブル インデックスが順番に増加するキーに基づいている場合、各新規挿入は、B ツリーの最後にある同じページに、そのページがいっぱいになるまで送られます。If the table index is based upon a sequentially increasing key, each new insert will go to the same page at the end of the B-tree, until that page is full. 高コンカレンシーのシナリオの場合、これにより B ツリーの右端で競合が発生する可能性があります。これは、クラスター化インデックスと非クラスター化インデックスの両方で発生する場合があります。Under high-concurrency scenarios, this may cause contention on the rightmost edge of the B-tree and can occur on clustered and non-clustered indexes. この種類の競合の影響を受けるテーブルは、主に INSERT を受け入れ、問題のあるインデックスのページは比較的高密度です (たとえば、行のオーバーヘッドを含む行サイズ ~165 バイトは、1 ページあたり ~49 行になります)。Tables that are affected by this type of contention primarily accept INSERTs, and pages for the problematic indexes are normally relatively dense (for example, a row size ~165 bytes (including row overhead) equals ~49 rows per page). この挿入の多い例の場合、PAGELATCH_EX および PAGELATCH_SH 待機が発生することが予想され、これは一般的な観察です。In this insert-heavy example, it is expected that PAGELATCH_EX/PAGELATCH_SH waits will occur, and this is the typical observation. ページ ラッチ待機とツリー ページ ラッチ待機の対比を調べるには、sys.dm_db_index_operational_stats DMV を使用します。To examine Page Latch waits vs. Tree Page Latch waits, use the sys.dm_db_index_operational_stats DMV.

次の表は、この種類のラッチの競合によって見られる主な要素をまとめたものです。The following table summarizes the major factors observed with this type of latch contention:

要素Factor 一般的な観測Typical observations
SQL Server によって使用されている論理 CPULogical CPUs in use by SQL Server この種類のラッチの競合は、主に CPU コアが 16 個以上のシステムで発生し、CPU コアが 32 個以上のシステムで最も一般的です。This type of latch contention occurs mainly on 16+ CPU core systems and most commonly on 32+ CPU core systems.
スキーマの設計とアクセス パターンSchema design and access patterns トランザクション データのテーブルのインデックスでの先頭列として、順番に増加する ID 値を使用します。Uses a sequentially increasing identity value as a leading column in an index on a table for transactional data.

インデックスには、挿入の率が高い、増加する主キーがあります。The index has an increasing primary key with a high rate of inserts.

インデックスには、少なくとも 1 つの順番に増加する列の値があります。The index has at least one sequentially increasing column value.

通常、行サイズが小さく、ページごとに多数の行が含まれます。Typically small row size with many rows per page.
観察される待機の種類Wait type observed 待機期間クエリの順に並べ替えられた sys.dm_os_waiting_tasks のクエリによって返される sys.dm_os_waiting_tasks DMV 内の同じ resource_description に関連付けられている排他 (EX) または共有 (SH) ラッチ待機で同じリソースに対して競合する多くのスレッド。Many threads contending for same resource with exclusive (EX) or shared (SH) latch waits associated with the same resource_description in the sys.dm_os_waiting_tasks DMV as returned by the Query sys.dm_os_waiting_tasks Ordered by Wait Duration query.
考慮する設計要素Design factors to consider 挿入が常に B ツリー全体に一様に分散されることを保証できる場合は、連続しないインデックスの軽減戦略で説明されているように、インデックス列の順序を変更を検討します。Consider changing the order of the index columns as described in the Non-sequential index mitigation strategy if you can guarantee that inserts will be distributed across the B-tree uniformly all of the time.

ハッシュ パーティション軽減戦略を使用した場合は、スライディング ウィンドウ アーカイブなどの他の目的にパーティション分割を使用できなくなります。If the Hash partition mitigation strategy is used it removes the ability to use partitioning for any other purposes such as sliding window archiving.

ハッシュ パーティション軽減戦略を使用すると、アプリケーションによって使用される SELECT クエリに対してパーティション削除の問題が発生する可能性があります。Use of the Hash partition mitigation strategy can lead to partition elimination problems for SELECT queries used by the application.

非クラスター化インデックスとランダムな挿入を使用する小さなテーブルでのラッチの競合 (キュー テーブル)Latch contention on small tables with a non-clustered index and random inserts (queue table)

このシナリオは、通常、SQL テーブルが一時的なキューとして使用される場合に見られます (たとえば、非同期メッセージング システムの場合)。This scenario is typically seen when an SQL table is used as a temporary queue (for example, in an asynchronous messaging system).

このシナリオでは、次のような状況で排他 (EX) と共有 (SH) のラッチの競合が発生する可能性があります。In this scenario exclusive (EX) and shared (SH) latch contention can occur under the following conditions:

  • 挿入、選択、更新、または削除の各操作が、高コンカレンシーで発生します。Insert, select, update or delete operations occur under high concurrency.
  • 行のサイズは比較的小さいものです (高密度のページになります)。Row size is relatively small (leading to dense pages).
  • テーブル内の行数は比較的少なく、2 つまたは 3 つのインデックスの深さで定義された浅い B ツリーになります。The number of rows in the table is relatively small; leading to a shallow B-tree, defined by having an index depth of two or three.

注意

これより深さが大きい B ツリーであっても、データ操作言語 (DML) の頻度とシステムのコンカレンシーが十分に高い場合は、この種類のアクセス パターンでの競合が発生する可能性があります。Even B-trees with a greater depth than this can experience contention with this type of access pattern, if the frequency of data manipulation language (DML) and concurrency of the system is high enough. システムで使用可能な CPU コアが 16 個以上あるときは、コンカレンシーが高くなるので、ラッチの競合のレベルが顕著になる場合があります。The level of latch contention may become pronounced as concurrency increases when 16 or more CPU cores are available to the system.

連続していない列がクラスター化インデックスの先頭キーであるときなど、アクセスが B ツリー全体でランダムであっても、ラッチの競合が発生する可能性があります。Latch contention can occur even if access is random across the B-tree such as when a non-sequential column is the leading key in a clustered index. 次のスクリーンショットは、この種類のラッチの競合が発生しているシステムのものです。The following screenshot is from a system experiencing this type of latch contention. この例では、行のサイズが小さく B ツリーが比較的浅いことによるページの密度が原因で、競合が発生しています。In this example, contention is due to the density of the pages caused by small row size and a relatively shallow B-tree. GUID がインデックスの先頭の列であるため、コンカレンシーが高くなると、挿入が B ツリー全体に対してランダムであっても、ページでラッチの競合が発生します。As concurrency increases, latch contention on pages occurs even though inserts are random across the B-tree since a GUID was the leading column in the index.

次のスクリーンショットでは、バッファー データ ページとページ空き領域 (PFS) ページの両方で、待機が発生しています。In the following screenshot, the waits occur on both buffer data pages and pages free space (PFS) pages. PFS ページのラッチの競合の詳細については、SQLSkills での次のサードパーティのブログ記事を参照してください: 「Benchmarking:Multiple data files on SSDs」(ベンチマーク: SSD 上の複数のデータ ファイル)。For more information about PFS page latch contention, see the following third-party blog post on SQLSkills: Benchmarking: Multiple data files on SSDs. データ ファイルの数が増えた場合でも、バッファー データ ページではラッチの競合が発生していました。Even when the number of data files was increased, latch contention was prevalent on buffer data pages.

待機の種類

次の表は、この種類のラッチの競合によって見られる主な要素をまとめたものです。The following table summarizes the major factors observed with this type of latch contention:

要素Factor 一般的な観測Typical observations
SQL Server によって使用されている論理 CPULogical CPUs in use by SQL Server ラッチの競合は、主に CPU コアが 16 個以上のコンピューターで発生します。Latch contention occurs mainly on computers with 16+ CPU cores.
スキーマの設計とアクセス パターンSchema Design and Access Patterns 小さいテーブルに対する高率の挿入、選択、更新、削除アクセス パターン。High rate of insert/select/update/delete access patterns against small tables.

浅い B ツリー (インデックスの深さが 2 または 3)。Shallow B-tree (index depth of two or three).

小さい行サイズ (1 ページあたりのレコード数が多い)。Small row size (many records per page).
コンカレンシーのレベルLevel of concurrency アプリケーション層からの同時要求のレベルが高い場合にのみ、ラッチの競合が発生します。Latch contention will occur only under high levels of concurrent requests from the application tier.
観察される待機の種類Wait type observed ルート分割のため、バッファー (PAGELATCH_EX と PAGELATCH_SH) と非バッファー ラッチ ACCESS_METHODS_HOBT_VIRTUAL_ROOT で待機が観察されます。Observe waits on buffer (PAGELATCH_EX and PAGELATCH_SH) and non-buffer latch ACCESS_METHODS_HOBT_VIRTUAL_ROOT due to root splits. また、PFS ページで PAGELATCH_UP が待機されます。Also PAGELATCH_UP waits on PFS pages. 非バッファー ラッチ待機の詳細については、SQL Server のヘルプで「sys.dm_os_latch_stats (Transact-SQL)」を参照してください。For more information about non-buffer latch waits, see sys.dm_os_latch_stats (Transact-SQL) in SQL Server help.

浅い B ツリーとインデックス全体へのランダムな挿入が組み合わさると、B ツリーでページ分割が発生しやすくなります。The combination of a shallow B-Tree and random inserts across the index is prone to causing page splits in the B-tree. SQL Server でページ分割を実行するには、すべてのレベルで共有 (SH) ラッチを取得した後、ページ分割に含まれる B ツリー内のページで排他 (EX) ラッチを取得する必要があります。In order to perform a page split, SQL Server must acquire shared (SH) latches at all levels, and then acquire exclusive (EX) latches on pages in the B-tree that are involved in the page splits. また、コンカレンシーが高く、データが継続的に挿入および削除される場合も、B ツリーのルート分割が発生する可能性があります。Also when concurrency is high and data is continually inserted and deleted, B-tree root splits may occur. この場合、他の挿入は、B ツリーで取得された非バッファー ラッチを待機することが必要になる場合があります。In this case, other inserts may have to wait for any non-buffer latches acquired on the B-tree. これは、sys.dm_os_latch_stats DMV で観察される ACCESS_METHODS_HBOT_VIRTUAL_ROOT ラッチの種類での多数の待機として示されます。This will be manifested as a large number of waits on the ACCESS_METHODS_HBOT_VIRTUAL_ROOT latch type observed in the sys.dm_os_latch_stats DMV.

次のスクリプトを変更して、影響を受けたテーブルでのインデックスの B ツリーの深さを確認できます。The following script can be modified to determine the depth of the B-tree for the indexes on the affected table.

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 i
join sysObjects 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) ページでのラッチの競合Latch contention on page free space (PFS) pages

PFS はページ空き領域 (Page Free Space) を表し、SQL Server により、各データベース ファイルの 8088 ページ (ページ ID = 1 以降) ごとに 1 つの PFS ページが割り当てられます。PFS stands for Page Free Space, SQL Server allocates one PFS page for every 8088 pages (starting with PageID = 1) in each database file. PFS ページの各バイトには、ページの空き領域の量、割り当てられているかどうか、ページにゴースト レコードが格納されているかどうかなどの情報が記録されます。Each byte in the PFS page records information including how much free space is on the page, if it is allocated or not and whether the page stores ghost records. PFS ページには、挿入操作または更新操作で新しいページが必要なときに割り当てに使用できるページに関する情報が含まれます。The PFS page contains information about the pages available for allocation when a new page is required by an insert or update operation. PFS ページは、割り当てや割り当て解除が発生したときなど、さまざまなシナリオで更新する必要があります。The PFS page must be updated in a number of scenarios, including when any allocations or de-allocations occur. PFS ページを保護するには更新 (UP) ラッチを使用する必要があるため、ファイル グループ内のデータ ファイルの数が比較的少なく、CPU コアの数が多い場合、PFS ページでラッチの競合が発生する可能性があります。Since the use of an update (UP) latch is required to protect the PFS page, latch contention on PFS pages can occur if you have relatively few data files in a filegroup and a large number of CPU cores. これを解決する簡単な方法は、ファイル グループごとのファイルの数を増やすことです。A simple way to resolve this is to increase the number of files per filegroup.

警告

ファイル グループごとのファイルの数を増やすと、メモリがディスクに書き込まれる多数の大きな並べ替え操作による負荷など、特定の負荷のパフォーマンスに悪影響を及ぼす可能性があります。Increasing the number of files per filegroup may adversely affect performance of certain loads, such as loads with many large sort operations which spill memory to disk.

tempdb の PFS ページまたは SGAM ページで PAGELATCH_UP 待機が多数発生する場合は、以下の手順のようにして、このボトルネックを解消します。If many PAGELATCH_UP waits are observed for PFS or SGAM pages in tempdb, complete these steps to eliminate this bottleneck:

  1. tempdb のデータ ファイルの数がサーバーのプロセッサ コアの数と同じになるように、データ ファイルを tempdb に追加します。Add data files to tempdb so that the number of tempdb data files is equal to the number of processor cores in your server.

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

システム ページでの競合によって発生する割り当てのボトルネックの詳細については、ブログ記事「割り当てのボトルネックとは」を参照してください。For more information about allocation bottlenecks caused by contention on system pages, see the blog post What is allocation bottleneck?.

tempdb でのテーブル値関数とラッチの競合Table-valued functions and latch contention on tempdb

割り当ての競合以外にも、クエリ内での TVF の多用など、tempdb でのラッチの競合の原因になる可能性がある他の要因があります。There are other factors beyond allocation contention that can cause latch contention on tempdb, such as heavy TVF use within queries.

さまざまなテーブル パターンに対するラッチの競合の処理Handling latch contention for different table patterns

以下のセクションでは、過度なラッチの競合に関連するパフォーマンスの問題に対処するために使用できる手法について説明します。The following sections describe techniques that can be used to address or work around performance issues related to excessive latch contention.

連続していない先頭のインデックス キーを使用するUse a non-sequential leading index key

ラッチの競合を処理する方法のひとつは、連続したインデックス キーを連番でないキーに置き換えて、インデックスの範囲に挿入を均等に配置することです。One method for handling latch contention is to replace a sequential index key with a non-sequential key to evenly distribute inserts across an index range.

これは通常、ワークロードを比例的に配分する先頭列をインデックスに設けることによって実行します。Typically this is done by having a leading column in the index that will distribute the workload proportionally. これには、次の 2 つのオプションがあります。There are a couple of options here:

オプション:テーブル内の列を使用して、インデックス キーの範囲全体に値を分散させるOption: Use a column within the table to distribute values across the index key range

キーの範囲に挿入を分散させるために使用できる自然な値でワークロードを評価します。Evaluate your workload for a natural value that can be used to distribute inserts across the key range. たとえば、ATM バンキングのシナリオを考えると、1 人の顧客が一度に使用できる ATM は 1 台だけなので、引き出しの場合に挿入をトランザクション テーブルに分散させるには、ATM_ID がよい候補になる可能性があります。For example, consider an ATM banking scenario where ATM_ID may be a good candidate to distribute inserts into a transaction table for withdrawals since one customer can only use one ATM at a time. 同様に、販売時点管理システムの場合は、おそらく Checkout_ID や Store ID が、キーの範囲に挿入を分散させるために使用できる自然な値になります。Similarly in a point of sales system, perhaps Checkout_ID or a Store ID would be a natural value that could be used to distribute inserts across a key range. この手法を使用するには、一意性を提供するために、列を識別する値またはその値のハッシュである先頭のキー列と、1 つ以上の追加列を結合することによって、複合インデックス キーを作成する必要があります。This technique requires creating a composite index key with the leading key column being either the value of the column identified or some hash of that value combined with one or more additional columns to provide uniqueness. ほとんどの場合、個別の値が多すぎると物理的な編成が不十分になるため、値のハッシュを使用するのが最善です。In most cases, a hash of the value will work best because too many distinct values will result in poor physical organization. たとえば、販売時点管理システムの場合は、何らかの剰余 (これは、CPU コアの数に合わせます) である Store ID からハッシュを作成できます。For example, in a point of sales system, a hash can be created from the Store ID that is some modulo, which aligns with the number of CPU cores. この手法を使用すると、テーブル内の範囲の数は比較的少なくなりますが、ラッチの競合が回避されるように挿入を分散させるには十分です。This technique would result in a relatively small number of ranges within the table however it would be enough to distribute inserts in such a way to avoid latch contention. 次の図は、この手法を示したものです。The following image illustrates this technique.

連続していないインデックスを適用した後の挿入

重要

このパターンは、従来のインデックス作成のベスト プラクティスとは矛盾しています。This pattern contradicts traditional indexing best practices. この手法を使用すると、B ツリー全体に挿入を均等に分散させることはできますが、アプリケーション レベルでのスキーマの変更が必要になる場合もあります。While this technique will help ensure uniform distribution of inserts across the B-tree, it may also necessitate a schema change at the application level. さらに、このパターンの場合、クラスター化インデックスを利用する範囲スキャンが必要なクエリのパフォーマンスに悪影響を与える可能性があります。In addition, this pattern may negatively impact performance of queries which require range scans that utilize the clustered index. この設計アプローチが適切に機能するかどうかを判断するため、ワークロード パターンの分析が必要になります。Some analysis of the workload patterns will be required to determine if this design approach will work well. 挿入のスループットとスケールを得るために、逐次スキャンのパフォーマンスをある程度犠牲にできる場合は、このパターンを実装する必要があります。This pattern should be implemented if you are able to sacrifice some sequential scan performance to gain insert throughput and scale.

このパターンをパフォーマンス ラボのエンゲージメントの間に実装したところ、32 個の物理 CPU コアを搭載したシステムでラッチの競合が解決されました。This pattern was implemented during a performance lab engagement and resolved latch contention on a system with 32 physical CPU cores. テーブルは、トランザクション終了時の残高を格納するために使用されました。各ビジネス トランザクションで、テーブルへの 1 回の挿入が実行されました。The table was used to store the closing balance at the end of a transaction; each business transaction performed a single insert into the table.

元のテーブルの定義Original table definition

元のテーブルの定義を使用すると、クラスター化インデックス pk_table1 で過剰なラッチの競合が発生することが観察されました。When using the original table definition, excessive latch contention was observed to occur on the clustered index 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

注意

テーブル定義内のオブジェクト名が、元の値から変更されています。The object names in the table definition have been changed from their original values.

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

主キーの先頭列として UserID を使用するインデックスを並べ替えると、挿入はページ間にほぼランダムに分散されました。Re-ordering the index with UserID as the leading column in the primary key provided an almost random distribution of inserts across the pages. すべてのユーザーが同時にオンラインになるわけではないため、結果の分散は 100% ランダムではありませんが、過剰なラッチの競合を軽減するには十分にランダムな分散になりました。The resulting distribution was not 100% random since not all users are online at the same time, but the distribution was random enough to alleviate excessive latch contention. インデックス定義の並べ替えに関する注意点の 1 つとして、このテーブルに対する選択クエリを、等値述語として UserID と TransactionID の両方を使用するように変更する必要があります。One caveat of reordering the index definition is that any select queries against this table must be modified to use both UserID and TransactionID as equality predicates.

重要

運用環境で実行する前に、テスト環境で変更を徹底的にテストする必要があります。Ensure that you thoroughly test any changes in a test environment before running in a production environment.

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

主キーの先頭列としてハッシュ値を使用するUsing a hash value as the leading column in primary key

次のテーブルの定義を使用すると、CPU の数と一致する剰余を生成できます。B ツリー全体に均等に分散されるよう、HashValue は連続して増加する値 TransactionID を使用して生成されます。The following table definition can be used to generate a modulo that aligns to the number of CPUs, HashValue is generated using the sequentially increasing value TransactionID to ensure a uniform distribution across the B-Tree:

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 を使用するOption: Use a GUID as the leading key column of the index

自然な区切りがない場合は、GUID 列をインデックスの先頭のキー列として使用して、挿入を均等に分散させることができます。If there is no natural separator, then a GUID column can be used as a leading key column of the index to ensure uniform distribution of inserts. インデックス キーの先頭列として GUID を使用すると、他の機能に対してパーティション分割を使用できるようになりますが、この手法を使用すると、より多くのページ分割、物理的な編成の不備、低ページ密度による潜在的な欠点が生じる可能性もあります。While using the GUID as the leading column in the index key approach enables use of partitioning for other features, this technique can also introduce potential downsides of more page-splits, poor physical organization and low page densities.

注意

インデックスの先頭キー列としての GUID の使用は、盛んに議論されたテーマです。The use of GUIDs as leading key columns of indexes is a highly debated subject. この方法の長所と短所の詳細については、この記事の範囲外です。An in-depth discussion of the pros and cons of this method falls outside the scope of this article.

計算列でハッシュ パーティション分割を使用するUse hash partitioning with a computed column

SQL Server でテーブルのパーティション分割を使用すると、過度なラッチの競合を軽減できます。Table partitioning within SQL Server can be used to mitigate excessive latch contention. パーティション テーブルの計算列でハッシュ パーティション分割の構成を作成することは、次の手順で実行できる一般的な方法です。Creating a hash partitioning scheme with a computed column on a partitioned table is a common approach that can be accomplished with these steps:

  1. 新しいファイル グループを作成するか、既存のファイル グループを使用して、パーティションを保持します。Create a new filegroup or use an existing filegroup to hold the partitions.

  2. 新しいファイル グループを使用する場合は、最適なレイアウトを使用するように注意して、LUN に対して個別のファイルを均等に分散させます。If using a new filegroup, equally balance individual files over the LUN, taking care to use an optimal layout. 挿入の割合が高いアクセス パターンの場合は、SQL Server コンピューターの物理 CPU コアと同じ数のファイルを作成するようにします。If the access pattern involves a high rate of inserts, make sure to create the same number of files as there are physical CPU cores on the SQL Server computer.

  3. CREATE PARTITION FUNCTION コマンドを使用して、テーブルを X 個のパーティションにパーティション分割します。X は SQL Server コンピューター上の物理 CPU コアの数です。Use the CREATE PARTITION FUNCTION command to partition the tables into X partitions, where X is the number of physical CPU cores on the SQL Server computer. (少なくとも 32 個のパーティション)(at least up to 32 partitions)

    注意

    CPU コアの数に対してパーティションの数を 1:1 に配置することは、必ずしも必要ではありません。A 1:1 alignment of the number of partitions to the number of CPU cores is not always necessary. 多くの場合、これは CPU コアの数より小さい値にすることができます。In many cases this can be some value less than the number of CPU cores. パーティションを増やすと、すべてのパーティションを検索する必要があるクエリでオーバーヘッドが増加する可能性があります。このような場合は、パーティションを減らすのが有効です。Having more partitions can result in more overhead for queries which have to search all partitions and in these cases fewer partitions will help. 実際の顧客ワークロードを使用した 64 および 128 個の論理 CPU システムによる SQLCAT テストの場合、過剰なラッチの競合を解決してスケール ターゲットを達成するのに、32 個のパーティションで十分でした。In SQLCAT testing on 64 and 128 logical CPU systems with real customer workloads 32 partitions has been sufficient to resolve excessive latch contention and reach scale targets. 最終的に、パーティションの最適な数はテストによって決定する必要があります。Ultimately the ideal number of partitions should be determined through testing.

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

    • パーティション関数をファイル グループにバインドします。Bind the partition function to the filegroups.
    • tinyint 型または smallint 型のハッシュ列をテーブルに追加します。Add a hash column of type tinyint or smallint to the table.
    • 適切なハッシュ分散を計算します。Calculate a good hash distribution. たとえば、hashbytes と剰余または binary_checksum を使用します。For example, use hashbytes with modulo or binary_checksum.

次のサンプル スクリプトをカスタマイズして実装に利用できます。The following sample script can be customized for purposes of your implementation:

--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)

このスクリプトを使用すると、最後のページまたは後続のページでの挿入の競合によって引き起こされる問題が発生しているテーブルをハッシュ パーティションできます。This script can be used to hash partition a table that is experiencing problems caused by Last page/trailing page insert contention. この手法を使用すると、テーブルがパーティション分割され、ハッシュ値剰余演算でテーブル パーティション全体に挿入が分散されることによって、最後のページから競合が移動されます。This technique moves contention from the last page by partitioning the table and distributing inserts across table partitions with a hash value modulus operation.

計算列でのハッシュ パーティション分割によって行われることWhat hash partitioning with a computed column does

次の図に示すように、この手法を使用すると、ハッシュ関数でインデックスが再構築され、SQL Server コンピューター上の物理 CPU コアと同じ数のパーティションが作成されることによって、最後のページから競合が移動されます。As the following diagram illustrates, this technique moves the contention from the last page by rebuilding the index on the hash function and creating the same number of partitions as there are physical CPU cores on the SQL Server computer. 挿入は引き続き論理範囲 (順番に増加する値) の最後に送られますが、ハッシュ値の剰余演算によって、挿入が異なる B ツリーに分割されることが保証されるため、ボトルネックは軽減されます。The inserts are still going into the end of the logical range (a sequentially increasing value) but the hash value modulus operation ensures that the inserts are split across the different B-trees, which alleviates the bottleneck. 次の図はこれを示したものです。This is illustrated in the following diagrams:

最後のページへの挿入からのページ ラッチの競合

パーティション分割で解決されたページ ラッチの競合

ハッシュ パーティション分割を使用するときのトレードオフTrade-offs when using hash partitioning

ハッシュ パーティション分割によって挿入での競合を解消できますが、この手法を使用するかどうかを決定するときは、いくつかのトレードオフについて検討する必要があります。While hash partitioning can eliminate contention on inserts, there are several trade-offs to consider when deciding whether or not to use this technique:

  • 選択クエリは、ほとんどの場合、述語にハッシュ パーティションが含まれ、これらのクエリの発行でパーティションが除去されないようなクエリ プランになるように変更する必要があります。Select queries will in most cases need to be modified to include the hash partition in the predicate and lead to a query plan that provides no partition elimination when these queries are issued. 次のスクリーンショットでは、ハッシュ パーティション分割が実装された後でパーティションの除去が行われない不適切なプランを示します。The following screenshot shows a bad plan with no partition elimination after hash partitioning has been implemented.

    パーティションが除去されないクエリ プラン

  • 範囲ベースのレポートなど、他の特定のクエリでのパーティションの除去の可能性はなくなります。It eliminates the possibility of partition elimination on certain other queries, such as range-based reports.

  • ハッシュ パーティション分割されたテーブルを別のテーブルに結合するとき、パーティションの除去を実現するには、2 番目のテーブルを同じキーでハッシュ パーティション分割し、ハッシュ キーを結合条件の一部にする必要があります。When joining a hash partitioned table to another table, to achieve partition elimination the second table will need to be hash partitioned on the same key and the hash key should be part of the join criteria.

  • ハッシュ パーティション分割を使用すると、スライディング ウィンドウ アーカイブやパーティション切り替え機能などの他の管理機能で、パーティション分割を使用できません。Hash partitioning prevents the use of partitioning for other management features such as sliding window archiving and partition switch functionality.

ハッシュ パーティション分割は、挿入時の競合が軽減されることでシステム全体のスループットが向上するため、過剰なラッチの競合を軽減するための効果的な方法です。Hash partitioning is an effective strategy for mitigating excessive latch contention as it does increase overall system throughput by alleviating contention on inserts. いくつかのトレードオフがあるため、一部のアクセス パターンに対しては最適な解決策でない可能性があります。Because there are some trade-offs involved, it may not be the optimal solution for some access patterns.

ラッチの競合の解決に使用される手法のまとめSummary of techniques used to address latch contention

次の 2 つのセクションでは、過剰なラッチの競合に対処するために使用できる手法をまとめます。The following two sections provide a summary of the techniques that can be used to address excessive latch contention:

連続していないキーとインデックスNon-sequential key/index

長所:Advantages:

  • スライディング ウィンドウ スキームやパーティション切り替え機能を使用するデータのアーカイブなど、他のパーティション分割機能を使用できます。Allows the use of other partitioning features, such as archiving data using a sliding window scheme and partition switch functionality.

短所:Disadvantages:

  • キーまたはインデックスを選択して常に挿入の分散を "ほぼ" 均等にするときに問題が発生する可能性があります。Possible challenges when choosing a key/index to ensure 'close enough to' uniform distribution of inserts all of the time.
  • GUID を先頭列として使用することで均等な分散を保証できますが、ページ分割操作が過剰になる可能性があることに注意します。GUID as a leading column can be used to guarantee uniform distribution with the caveat that it can result in excessive page-split operations.
  • B ツリー全体にランダムに挿入すると、ページ分割操作が多くなり、非リーフ ページでラッチの競合が発生する可能性があります。Random inserts across B-Tree can result in too many page-split operations and lead to latch contention on non-leaf pages.

計算列を用いたハッシュ パーティション分割Hash partitioning with computed column

長所:Advantages:

  • 挿入に対して透過的。Transparent for inserts.

短所:Disadvantages:

  • パーティション分割は、パーティション切り替えオプションを使用したデータのアーカイブなどの管理機能には使用できません。Partitioning cannot be used for intended management features such as archiving data using partition switch options.
  • 個別および範囲ベースの選択や更新を含むクエリ、および結合を実行するクエリの場合、パーティション除去の問題が発生する可能性があります。Can cause partition elimination issues for queries including individual and range-based select/update, and queries that perform a join.
  • 保存される計算列の追加はオフライン操作です。Adding a persisted computed column is an offline operation.

ヒント

その他の手法については、ブログ記事「PAGELATCH_EX の待機と大量の挿入」を参照してください。For additional techniques, see the blog post PAGELATCH_EX waits and heavy inserts.

チュートリアル:ラッチの競合を診断するWalkthrough: Diagnose a latch contention

次のチュートリアルでは、「SQL Server のラッチの競合の診断」と「さまざまなテーブル パターンに対するラッチの競合の処理」で説明されているツールと手法を使用して、実際のシナリオで問題を解決する方法を示します。The following walkthrough demonstrates the tools and techniques described in Diagnosing SQL Server Latch Contention and Handling Latch Contention for Different Table Patterns to resolve a problem in a real world scenario. このシナリオでは、256 GB のメモリを搭載した 8 ソケット、32 物理コアのシステムで実行されている SQL Server アプリケーションに対する約 8,000 の店のトランザクションをシミュレートする、販売時点管理システムのロード テストを実行する顧客エンゲージメントについて説明します。This scenario describes a customer engagement to perform load testing of a point of sales system which simulated approximately 8,000 stores performing transactions against a SQL Server application which was running on an 8 socket, 32 physical core system with 256 GB of memory.

次の図は、販売時点管理システムのテストに使用されるハードウェアの詳細を示したものです。The following diagram details the hardware used to test the point of sales system:

販売時点管理システムのテスト環境

症状:ホット ラッチSymptom: Hot latches

この例では、普通平均 1 ミリ秒を超えるものとして高く定義される PAGELATCH_EX に対して高い待機が観察されました。In this case, we observed high waits for PAGELATCH_EX where we typically define high as an average of more than 1 ms. この例では、20 ミリ秒を超える待機を常に観察しました。In this case, we consistently observed waits exceeding 20 ms.

ホット ラッチ

ラッチの競合が問題であると判断した後、ラッチの競合の原因の特定を始めました。Once we determined that latch contention was problematic, we then set out to determine what was causing the latch contention.

ラッチの競合の原因となったオブジェクトの分離Isolating the object causing latch contention

次のスクリプトを使用すると、resource_description 列を使用して、PAGELATCH_EX 競合の原因となったインデックスが分離されます。The following script uses the resource_description column to isolate which index was causing the PAGELATCH_EX contention:

注意

このスクリプトによって返される resource_description 列により、<DatabaseID,FileID,PageID> の形式でリソースの説明が提供されます。DatabaseID に関連付けられているデータベースの名前は、DatabaseID の値を DB_NAME () 関数に渡すことによって確認できます。The resource_description column returned by this script provides the resource description in the format <DatabaseID,FileID,PageID> where the name of the database associated with DatabaseID can be determined by passing the value of DatabaseID to the DB_NAME () function.

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 bd 
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 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))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id 
JOIN sys.schemas s ON o.schema_id = s.schema_id
order by wt.wait_duration_ms desc

ここで示すように、競合はテーブル LATCHTEST およびインデックス名 CIX_LATCHTEST で発生しています。As shown here, the contention is on the table LATCHTEST and index name CIX_LATCHTEST. ワークロードを匿名にするため、名前は変更されていることに注意してください。Note names have been changed to anonymize the workload.

LATCHTEST の競合

繰り返しポーリングを行い、一時テーブルを使用して構成可能な期間に対する合計待機時間を決定する、さらに高度なスクリプトについては、「付録」の「バッファー記述子のクエリを実行して、ラッチ競合の原因となっているオブジェクトを特定する」を参照してください。For a more advanced script that polls repeatedly and uses a temporary table to determine the total waiting time over a configurable period see Query Buffer Descriptors to Determine Objects Causing Latch Contention in the Appendix.

ラッチ競合の原因となるオブジェクトを分離する別の方法Alternative technique to isolate the object causing latch contention

sys.dm_os_buffer_descriptors のクエリを実行することが実用的でない場合があります。Sometimes it can be impractical to query sys.dm_os_buffer_descriptors. バッファー プールに使用できるシステムのメモリが増えると、この DMV を実行するために必要な時間も長くなります。As the memory in the system, and available to the buffer pool increases so does the time required to run this DMV. 256 GB のシステムでは、この DMV を実行するのに最大で 10 分、またはそれ以上かかる場合があります。On a 256 GB system, it may take up to 10 minutes or more for this DMV to run. 以下では、使用可能な別の手法を、ラボで別のワークロードを使用して実行した場合の概要を説明します。An alternative technique is available and is broadly outlined as follows and is illustrated with a different workload, which we ran in the lab:

  1. 付録の「sys.dm_os_waiting_tasks のクエリを実行して待機時間の順に並べ替える」のスクリプトを使用して、現在待機中のタスクのクエリを実行します。Query current waiting tasks, using the Appendix script Query sys.dm_os_waiting_tasks Ordered by Wait Duration.

  2. コンボイが観察されているキー ページを特定します。これは、複数のスレッドが同じページで競合している場合に発生します。Identify the key page where a convoy is observed, which happens when multiple threads are contending on the same page. この例では、挿入を実行しているスレッドが B ツリーの末尾のページで競合しており、EX ラッチを取得できるまで待機します。In this example, the threads performing the insert are contending on the trailing page in the B-tree and will wait until they can acquire an EX latch. これは、最初のクエリの resource_description によって示されます (この例では 8:1:111305)。This is indicated by the resource_description in the first query, in our case 8:1:111305.

  3. トレース フラグ 3604 を有効にします。これにより、次の構文を使用して DBCC PAGE によりページに関する詳細情報が公開されます。かっこ内の値を、resource_description で取得した値に置き換えます。Enable trace flag 3604, which exposes further information about the page via DBCC PAGE with the following syntax, substitute the value you obtained via the resource_description for the value in parentheses:

    --enable trace flag 3604 to enable console output
    dbcc traceon (3604)
    
    --examine the details of the page
    dbcc page (8,1, 111305, -1)
    
  4. DBCC の出力を調べます。Examine the DBCC output. これらは、関連付けられたメタデータ ObjectID である必要があります (この例では "78623323")。There should be an associated Metadata ObjectID, in our case '78623323'.

    メタデータ ObjectID

  5. これで、次のコマンドを実行して、競合の原因になっているオブジェクトの名前を特定できます。これは LATCHTEST と予想されます。We can now run the following command to determine the name of the object causing the contention, which as expected is LATCHTEST.

    注意

    データベース コンテキストが正しいことを確認します。そうでない場合は、クエリから NULL が返されます。Ensure you are in the correct database context otherwise the query will return NULL.

    --get object name
    select OBJECT_NAME (78623323)
    

    オブジェクト名

まとめと結果Summary and results

上記の手法を使用することで、最も多くの挿入を受け取ったテーブルの、順番に増加するキー値のクラスター化インデックスで、競合が発生していることを確認できました。Using the technique above we were able to confirm that the contention was occurring on a clustered index with a sequentially increasing key value on the table which by far received the highest number of inserts. この種の競合は、日時、ID、アプリケーションで生成されたトランザクション ID など、キー値が順番に増加するインデックスでは一般的ではありません。This type of contention is not uncommon for indexes with a sequentially increasing key value such as datetime, identity or an application-generated transactionID.

この問題を解決するため、計算列でハッシュ パーティション分割を使用し、パフォーマンスが 690% 向上することを観察しました。To resolve this issue, we used hash partitioning with a computed column and observed a 690% performance improvement. 次の表は、計算列でハッシュ パーティション分割を実装する前と後の、アプリケーションのパフォーマンスをまとめたものです。The following table summarizes the performance of the application before and after implementing hash partitioning with a computed column. ラッチの競合のボトルネックが解消された後、CPU の使用率は、予想どおりスループットと共に大きくなります。The CPU utilization increases broadly in line with throughput as expected after the latch contention bottleneck was removed:

MeasurementMeasurement ハッシュ パーティション分割の前Before hash partitioning ハッシュ パーティション分割の後After hash partitioning
ビジネス トランザクション数/秒Business Transactions/Sec 3636 249249
ページ ラッチの平均待機時間Average Page Latch Wait Time 36 ミリ秒36 milliseconds 0.6 ミリ秒0.6 milliseconds
ラッチ待機数/秒Latch Waits/Sec 9,5629,562 2,8732,873
SQL プロセッサ時間SQL Processor Time 24%24% 78%78%
SQL バッチ要求数/秒SQL Batch Requests/sec 12,36812,368 47,04547,045

上の表からわかるように、ページ ラッチの過剰な競合によって発生するパフォーマンスの問題を正しく識別して解決すると、アプリケーション全体のパフォーマンスによい影響がある可能性があります。As can be seen from the table above, correctly identifying and resolving performance issues caused by excessive page latch contention can have a positive impact on overall application performance.

付録: 別の方法Appendix: Alternate technique

ページ ラッチの過剰な競合を回避するために可能な方法の 1 つは、CHAR 型の列を含む行を埋め込むことにより、各行でページ全体が使用されるようにすることです。One possible strategy for avoiding excessive page latch contention is to pad rows with a CHAR column to ensure that each row will use a full page. この方法は、データ全体のサイズが小さく、次の要素の組み合わせによって発生する EX ページ ラッチの競合を解決する必要がある場合に選択できます。This strategy is an option when the overall data size is small and you need to address EX page latch contention caused by the following combination of factors:

  • 小さい行サイズSmall row size
  • 浅い B ツリーShallow B-tree
  • ランダムな挿入、選択、更新、削除操作の割合が高いアクセス パターンAccess pattern with a high rate of random insert, select, update, and delete operations
  • 小さいテーブル (一時キュー テーブルなど)Small tables, such as temporary queue tables

ページ全体を占める行を埋め込むことにより、追加のページを割り当てるよう SQL に要求し、挿入に使用できるページ数を増やして、EX ページ ラッチの競合を減らします。By padding rows to occupy a full page you require SQL to allocate additional pages, making more pages available for inserts and reducing EX page latch contention.

各行がページ全体を占めるように行を埋め込むPadding rows to ensure each row occupies a full page

行を埋め込んでページ全体を占めるには、次のようなスクリプトを使用できます。A script similar to the following can be used to pad rows to occupy an entire page:

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

注意

値の埋め込みに必要な余分な CPU と、行をログに記録するために必要な追加領域を減らすため、可能な限り少ない文字数を使用して、強制的に 1 行を 1 ページにします。Use the smallest char possible that forces one row per page to reduce the extra CPU requirements for the padding value and the extra space required to log the row. ハイ パフォーマンスのシステムでは、すべてのバイトがカウントされます。Every byte counts in a high performance system.

完全を期すため、この手法について説明します。実際の SQLCAT においては、1 回のパフォーマンス エンゲージメントが 10,000 行の小さいテーブルでのみ、これを使用しました。This technique is explained for completeness; in practice SQLCAT has only used this on a small table with 10,000 rows in a single performance engagement. 大きなテーブルの場合に SQL Server でのメモリ負荷が増加し、非リーフ ページで非バッファー ラッチの競合が発生する可能性があるため、この手法の適用には制限があります。This technique has a limited application due to the fact that it increases memory pressure on SQL Server for large tables and can result in non-buffer latch contention on non-leaf pages. メモリ負荷の増加は、この手法の適用に対する大きな制限要因になることがあります。The additional memory pressure can be a significant limiting factor for application of this technique. 最新のサーバーでは使用可能なメモリ量が多いので、通常、OLTP ワークロードのワーキング セットの大部分はメモリに保持されます。With the amount of memory available in a modern server, a large proportion of the working set for OLTP workloads is typically held in memory. データ セットがメモリに収まらないほど大きいサイズになると、パフォーマンスの大幅な低下が発生します。When the data set increases to a size that it no longer fits in memory a significant drop-off in performance will occur. したがって、この手法は、小さなテーブルに対してのみ適用できます。Therefore, this technique is something that is only applicable to small tables. 大きいテーブルに対する最後のページまたは後続のページでの挿入の競合などのシナリオの場合、この手法は SQLCAT では使用されません。This technique is not used by SQLCAT for scenarios such as last page/trailing page insert contention for large tables.

重要

この方法を採用すると、B ツリーの非リーフ レベルで大量のページ分割が発生する場合があるため、ACCESS_METHODS_HBOT_VIRTUAL_ROOT のラッチの種類で多数の待機が発生する可能性があります。Employing this strategy can cause a large number of waits on the ACCESS_METHODS_HBOT_VIRTUAL_ROOT latch type because this strategy can lead to a large number of page splits occurring in the non-leaf levels of the B-tree. SQL Server でこのようなことが発生した場合は、すべてのレベルで共有 (SH) ラッチを取得した後、ページ分割の可能性がある B ツリー内のページで、排他 (EX) ラッチを取得する必要があります。If this occurs, SQL Server must acquire shared (SH) latches at all levels followed by exclusive (EX) latches on pages in the B-tree where a page split is possible. 行を埋め込んだ後、ACCESS_METHODS_HBOT_VIRTUAL_ROOT のラッチの種類で待機が多いかどうか、sys.dm_os_latch_stats DMV を確認します。Check the sys.dm_os_latch_stats DMV for a high number of waits on the ACCESS_METHODS_HBOT_VIRTUAL_ROOT latch type after padding rows.

付録: SQL Server ラッチ競合スクリプトAppendix: SQL Server latch contention scripts

このセクションでは、ラッチの競合の問題を診断およびトラブルシューティングするために使用できるスクリプトを示します。This section contains scripts which can be used to help diagnose and troubleshoot latch contention issues.

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

次のサンプル スクリプトを使用すると、sys.dm_os_waiting_tasks のクエリが実行され、セッション ID の順に並べ替えられたラッチ待機が返されます。The following sample script will query sys.dm_os_waiting_tasks and return latch waits ordered by session 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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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 のクエリを実行して待機時間の順に並べ替えるQuery sys.dm_os_waiting_tasks ordered by wait duration

次のサンプル スクリプトを使用すると、sys.dm_os_waiting_tasks のクエリが実行され、待機時間の順に並べ替えられたラッチ待機が返されます。The following sample script will query sys.dm_os_waiting_tasks and return latch waits ordered by wait duration:

-- 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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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

一定期間の待機時間を計算するCalculate waits over a time period

次のスクリプトを使用すると、一定期間におけるラッチ待機が計算されて返されます。The following script calculates and returns latch waits over a time period.

/* 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 datetime
declare @previous_snap_time 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 e
   inner join (
      select * from #_wait_stats 
         where snap_time = @previous_snap_time 
      ) 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

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

次のスクリプトを使用すると、バッファー記述子のクエリが実行されて、最も長いラッチ待機時間に関連付けられているオブジェクトが特定されます。The following script queries buffer descriptors to determine which objects are associated with the longest latch wait times.

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 varchar(16), @Counter INT, @MaxCount INT, @Counter2 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 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 wt
      JOIN sys.dm_os_buffer_descriptors 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
      JOIN sys.allocation_units au ON bd.allocation_unit_id = AU.allocation_unit_id
      JOIN sys.partitions p ON au.container_id = p.partition_id
      JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
      JOIN sys.objects o ON i.object_id = o.object_id
      JOIN sys.schemas 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;

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

このスクリプトの使用方法については、「計算列でハッシュ パーティション分割を使用する」で説明されており、実装するときはカスタマイズする必要があります。The use of this script is described in Use Hash Partitioning with a Computed Column and should be customized for purposes of your implementation.

--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)

次のステップNext steps

パフォーマンスの監視ツールの詳細については、「パフォーマンス監視およびチューニング ツール」を参照してください。For more information on performance monitoring tools, see Performance Monitoring and Tuning Tools.