SQL Server でのスピンロックの競合を診断して解決する

この記事では、コンカレンシーの高いシステム上の SQL Server アプリケーションでのスピンロックの競合に関連する問題を特定して解決する方法について詳しく説明します。

バックグラウンド

以前は、コモディティ Windows Server コンピューターは 1 つまたは 2 つのマイクロプロセッサ/CPU チップのみを使用しており、CPU は 1 つのプロセッサまたは "コア" のみで設計されていました。 コンピューター処理能力の向上は、トランジスタ密度の進歩を通じて、より高速なCPUを使用することによって達成されています。 1971年に汎用シングルチップCPUが初めて開発されて以来、トランジスタ密度や集積回路上に配置できるトランジスタの数は、2年ごとに一貫して2倍に増加しています。 近年、より高速なCPUでコンピュータ処理能力を増加させる従来のアプローチは、複数のCPUを持つコンピュータを構築することによって強化されています。 この執筆時点で、Intel Nehalem CPU アーキテクチャは CPU あたり最大 8 コアを収容します。これは、8 つのソケット システムで使用する場合、同時マルチスレッド (SMT) テクノロジを使用して 128 個の論理プロセッサに 2 倍にすることができます。 Intel CPU では、SMT は Hyper-Threading と呼ばれます。 x86 互換コンピューター上の論理プロセッサの数が増えると、論理プロセッサがリソースを競合するにつれて、コンカレンシー関連の問題が増加します。 このガイドでは、一部のワークロードで高コンカレンシー システムで SQL Server アプリケーションを実行するときに発生する特定のリソース競合の問題を特定して解決する方法について説明します。

このセクションでは、スピンロック競合の問題の診断と解決から SQLCAT チームが学んだ教訓を分析します。 スピンロックの競合は、大規模なシステム上の実際の顧客ワークロードで発生するコンカレンシーの問題の 1 つです。

スピンロックの競合の症状と原因

このセクションでは、 スピンロックの競合に関する問題を診断する方法について説明します。これは、SQL Server 上の OLTP アプリケーションのパフォーマンスに悪影響を与えます。 スピンロックの診断とトラブルシューティングは、デバッグ ツールと Windows 内部の知識が必要な高度な対象と見なす必要があります。

スピンロックは、データ構造へのアクセスを保護するために使用される軽量の同期プリミティブです。 スピンロックは SQL Server に固有ではありません。 オペレーティング システムでは、特定のデータ構造へのアクセスが短時間しか必要でない場合に使用されます。 スピンロックを取得しようとしているスレッドがアクセスできない場合、すぐに譲る代わりに、リソースが使用可能かどうかを定期的に確認しながらループ内で実行されます。 しばらくすると、スピンロックを待機しているスレッドは、リソースを取得できるようになる前に制御を手放します。 Yielding を使用すると、同じ CPU 上で実行されている他のスレッドを実行できます。 この動作はバックオフと呼ばれ、この記事の後半で詳しく説明します。

SQL Server では、スピンロックを使用して、内部データ構造の一部へのアクセスを保護します。 スピンロックは、ラッチと同様の方法で特定のデータ構造へのアクセスをシリアル化するためにエンジン内で使用されます。 ラッチとスピンロックの主な違いは、スピンロックがデータ構造の可用性を確かめつつループを実行し続けるのに対し、ラッチによって保護された構造体へのアクセスを得ようとするスレッドは、リソースが使用できない場合にすぐに処理を譲る点です。 スレッドのイールドには、CPUからスレッドをコンテキスト切り替えして、別のスレッドが実行できるようにする必要があります。 これは比較的負荷の高い操作であり、短期間保持されているリソースの場合は、リソースの可用性を定期的にチェックするループでスレッドを実行できるようにする方が全体的に効率的です。

SQL Server 2022 (16.x) で導入されたデータベース エンジンの内部調整により、スピンロックの効率が向上します。

症状

忙しい高並列システムでは、スピンロックで保護されている頻繁にアクセスされる構造体において、活発な競合が発生するのが普通です。 この使用は、競合によって CPU オーバーヘッドが大幅に発生する場合にのみ問題があると見なされます。 スピンロック統計は、SQL Server 内の sys.dm_os_spinlock_stats 動的管理ビュー (DMV) によって公開されます。 たとえば、このクエリでは次の出力が生成されます。

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

