SQL Server闩锁及其性能问题指示 [英] SQL Server Latches and their indication of performance issues
问题描述
我正在尝试了解数据库(SQL 2008)的潜在性能问题，尤其是一个性能计数器SQLServer:Latches \ Total Latch Wait Time总锁存等待时间(ms).我们看到数据库响应时间变慢，并且我能与之匹配的唯一相关峰值是总锁存等待时间和锁存等待/秒的峰值.我没有在磁盘IO，CPU使用率或内存中看到任何特定的瓶颈.
I am trying to understand a potential performance issue with our database (SQL 2008) and in particular one performance counter, SQLServer:Latches\Total Latch Wait Time Total Latch Wait Time (ms). We are seeing a slow down in DB response times and the only correlating spike that I can match it with is a spike in Total Latch Wait Time and Latch Waits/sec. I am not seeing any particular bottleneck in disk IO, CPU usage or memory.
SQLServer闩锁的常见解释是它是轻量级的锁，但是我试图更详细地了解闩锁是什么，它与锁有何不同以及它们的数量之多.看到可能是指示.
The common explanation of a SQLServer latch is that it is a lightweight lock, but I am trying to get a more detailed understanding of what a latch is, how it differs from a lock and what the high amount of them that I am seeing may be an indicator for.
推荐答案
I recommend you looke into sys.dm_os_latch_stats and see what type of latches have increased contention and wait types, compared to previous base-line.
如果您看到BUFFER类型的闩锁中出现尖峰，则表示它是由冲突冲突导致的，以修改同一页面.其他闩锁类型在MSDN中也有简短的解释，可以指导您解决问题的根本原因.对于那些标记为仅供内部使用"的用户，您将不得不与MS一起开立支持案例，以详细说明它们的含义即将在NDA中使用.
If you see a spike in the BUFFER type latches it means it is driven by updates conflicting to modify the same page. Other latch types have also short explanation in the MSDN and can guide you toward the problem root cause. For those marked 'internal use only', you're going to have to open a support case with MS, as a detailed explanation of what they mean is on the verge of NDA.
您还应该查看 sys.dm_os_wait_stats .如果看到PAGELATCH_*增加，则与上面的BUFFER类型锁存器存在相同的问题，即试图修改同一页面时也存在争用.作为更新热点.如果您看到PAGEIOLATCH_*增加，那么您的问题就是I/O暂存器，则在需要时将页面加载到内存中的时间太长.
You should also look into sys.dm_os_wait_stats. If you see an increase of PAGELATCH_*, then it is the same problem as the BUFFER type latch above, contention in trying to modify same page, aka. as an update hot-spot. If you see an increase PAGEIOLATCH_*then your problem is the I/O susbsytem, it takes too long to load the pages in memory when they are needed.
这篇关于SQL Server闩锁及其性能问题指示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
