本文深入介绍了如何在高并发系统上识别和解决与 SQL Server 应用程序中的旋转锁争用相关的问题。
注释
此处记录的建议和最佳做法基于实际 OLTP 系统开发和部署期间的实际体验。 它最初由Microsoft SQL Server 客户顾问团队 (SQLCAT) 团队发布。
背景
过去,商用 Windows Server 计算机只利用了一两个微控制器/CPU 芯片,而 CPU 只设计了一个处理器或“核心”。 计算机处理能力的增加是通过使用更快的 CPU 实现的,这主要得益于晶体管密度的提高。 继“摩尔法”之后,自1971年开发第一个通用单芯片 CPU 以来,可在集成电路上放置的晶体密度或晶体数量每两年翻一番。 近年来,除了通过提高 CPU 速度来增强计算机处理能力的传统方法外,还通过构建具有多个 CPU 的计算机来实现这一目的。 截至本文撰写时,Intel Nehalem CPU 架构支持每个 CPU 最多八个核心,当在八插槽系统中使用时,可以使用同时多线程(SMT)技术将逻辑处理器数翻倍至128个。 在 Intel CPU 上,SMT 称为超线程。 随着 x86 兼容计算机上的逻辑处理器数的增加,并发相关问题随着逻辑处理器争用资源而增加。 本指南介绍如何识别和解决某些工作负荷在高并发系统上运行 SQL Server 应用程序时观察到的特定资源争用问题。
在本部分中,我们将分析 SQLCAT 团队从诊断和解决旋转锁争用问题中学到的教训。 旋转锁争用是在大规模系统的实际客户工作负载中观察到的一种并发问题。
旋转锁争用的症状和原因
本部分介绍如何诊断 旋转锁争用问题,这不利于 SQL Server 上的 OLTP 应用程序的性能。 旋转锁诊断和故障排除应被视为一个高级主题,需要了解调试工具和 Windows 内部知识。
旋转锁是用于保护对数据结构的访问的轻型同步基元。 旋转锁对 SQL Server 不是唯一的。 当访问给定的数据结构只需要短时间时,操作系统会使用它们。 当一个线程尝试获取旋转锁但无法获取时,它会在循环中定期检查资源是否可用,而不是立即放弃。 一段时间过后,等待旋转锁的线程将在可以获取资源前暂停。 让出后,同一 CPU 上运行的其他线程即可执行。 此行为称为退避,本文稍后将更深入地讨论。
SQL Server 利用旋转锁来保护对其某些内部数据结构的访问。 旋转锁在引擎中使用,以类似于闩锁的方式序列化对某些数据结构的访问。 闩锁和旋转锁之间的主要区别在于,旋转锁将旋转(执行循环)一段时间,以检查数据结构的可用性,而如果资源不可用,当线程尝试访问受闩锁保护的结构时,会立即暂停。 让步需要进行上下文切换,使线程放弃 CPU,以便另一个线程能够执行。 这是一项相对昂贵的作,对于持续时间较短的资源,总体效率更高,允许线程定期在循环中执行,以检查资源的可用性。
SQL Server 2022(16.x)中引入的数据库引擎的内部调整使旋转锁更高效。
症状
在任何繁忙的高并发系统上,通常都会看到受旋转锁保护的频繁访问的结构上存在活动争用现象。 仅当争用引入大量 CPU 开销时,此用法才被视为有问题。 旋转锁统计信息由 SQL Server 中的 sys.dm_os_spinlock_stats 动态管理视图(DMV)公开。 例如,此查询生成以下输出:
注释
本文稍后将讨论有关解释此 DMV 返回的信息的更多详细信息。
SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;
此查询公开的统计信息如下所述:
| 列 | DESCRIPTION |
|---|---|
| 碰撞 | 每次阻止线程访问受旋转锁保护的资源时,都会递增此值。 |
| 旋转次数 | 每次线程在等待旋转锁变为可用时执行循环时,都会递增此值。 这是线程在尝试获取资源时工作量的度量值。 |
| 每次冲突的旋转次数 | 每次碰撞的自旋比率。 |
| 睡眠时间 | 与回退事件相关;但是,与本文中所述的技术无关。 |
| 回退次数 | 当尝试访问被占用资源的“自旋”线程确定需要让同一 CPU 上的其他线程来运行时,就会发生这种情况。 |
出于此讨论的目的,特别感兴趣的统计信息是系统负载过大时在特定时间段内发生的碰撞、旋转和退避事件数。 当线程尝试访问受旋转锁保护的资源时,会发生冲突。 发生冲突时,冲突计数会递增,线程将开始在循环中旋转,并定期检查资源是否可用。 每次线程自旋(循环)时,自旋计数都会递增。
每次碰撞的旋转次数是一个度量值,用于衡量线程持有旋转锁时发生的旋转次数,并告知你线程持有旋转锁时发生了多少次旋转。 例如,每次冲突的较少旋转次数和高冲突计数意味着在旋转锁下发生少量旋转并且有许多线程在争用。 旋转次数较多意味着在旋转锁代码中的旋转耗时相对较长(也就是说,代码要遍历哈希桶中的大量条目)。 随着争用的加剧(从而导致冲突计数增加),旋转次数也会随之增加。
退避可以被视为类似于自旋的一种概念。 按照设计,为了避免过多的 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上看到大量信号等待(由 DMVsys.dm_os_wait_stats报告)。系统处于高并发状态。
CPU 使用率和旋转与吞吐量不成比例增加。
易于诊断的一个常见现象是吞吐量和 CPU 使用率存在重大分歧。 许多 OLTP 工作负荷在(系统上的吞吐量/用户数)与 CPU 消耗之间存在关系。 当观察到大量旋转,同时 CPU 消耗量与吞吐量之间存在显著偏差时,可能表明旋转锁争用导致了 CPU 开销。 此处需要注意的一个重要事项是,当某些查询随着时间的推移变得更加昂贵时,在系统上也经常看到这种类型的分歧。 例如,针对在一段时间内执行更多逻辑读取的数据集发出的查询可能会导致类似的症状。
重要
排查此类问题时,排除 CPU 过高的其他常见原因至关重要。
即使上述每个条件都属实,也有可能导致 CPU 消耗过高的根本原因位于其他位置。 事实上,在绝大多数情况下,CPU 消耗增加是由于旋转锁争用以外的原因造成的。
CPU 消耗增加的一些较常见原因包括:
- 由于基础数据的增长,查询在一段时间内变得更加昂贵,因此需要对内存驻留数据执行额外的逻辑读取。
- 查询计划更改导致执行欠佳。
例子
在以下示例中,CPU 消耗量与吞吐量之间存在几乎线性关系,以每秒事务数衡量。 在此处看到一些分歧是正常现象,因为开销随任何工作负载的增加而产生。 如此处所示,这一分歧变得显著。 CPU 消耗量达到 100%后,吞吐量也会急剧下降。
在以 3 分钟为间隔测量旋转次数时,我们可以看到旋转次数呈现出指数性增长,而不是线性增长,这表明旋转锁争用可能存在问题。
如前所述,在负载过大的高并发系统上,最常见的是自旋锁。
一些容易出现此问题的方案包括:
由于无法完全限定对象名称而导致的名称解析问题。 有关详细信息,请参阅 编译锁导致的 SQL Server 阻塞的说明。 本文中更详细地介绍了此特定问题。
对于频繁访问同一锁的工作负载(例如对频繁读取的行上的共享锁),锁管理器中会发生锁哈希桶争用。 这种类型的争用表现为
LOCK_HASH类型旋转锁。 在一个特定情况下,我们发现此问题由于测试环境中错误建模的访问模式而浮出水面。 在此环境中,由于测试参数配置错误,线程数超过预期数量的线程不断访问完全相同的行。当 MSDTC 事务协调器之间存在高度延迟时,DTC 事务的发生频率较高。 此特定问题在 SQLCAT 博客文章中详细介绍了 如何解决 DTC 相关等待和优化 DTC 的可伸缩性。
诊断旋转锁争用
本部分提供有关诊断 SQL Server 旋转锁争用的信息。 用于诊断旋转锁争用的主要工具包括:
| 工具 | 使用 |
|---|---|
| 性能监视器 | 查找 CPU 使用率高的情况,或吞吐量与 CPU 消耗之间的差异。 |
| Spinlock 统计信息 | 查询 sys.dm_os_spinlock_stats DMV,以查找一段时间内出现的大量旋转和回退事件。 |
| 等待统计信息 | 从 SQL Server 2025 (17.x) 开始,使用 SPINLOCK_EXT 等待类型查询 sys.dm_os_wait_stats 和 sys.dm_exec_session_wait_stats DMV。 需要 跟踪标志 8134。 有关详细信息,请参阅 SPINLOCK_EXT。 |
| SQL Server 扩展事件 | 用于跟踪正在经历大量旋转的旋转锁调用堆栈。 |
| 内存转储 | 在某些情况下,需要使用 SQL Server 进程的内存转储和 Windows 调试工具。 一般情况下,当Microsoft支持团队参与时,将完成此级别的分析。 |
诊断 SQL Server 旋转锁争用的一般技术过程如下:
步骤 1:确定存在可能与旋转锁有关的争用。
步骤 2:从
sys.dm_os_spinlock_stats中捕获统计信息以查找争用最严重的旋转锁类型。步骤 3:获取 sqlservr.exe(sqlservr.pdb)的调试符号,并将符号放置在 SQL Server 服务 .exe 文件(sqlservr.exe)所在的同一目录中。\若要查看回退事件的调用堆栈,必须具有运行的特定 SQL Server 版本的符号。 Microsoft符号服务器上提供了 SQL Server 的符号。 有关如何从Microsoft符号服务器下载符号的详细信息,请参阅 使用符号进行调试。
步骤 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 是否存在严重争用之后,使用扩展事件脚本测量了相关旋转锁类型的回退事件数量。
| 名称 | 碰撞 | 旋转次数 | 每次冲突的旋转次数 | 回退次数 |
|---|---|---|---|---|
SOS_CACHESTORE |
14,752,117 | 942,869,471,526 | 63,914 | 67,900,620 |
SOS_SUSPEND_QUEUE |
69,267,367 | 473,760,338,765 | 6,840 | 2,167,281 |
LOCK_HASH |
5,765,761 | 260,885,816,584 | 45,247 | 3,739,208 |
MUTEX |
2,802,773 | 9,767,503,682 | 3,485 | 350,997 |
SOS_SCHEDULER |
1,207,007 | 3,692,845,572 | 3,060 | 109,746 |
量化旋转影响的最直接方法是查看具有最高旋转次数的旋转锁类型在同样的 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 使用率较高时运行了几次不同的时间,以检查返回的调用堆栈中的一致性。 具有最高槽存储桶计数的调用堆栈在这两个输出(35,668 和 8,506)之间是相同的。 这些调用堆栈的槽计数比排名第二的条目高出两个数量级。 此条件指示感兴趣的代码路径。
注释
看到之前脚本返回的调用堆栈并不少见。 当脚本运行 1 分钟时,我们观察到,槽计数为 > 1,000 的调用堆栈有问题,但 10,000 的槽计数 > 更有可能是有问题的,因为它是更高的槽计数。
注释
出于可读性目的,已清理以下输出的格式。
输出 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资源来提供帮助。
附录:自动执行内存转储捕获
仅适用于:SQL Server。
当旋转锁争用变得严重时,以下扩展事件脚本已被证明有助于自动收集内存转储。 在某些情况下,需要内存转储才能对问题进行全面诊断,或者 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;
附录:收集自旋锁随时间变化的统计信息
以下脚本可用于查看特定时间段的旋转锁统计信息。 每次运行时,它都会返回当前采集值与之前采集值之间的差值。
注释
在 Azure SQL 数据库 和 Microsoft Fabric 中的 SQL 数据库中,USE语句不可用,也不支持直接连接到 tempdb。 连接到所需的样本或测试数据库,运行剩余的代码样本。
/* 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;