诊断和解决 SQL Server 上的自旋锁争用

本文深入介绍了如何在高并发系统上识别和解决与 SQL Server 应用程序中的旋转锁争用相关的问题。

背景

过去，商用 Windows Server 计算机只利用了一两个微控制器/CPU 芯片，而 CPU 只设计了一个处理器或“核心”。 计算机处理能力的增加是通过使用更快的 CPU 实现的，这主要得益于晶体管密度的提高。 继“摩尔法”之后，自1971年开发第一个通用单芯片 CPU 以来，可在集成电路上放置的晶体密度或晶体数量每两年翻一番。 近年来，除了通过提高 CPU 速度来增强计算机处理能力的传统方法外，还通过构建具有多个 CPU 的计算机来实现这一目的。 截至本文撰写时，Intel Nehalem CPU 架构支持每个 CPU 最多八个核心，当在八插槽系统中使用时，可以使用同时多线程（SMT）技术将逻辑处理器数翻倍至128个。 在 Intel CPU 上，SMT 称为超线程。 随着 x86 兼容计算机上的逻辑处理器数的增加，并发相关问题随着逻辑处理器争用资源而增加。 本指南介绍如何识别和解决某些工作负荷在高并发系统上运行 SQL Server 应用程序时观察到的特定资源争用问题。

在本部分中，我们将分析 SQLCAT 团队从诊断和解决旋转锁争用问题中学到的教训。 Spinlock 争用是在大规模系统上的实际客户工作负荷中观察到的一种类型的并发问题。

旋转锁争用的症状和原因

本部分介绍如何诊断 旋转锁争用问题，这不利于 SQL Server 上的 OLTP 应用程序的性能。 旋转锁诊断和故障排除应被视为一个高级主题，需要了解调试工具和 Windows 内部知识。

旋转锁是用于保护对数据结构的访问的轻型同步基元。 旋转锁对 SQL Server 不是唯一的。 当访问给定的数据结构只需要短时间时，操作系统会使用它们。 当一个线程尝试获取旋转锁但无法获取时，它会在循环中定期检查资源是否可用，而不是立即放弃。 经过一段时间后，等待旋转锁的线程会在获取资源之前让出。 让出（调度）允许在同一 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 中，某些旋转锁类型（例如， LOCK_HASH）通过使用尝试获取旋转锁（高达特定限制）之间的指数级递增间隔来改进，这通常会降低对 CPU 性能的影响。

下图提供了旋转锁算法的概念视图：

典型方案

旋转锁争用可能会出于一些与数据库设计决策无关的原因而发生。 由于旋转锁控制着对内部数据结构的访问，因此旋转锁争用的表现方式与缓冲区闩锁争用不同，而缓冲区闩锁争用则直接受到架构设计选择和数据访问模式的影响。

与旋转锁争用主要相关的症状是由于大量旋转和许多线程尝试获取同一旋转锁，导致高 CPU 占用。 一般情况下，这在具有 24 个和更多 CPU 核心的系统上观察到，最常见的是在 CPU 核心数超过 32 个的系统上观察到的。 如前所述，对于负载很大的高并发 OLTP 系统而言，某些级别的自旋锁争用是正常的，并且在长时间运行的系统上，通常会从 sys.dm_os_spinlock_stats DMV 中报告大量自旋（数十亿/万亿）。 再者，只观察某种特定自旋锁类型的高次数旋转，还不足以判断其对工作负载性能是否有负面影响。

以下几种症状的组合可能表示旋转锁争用。 如果所有这些条件均属实，请进一步调查可能的旋转锁争用问题。

• 观察到某种特定类型的旋转锁存在大量的自旋和退避现象。

• 系统 CPU 使用率过高或 CPU 消耗峰值。 在 CPU 使用密集的场景中，会看到 SOS_SCHEDULER_YIELD 上出现高信号等待（由 DMV sys.dm_os_wait_stats 报告）。

• 系统正在经历高并发。

• CPU 使用率和旋转与吞吐量不成比例增加。

易于诊断的一个常见现象是吞吐量和 CPU 使用率存在重大分歧。 许多 OLTP 工作负荷在（系统上的吞吐量/用户数）与 CPU 消耗之间存在关系。 观察到的高自旋频率与 CPU 消耗和吞吐量之间显著的差异可能表明旋转锁争用导致CPU开销。 此处需要注意的一个重要事项是，当某些查询随着时间的推移变得更加昂贵时，在系统上也经常看到这种类型的分歧。 例如，针对在一段时间内执行更多逻辑读取的数据集发出的查询可能会导致类似的症状。

CPU 消耗增加的一些较常见原因包括：

• 由于基础数据的增长，查询在一段时间内变得更加昂贵，因此需要对内存驻留数据执行额外的逻辑读取。
• 查询计划更改导致执行欠佳。

例子

在以下示例中，CPU 消耗量与吞吐量之间存在几乎线性关系，以每秒事务数衡量。 由于任何工作负载增加都会产生开销，所以在这里看到一些变化是正常的。 如此处所示，这一分歧变得显著。 CPU 消耗量达到 100%后，吞吐量也会急剧下降。

在以 3 分钟为间隔测量旋转次数时，我们可以看到旋转次数呈现出指数性增长，而不是线性增长，这表明旋转锁争用可能存在问题。

如前所述，在负载过大的高并发系统上，最常见的是自旋锁。

一些容易出现此问题的方案包括：

• 由于无法完全限定对象名称而导致的名称解析问题。 有关详细信息，请参阅 编译锁导致的 SQL Server 阻塞的说明。 本文中更详细地介绍了此特定问题。

