学习笔记-SQLSERVER SPINLOCK诊断
可能有朋友会有疑惑，SQLSERVER也会有SPINLOCK吗？事实上，只要是现代的多核心CPU环境中，多路服务器环境中，要实现高并发环境下的共享内存串行化访问，spinlock就必定会存在的，只是Windows和Linux在自旋锁的实现代码上不同而已。正是因为spinlock的存在，SQLSERVER也会和Oracle、MySQL、PG等数据库一样因为spinlock较为严重而导致OLTP系统的性能问题。因为spinlock主要出现在高并发应用环境下，因此在OLTP系统中出现严重问题的可能性远高于并发量不大的OLAP系统。和Linux上类似，spinlock是一个原子操作的轻量级锁，用于保护SQLSERVER的共享内存，使之能够通过串行化访问以确保其一致性。不仅SQLSERVER数据库需要使用spinlock,当操作系统需要排他性访问共享内存结构时，也会使用spinlock。我们基本上可以把SQLSERVER的SPINLOCK与Oracle的LATCH等同起来。当某个线程试图获取自旋锁的线程无法获得访问权限时，它会循环尝试，定期检查以确定资源是否可用，而不是立即让出CPU。一段时间后，等待spinlock的线程无法获取资源后失败，而不直接让出CPU给其他线程使用，这是因为切换CPU的上下文是比较高开销的操作，持续持有CPU并不断测试在短期内能够获取资源的情况下，更为高效。虽然这种设计在大多数场景下是没问题的，不过对于某些场景，可能会出现某些线程为了获得某个spinlock的访问权而把持了CPU资源，从而导致CPU资源的使用效率降低，从而导致性能问题。分析SQLSERVER spinlock问题的好接口是sys.dm_os_spinlock_stats，其中十分重要的几列在下面描述。上面的查询可以显示出SQLSERVER的spinlock的统计信息，如果我们在某个固定的时间区间内去采集这些数据，并通过delta计算增量差值，就可以掌握近一个采样周期内各种spinlock的情况，从而从中推测数据库可能存在的并发方面的问题。我们应该特别关注系统处于重负载下的特定时期内发生的冲突、spin和backoff事件的数量。当一个线程试图访问一个受spinlock保护的资源时，就会发生冲突。当发生冲突时，冲突计数会增加，线程将开始spin并定期检查资源是否可用。每次线程spin时，spins计数都会增加。spins_per_collision是线程持有spinlock时发生的spins的度量，它会告诉您在线程持有spinlock时发生了多少次自旋。每次冲突的spins很小，但是冲突计数器很高，这就意味着spinlock下会发生少量自旋，有许多线程在竞争它。大量的spins意味着spinlock相关的代码中spin所花费的时间相对较长,而spinlock是用来保护内存结构的，这些代码都十分简单，花费时间长往往出在代码正在遍历的哈希桶中有比较多的条目。这种情况我以前在Oracle数据库中也遇到过，当某些数据块的cr block比较多的时候，再加上一些其他因素，会出现HASH CHAINS上的HOT CHAINS，当CACHE BUFFERS CHAINS出现HOT CHAINS的时候，扫描这条链的时间就会增加，从而引发严重的cache buffer chains闩锁等待。而这时候通过调整buckets数量或者较大的增加减少DB CACHE的大小，就可以打破HOT CHAINS，解决这个问题。在《Oracle DBA优化日记》中国的海尔的那个优化案例就是采用这个方法去临时解决的。从另外一个角度看，随着系统并发争用的增加，spinlock的冲突计数计数也会增加，同时spins的数量也会增加。退避（backoff）是spinlock的一种补偿性设计，如果一直spin无法获取lock，那么一个CPU就会被持续占用，导致CPU资源的浪费。为提高CPU的工作效率，spinlock在可以访问持有的资源之前不会无限期地继续spin。为确保spinlock不会过度使用 CPU 资源，spinlock在达到spin的轮数限制后会退出或停止spin并“休眠”，无论它们是否曾经获得目标资源的所有权。这样做是为了允许在 CPU 上调度其他线程提高CPU的使用效率。引擎的默认行为是在执行backoff之前先spin一个恒定的时间间隔。尝试获取spinlock需要保持高速缓存并发状态，这是一个 CPU 密集型操作。在 SQL Server 中，某些spinlock（例如：LOCK_HASH）通过利用以指数方式增加的尝试获取spinlock（达到一定限制）从而避免对CPU 性能产生较大影响。SQLSERVER的spinlock的实现与Oracle的LATCH是十分相似的，我在阅读上面这段描述的时候，甚至对Oracle的LATCH的认知也更加清晰了一些。如果了解Oracle LATCH冲突分析的DBA，想要处理SQL SERVER的spinlock，也是比较容易的。只需要了解SQL SERVER的每个spinlock保护的数据结构属于哪方面的就可以了。RDBMS是一个经过精心设计的处理高并发复杂业务的软件系统，虽然经过精心设计，但是某些情况可能也在软件开发人员的意料之外。Spinlock就是充满了意外的部分，spinlock争用无法通过RDBMS代码优化或者应用开发人员精心设计数据库结构而避免。spinlock对RDBMS内部的不同数据结构进行访问，所以spinlock争用的表现方式与缓冲区锁存争用不同，后者可以通过优化数据库设计和SQL来解决，而spinlock不能。根据微软官方文档的描述，spinlock的严重争用已在CPU线程数 >= 24 的系统上观察到，并且常见于 >= 32 CPU内核的系统上。对于负载很大的高并发 OLTP 系统，存在一定的spinlock冲突，休眠是正常的。观察到任何给定自旋锁类型的大量spins并不足以说明系统存在并发方面的问题。不过以下几种症状的组合可能表明spinlock存在较为严重的争用：l系统 CPU 使用率过高或出现 CPU 消耗量激增的情况。在繁重的 CPU 场景中，您会在 SOS_SCHEDULER_YEILD 上看到高信号等待（由 DMV sys.dm_os_wait_stats报告）；lCPU 使用率和spins的增长与数据库系统的吞吐量不成比例地增加。上述条件都成立，是系统出现spinlock性能问题的典型征兆，不过即使上述每个条件都成立，高 CPU 消耗的根本原因仍然可能在其他地方。事实上，在绝大多数情况下，CPU 的增加是由于自旋锁争用以外的原因。导致 CPU 消耗增加的一些更常见的原因包括：u由于基础数据的增长导致需要对内存驻留数据执行额外的逻辑读取，查询随着时间的推移变得更加昂贵；针对spinlock异常问题的发现，一个比较典型的场景是数据库负载的增加比例与 CPU 使用率的增加比例存在显著的差异。OLTP 工作负载在（吞吐量/系统上的用户数）和 CPU 消耗之间都有相对固定的关系。观察到较高的spins以及 CPU 使用率和数据库负载之间的差异可能表明spinlock争用增加了 CPU 开销。这里需要注意的一点是，当某些SQL执行计划变坏时，数据库逻辑读的增加，也会引发类似的现象。在分析spinlock问题时，排除其他更常见的高 CPU 原因至关重要。下面我们来看看如何诊断spinlock的问题。诊断首先要看工具。微软官方文档给出的工具十分简单，其中后两个是比较复杂的，需要专业的人员进行。而前两个是DBA十分常用的工具。如果要做现场分析，则可以通过性能监视器来观察系统资源情况，如果你已经采集了OS的CPU，内存，IO等数据，则十分有利于现场分析或者事后分析。Spinlock状态的DMV视图是十分重要的工具，我们可以根据这个信息发现哪些spinlock出现了问题，从而进行根因定位。分析的过程也很清晰。只不过后两个步骤对于一般的DBA来说过于专业，而且必须在具有符号调试器的SQLSERVER环境中才能进行。不过我们在大多数情况下不必要如此兴师动众。通过第二步发现的spinlock种类，查出存在问题的spinlock后，根据spinlock的种类基本上就能够对出问题的地方进行猜测了。比如buff_free_list是缓冲池空闲列表spinlock,该spinlock出现严重问题一般来说和缓冲区申请有关，如果SQL出现了大量的逻辑读，而缓冲区申请过于频繁，缓冲内存不足等，就会导致该spinlock争用变得严重。似乎很简单，实际上不是的，spinlock分析是SQLSERVER性能分析的深水区，往往会遇到灰色地带，这些地带是你的知识积累锁不能达到的。比如微软官网的这个白皮书里提到的一个案例。LOCK_HASH这个spinlock我们还是比较容易理解的，当很多并发进程访问某个锁HASH的同一个桶的时候，很容易出现此等待，比如大量的并发会话都在争用某几个具体的行数据的时候，就会发生。当然这只是其中一种情况，更多的情况还需要微软官方的介绍或者我们积累更多的案例与运维知识。而对于严重的SOS_CACHESTORE，我们就一头雾水了。微软官方都无法说清楚这个spinlock到底会出现在多少个场景中，因此必须通过符号调试器去获得扩展事件，从而获得CALL STACK的信息，才能进一步分析。恐怕没有DBA能够看出这个CALL STACK代表什么含义，微软后台的服务专家可以根据源代码分析出这是因为权限不足导致的问题，把访问的用户权限提升后，这个spinlock就消失了。上面是我今天学习SQLSERVER spinlock分析的白皮书的笔记，实际上以我以往的知识，对spinlock的理解并不困难，分析诊断的思路与其他数据库的类似问题并无太大区别。主要的还是SQLSERVER的运维知识的积累还是不足，对于不同的spinlock可能出现在哪些场景中的归纳还是太欠缺。而积累这方面的知识，仅仅依靠看书是无法获得的。必须汇聚和分析大量的案例才能实现。我们在Oracle上已经有了20多年的此类积累，而SQLSERVER上的积累还太薄弱。我们准备节后在D-SMART社区版中增加对SQLSERVER spinlock的数据采集。有兴趣的朋友可以下载我们的D-SMART社区版，去采集一些数据。并通过hola分享给我们。我们会用来积累运维经验，并将这些运维经验发布到社区版中，免费给大家使用。本文来源https://www.modb.pro/db/507882