このクエリによって公開される統計は、次のように記述されます。

この説明の目的上、特に関心のある統計は、システムの負荷が高い場合に特定の期間内に発生する衝突、スピン、バックオフ イベントの数です。 スレッドがスピンロックによって保護されたリソースにアクセスしようとすると、競合が発生します。 競合が発生すると、競合カウントがインクリメントされ、スレッドがループ内でスピンし始め、リソースが使用可能かどうかを定期的に確認します。 スレッドがスピン (ループ) するたびに、スピンカウントがインクリメントされます。

衝突ごとのスピンは、スピンロックがスレッドによって保持されている間に発生するスピンの量の尺度であり、スレッドがスピンロックを保持している間に発生しているスピンの数を示します。 たとえば、衝突あたりのスピンが小さく、衝突回数が多い場合は、スピンロックの下で発生するスピンの量が少なく、競合するスレッドが多数存在することを意味します。 スピンの量が多いということは、スピンロック コードでスピンに費やされた時間が比較的長い有効期間を意味します (つまり、コードはハッシュ バケット内の多数のエントリを通過します)。 競合が増加する (したがって衝突数が増える) と、スピンの数も増加します。

バックオフは、スピンと同様の方法で考えることができます。 設計上、CPU の過剰な無駄を避けるために、スピンロックは保持されているリソースにアクセスできるようになるまで無期限にスピンを続けられません。 スピンロックが CPU リソースを過度に使用しないようにするには、スピンロックをオフにするか、スピンと "スリープ" を停止します。 スピンロックは、ターゲット リソースの所有権を取得したかどうかに関係なく、バックオフされます。 これにより、生産性の高い作業が可能になることを期待して、CPU で他のスレッドをスケジュールできます。 エンジンの既定の動作では、バックオフを実行する前に、最初に一定の時間間隔でスピンします。 スピンロックを取得するには、キャッシュコンカレンシーの状態が維持されている必要があります。これは、スピンの CPU コストに対する CPU 負荷の高い操作です。 したがって、スピンロックの取得の試行は、スレッドがスピンするたびに実行されるのではなく、控えめに実行されます。 SQL Server では、スピンロックを取得する試行の間隔が指数関数的に増加し (一定の制限まで)、CPU パフォーマンスへの影響が少なくなっているため、特定のスピンロックの種類 (たとえば、 LOCK_HASH) が改善されました。

次の図は、スピンロック アルゴリズムの概念図を示しています。

一般的なシナリオ

スピンロックの競合は、データベースの設計上の決定とは無関係なさまざまな理由で発生する可能性があります。 スピンロックは内部データ構造へのアクセスをゲートするため、スピンロックの競合はバッファー ラッチの競合と同じ方法で現れるのではなく、スキーマ設計の選択やデータ アクセス パターンの影響を直接受けます。

主にスピンロックの競合に関連する現象は、多数のスピンが発生し、多くのスレッドが同じスピンロックを取得しようとした結果、CPU の消費量が高くなります。 一般に、これは 24 以上の CPU コアを持つシステムで確認されており、最も一般的には 32 を超える CPU コアを持つシステムで確認されています。 前述のように、スピンロックの競合の一部のレベルは、負荷が大きい高コンカレンシー OLTP システムでは正常であり、長時間実行されているシステムでは、 sys.dm_os_spinlock_stats DMV から多数のスピン (数十億/兆) が報告されることがよくあります。 ここでも、特定のスピンロックの種類に対して多数のスピンを観察するだけでは、ワークロードのパフォーマンスに悪影響があることを判断するのに十分な情報ではありません。

次のいくつかの現象の組み合わせは、スピンロックの競合を示している可能性があります。 これらの条件がすべて当てはまる場合は、スピンロックの競合の可能性に関する問題をさらに調査してください。

• 特定のスピンロックタイプでは、多数のスピンとバックオフが観察されます。

• システムで CPU 使用率が高いか、CPU 消費量が急増しています。 CPU の負荷の高いシナリオでは、(DMV SOS_SCHEDULER_YIELDによって報告される) sys.dm_os_wait_statsで高信号待機が発生します。