• 锁管理器中，那些频繁访问同一锁的工作负荷（例如频繁读取某行带来的共享锁）会导致对锁哈希桶的争用。 这种类型的争用表现为 LOCK_HASH 类型旋转锁。 在一个特定情况下，我们发现此问题由于测试环境中错误建模的访问模式而浮出水面。 在此环境中，由于测试参数配置错误，线程数超过预期数量的线程不断访问完全相同的行。

• 当 MSDTC 事务协调器之间存在高度延迟时，DTC 事务的发生频率较高。 此特定问题在 SQLCAT 博客文章中详细介绍了 如何解决 DTC 相关等待和优化 DTC 的可伸缩性。

诊断自旋锁争用

本部分提供有关诊断 SQL Server 旋转锁争用的信息。 用于诊断旋转锁争用的主要工具包括：

诊断 SQL Server 自旋锁争用的一般技术过程是：

1. 步骤 1：确定可能存在与旋转锁相关的争用。

2. 步骤 2：从中捕获统计信息 sys.dm_os_spinlock_stats 以查找遇到最多争用的旋转锁类型。

3. 步骤 3：获取 sqlservr.exe（sqlservr.pdb）的调试符号，并将符号放置在 SQL Server 服务 .exe 文件（sqlservr.exe）所在的同一目录中。\若要查看回退事件的调用堆栈，必须具有运行的特定 SQL Server 版本的符号。 Microsoft符号服务器上提供了 SQL Server 的符号。 有关如何从Microsoft符号服务器下载符号的详细信息，请参阅 使用符号进行调试。

4. 步骤 4：使用 SQL Server 扩展事件跟踪感兴趣的旋转锁类型的回退事件。 要捕获的事件是 spinlock_backoff 和 spinlock_backoff_warning。

扩展事件提供了跟踪退避事件并捕获那些最频繁地尝试获取自旋锁的操作调用堆栈的能力。 通过分析调用堆栈，可以确定导致任何特定旋转锁竞争的操作类型。

诊断演练

以下示例演示如何使用工具和技术诊断实际场景中的旋转锁争用问题。 本演练基于客户参与的基准测试，以模拟在具有 8 个插槽、64 个物理核心和 1 TB 内存的服务器上大约 6,500 个并发用户的运行情况。

症状

观察到 CPU 周期性峰值，使 CPU 利用率提升到接近 100%。 观察到吞吐量与 CPU 消耗之间存在分歧，导致问题发生。 在 CPU 大量使用的特定间隔期间，出现了大量旋转的模式，这种模式在出现大 CPU 峰值时已经形成。

这是一个极端的情况，其中争用导致产生了自旋锁队列问题。 当线程无法再为工作负荷提供服务，而是花费所有正在尝试获取锁访问权限的处理资源时，将发生车队。 性能监视器日志说明了事务日志吞吐量与 CPU 消耗之间的这种差异，最终说明了 CPU 使用率的大幅峰值。

在查询 sys.dm_os_spinlock_stats 确定 SOS_CACHESTORE 是否存在重大争用之后，使用扩展事件脚本测量感兴趣的旋转锁类型的退避事件数量。

量化自旋对性能影响的最直接方法是查看自旋次数最多的自旋锁类型在 sys.dm_os_spinlock_stats 相同的 1 分钟间隔内公开的退避事件数量。 此方法最适合检测重大争用，因为它指示线程在等待获取旋转锁时耗尽旋转限制。 以下脚本演示了一种高级技术，该方法利用扩展事件来度量相关的退避事件，并确定争用所在特定代码路径。

有关 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 使用率较高时运行了几次不同的时间，以检查返回的调用堆栈中的一致性。 具有最高槽存储桶计数的调用堆栈在这两个输出（35,668 和 8,506）之间是相同的。 这些调用堆栈的槽计数大于下一个最高条目的两个数量级。 此条件指示感兴趣的代码路径。

输出 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 峰值消失。

选项和解决方法

显然，排查旋转锁争用问题可能是一项困难的任务。 没有“一种常见的最佳方法”。 排查和解决任何性能问题的第一步是确定根本原因。 使用本文中所述的技术和工具是执行了解与旋转锁相关的争用点所需的分析的第一步。

随着 SQL Server 的新版本的开发，引擎通过实现更好地针对高并发系统进行优化的代码来继续提高可伸缩性。 SQL Server 为高并发系统引入了许多优化，其中之一是最常见的争用点的指数退避。 从 SQL Server 2012 开始，通过利用引擎内所有旋转锁的指数退避算法，特别改进了这一领域的性能。

设计需要极端性能和缩放的高端应用程序时，请考虑如何尽可能短地保留 SQL Server 中所需的代码路径。 较短的代码路径意味着数据库引擎执行的工作更少，并且自然会避免争用点。 许多最佳做法都有一个副作用，即减少引擎所需的工作量，从而优化工作负荷性能。

以本文前面的一些最佳做法为例：

• 完全限定的名称： 所有对象的完全限定名称将导致不再需要 SQL Server 执行解析名称所需的代码路径。 我们观察到，在未使用完全限定名称来调用存储过程时，自旋锁类型上出现了争用点 SOS_CACHESTORE 。 未能完全限定这些名称会导致需要 SQL Server 查找用户的默认架构，这会导致执行 SQL 所需的较长代码路径。

• 参数化查询： 另一个示例是利用参数化查询和存储过程调用来减少生成执行计划所需的工作。 这再次导致执行代码路径较短。

• 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;

相关内容

• 性能监视和优化工具