• システムが高い同時実行を経験しています。

• CPU 使用率とスピンは、スループットに対して不均衡に増加します。

簡単に診断できる一般的な現象の 1 つは、スループットと CPU 使用率の大幅な相違です。 多くの OLTP ワークロードは、(スループット/システム上のユーザー数) と CPU 消費量の間に関係があります。 CPU 消費とスループットの大幅な相違と組み合わせて観察される高スピンは、CPU オーバーヘッドが発生するスピンロックの競合を示している可能性があります。 ここで注意すべき重要な点は、特定のクエリが時間の経過と同時にコストが高くなる場合に、この種の相違がシステムで見られるのも一般的なことです。 たとえば、時間の経過と同時により多くの論理的な読み取りを実行するデータセットに対して発行されたクエリは、同様の現象を引き起じることがあります。

CPU 消費量の増加の一般的な原因には、次のようなものがあります。

• 基になるデータが増加し、メモリ常駐データの追加の論理読み取りを実行する必要があるため、時間の経過と伴ってコストが高くなるクエリ。
• クエリ プランの変更により、最適でない実行が発生します。

例示

次の例では、1 秒あたりのトランザクションによって測定される CPU 消費量とスループットの間にほぼ線形の関係があります。 ワークロードの増加に伴ってオーバーヘッドが発生するため、ここでいくつかの相違点が見えるのが普通です。 ここで示すように、この相違は顕著になります。 また、CPU 消費量が 100%に達すると、スループットが急激に低下します。

3 分間隔でスピンの数を測定すると、スピンの線形増加よりも指数関数的な増加が見られます。これは、スピンロックの競合が問題になる可能性があることを示しています。

前述のように、スピンロックは、負荷が高いコンカレンシーの高いシステムで最も一般的です。

この問題が発生しやすいシナリオには、次のようなものがあります。

• オブジェクトの名前を完全に修飾できなかった場合に発生する名前解決の問題。 詳細については、「 コンパイル ロックによって発生する SQL Server のブロックの説明」を参照してください。 この特定の問題については、この記事で詳しく説明します。

• 同じロックに頻繁にアクセスするワークロード (頻繁に読み取る行の共有ロックなど) のロック マネージャーでのロック ハッシュ バケットの競合。 この種類の競合は、LOCK_HASH タイプのスピンロックとして表面化します。 特定のケースでは、テスト環境で誤ってモデル化されたアクセス パターンの結果として、この問題が発生していることがわかりました。 この環境では、テスト パラメーターが正しく構成されていないために、予想以上の数のスレッドが常にまったく同じ行にアクセスしていました。

• MSDTC トランザクション コーディネーター間の待機時間が長い場合の DTC トランザクションのレートが高い。 この特定の問題については、 DTC 関連の待機の解決と DTC のスケーラビリティのチューニングに関する SQLCAT ブログ エントリに詳しく記載されています。

スピンロックの競合を診断する

このセクションでは、SQL Server スピンロックの競合を診断するための情報を提供します。 スピンロックの競合を診断するために使用される主なツールは次のとおりです。

SQL Server スピンロックの競合を診断するための一般的な技術的なプロセスは次のとおりです。

1. 手順 1: スピンロックに関連する可能性のある競合があることを確認します。

2. 手順 2: sys.dm_os_spinlock_stats から統計情報をキャプチャして、最も競合が発生しているスピンロックの種類を見つけます。

3. 手順 3: sqlservr.exe (sqlservr.pdb) のデバッグ シンボルを取得し、SQL Server のインスタンスの SQL Server サービス .exe ファイル (sqlservr.exe) と同じディレクトリにシンボルを配置します。\ バックオフ イベントの呼び出し履歴を表示するには、実行している SQL Server の特定のバージョンのシンボルが必要です。 SQL Server のシンボルは、Microsoft シンボル サーバーで使用できます。 Microsoft シンボル サーバーからシンボルをダウンロードする方法の詳細については、「 シンボルを使用したデバッグ」を参照してください。

4. 手順 4: SQL Server 拡張イベントを使用して、目的のスピンロックの種類のバックオフ イベントをトレースします。 キャプチャするイベントは spinlock_backoff と spinlock_backoff_warning です。

拡張イベントは、バックオフ イベントを追跡し、スピンロックを取得しようとしている操作の呼び出し履歴をキャプチャする機能を提供します。 呼び出し履歴を分析することで、特定のスピンロックの競合の原因となる操作の種類を特定できます。

診断チュートリアル

次のチュートリアルでは、ツールと手法を使用して、実際のシナリオでスピンロックの競合の問題を診断する方法を示します。 このチュートリアルは、ベンチマーク テストを実行する顧客エンゲージメントに基づいて、1 TB のメモリを備えた 8 ソケット、64 個の物理コア サーバーで、約 6,500 人の同時ユーザーをシミュレートします。

症状

CPUの使用率における定期的な急上昇が観察され、その結果、CPU使用率がほぼ100%に達しました。 この問題に至るまで、スループットと CPU 消費量の間に相違が見られました。 大きな CPU スパイクが発生するまでに、特定の間隔で CPU 使用率が高い時間帯に多数のスピンが発生するパターンが確立されました。

これは、競合が非常に激しく、スピンロックのコンボイ状態を引き起こす極端なケースでした。 スレッドがワークロードの処理を進めることができず、代わりにすべての処理リソースをロックへのアクセス試行に消費する場合、コンボイが発生します。 パフォーマンス モニター ログは、トランザクション ログのスループットと CPU 消費量の間のこの相違点と、最終的には CPU 使用率の大きなスパイクを示しています。

sys.dm_os_spinlock_statsクエリを実行して、SOS_CACHESTOREでの重大な競合の存在を判断した後、拡張イベント スクリプトを使用して、対象のスピンロックの種類のバックオフ イベントの数を測定しました。

スピンの影響を定量化する最も簡単な方法は、スピンの数が最も多いスピンロックの種類について、同じ 1 分間に sys.dm_os_spinlock_stats によって公開されるバックオフ イベントの数を調べます。 この方法は、スピンロックの取得を待機している間にスレッドがスピン制限を使い果たすタイミングを示すので、重大な競合を検出することをお勧めします。 次のスクリプトは、拡張イベントを利用して関連するバックオフ イベントを測定し、競合がある特定のコード パスを識別する高度な手法を示しています。

SQL Server の拡張イベントの詳細については、「 拡張イベントの概要」を参照してください。

スクリプト

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

出力を分析することで、 SOS_CACHESTORE スピンの最も一般的なコード パスの呼び出し履歴を確認できます。 このスクリプトは、返された呼び出し履歴の一貫性を確認するために CPU 使用率が高い間に、いくつかの異なる時間実行されました。 コールスタックのスロットバケット数が最も多いものは、2つの出力 (35,668 と 8,506) に共通しています。 これらのコールスタックには、次に高いエントリを2桁上回るスロット数があります。 この条件は、対象のコード パスを示します。

出力 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

出力 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

前の例では、最も興味深いスタックのスロット数が最も多く (35,668 と 8,506)、実際にはスロット数が 1,000 を超えています。

「この情報をどうしたらよいか」という疑問が出たかもしれません。 一般に、呼び出し履歴情報を使用するには SQL Server エンジンに関する深い知識が必要であるため、この時点でトラブルシューティング プロセスは灰色の領域に移動します。 この特定のケースでは、呼び出し履歴を調べることで、問題が発生するコード パスがセキュリティとメタデータの参照に関連していることがわかります (次のスタック フレーム CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)によって明らかになります。

分離してこの情報を使用して問題を解決することは困難ですが、問題をさらに特定するために追加のトラブルシューティングに重点を置くいくつかのアイデアが得られます。

この問題はセキュリティ関連のチェックを実行するコード パスに関連していると考えられるため、データベースに接続しているアプリケーション ユーザーに sysadmin 特権が付与されるテストを実行することにしました。 この手法は運用環境では推奨されませんが、テスト環境では、トラブルシューティングに役立つ手順であることが判明しました。 昇格された特権 (sysadmin) を使用してセッションを実行すると、競合に関連する CPU スパイクが消えました。

オプションと回避策

明らかに、スピンロック競合の解決は、容易な作業ではありません。 "1 つの一般的なベスト アプローチ" はありません。 パフォーマンスの問題のトラブルシューティングと解決の最初の手順は、根本原因を特定することです。 この記事で説明する手法とツールを使用して、スピンロック関連の競合ポイントを理解するために必要な分析を実行する最初の手順です。

新しいバージョンの SQL Server が開発されると、エンジンは高コンカレンシー システム用に最適化されたコードを実装することで、スケーラビリティの向上を続けます。 SQL Server では、コンカレンシーの高いシステムに対して多くの最適化が導入されています。そのうちの 1 つは、最も一般的な競合ポイントの指数バックオフです。 SQL Server 2012 以降では、エンジン内のすべてのスピンロックに対して指数バックオフ アルゴリズムを利用することで、この特定の領域を具体的に改善する機能強化があります。

極端なパフォーマンスとスケールを必要とするハイエンド アプリケーションを設計する場合は、SQL Server 内で必要なコード パスをできるだけ短くする方法を検討してください。 コード パスが短い場合は、データベース エンジンによって実行される作業が少なくなり、競合ポイントが自然に回避されます。 多くのベスト プラクティスには、エンジンに必要な作業量を減らすという副作用があるため、ワークロードのパフォーマンスが最適化されます。

この記事の前半のいくつかのベスト プラクティスを例として取り上げた場合:

• 完全修飾名: すべてのオブジェクトの名前を完全に修飾すると、名前を解決するために必要なコード パスを SQL Server で実行する必要がなくなります。 また、ストアド プロシージャの呼び出しで完全修飾名を使用しない場合に検出された SOS_CACHESTORE スピンロック型でも競合ポイントが見つかりました。 これらの名前を完全に修飾しないと、SQL Server がユーザーの既定のスキーマを検索する必要が生じ、SQL の実行に必要なコード パスが長くなります。

• パラメーター化されたクエリ: もう 1 つの例として、パラメーター化クエリとストアド プロシージャ呼び出しを使用して、実行プランの生成に必要な作業を減らしています。 この場合も、実行のためのコード パスが短くなります。

• LOCK_HASH 競合: 特定のロック構造またはハッシュ バケットの競合に関する競合は、場合によっては避けられません。 SQL Server エンジンはロック構造の大部分をパーティション分割しますが、ロックを取得すると、同じハッシュ バケットにアクセスする場合があります。 たとえば、アプリケーションは、多数のスレッド (つまり参照データ) によって同じ行に同時にアクセスします。 このような問題は、データベース スキーマ内でこの参照データをスケールアウトするか、可能な場合はオプティミスティック コンカレンシー制御 と 最適化されたロックを使用する 手法によってアプローチできます。

SQL Server ワークロードのチューニングにおける防御の最初の行は、常に標準のチューニング プラクティス (インデックス作成、クエリの最適化、I/O 最適化など) です。 ただし、標準のチューニングに加えて、操作を実行するために必要なコードの量を減らす次のプラクティスが重要なアプローチです。 ベストプラクティスに従っても、高い同時実行性を持つビジーなシステムでは、スピンロックの競合が発生する可能性があります。 この記事のツールと手法を使用すると、これらの種類の問題を特定または除外し、適切な Microsoft リソースを支援するために必要なタイミングを判断するのに役立ちます。

付録: メモリ ダンプ キャプチャを自動化する

次の拡張イベント スクリプトは、スピンロックの競合が重大になったときにメモリ ダンプの収集を自動化するのに役立つことが証明されています。 場合によっては、問題の完全な診断を実行するためにメモリ ダンプが必要になったり、詳細な分析を実行するように Microsoft チームから要求されたりすることがあります。

次の SQL スクリプトを使用すると、スピンロックの競合を分析するためにメモリ ダンプをキャプチャするプロセスを自動化できます。

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

付録: スピンロックの統計を時系列でキャプチャする

次のスクリプトを使用して、特定の期間のスピンロック統計を確認できます。 実行するたびに、現在の値と収集された以前の値の間の差分が返されます。

/* Snapshot the current spinlock 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 '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

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

関連コンテンツ

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