讨论：如何降低数据库的latch --- 大神请进

有几台数据库（最低配置是16核CPU ，64G内存），目前的瓶颈都在latch ，如何降低数据库的latch 。
有谁遇到过此类latch瓶颈问题，如何解决的呢 ？
也可根据自己的理解，给出解决方向。

以上，请诸位大神不吝赐教，小弟感激不尽！！！

57 个解决方案

#1

lz 说的latch是？ io latch 还是page latch?

#2

先提供当前详细信息

#3

敏感信息除外，推荐一下。。

#4

大家稍等，我整个出来。

#5

表示我不知道啊

#6

如下图：

#7

该回复于2014-05-19 13:42:53被管理员删除

#8

SELECT TOP 5 * FROM sys.dm_os_waiting_tasks ORDER BY wait_duration_ms DESC 
SELECT TOP 5 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

 再看看，另外我觉得你用【数据收集器集】那个收集一段时间可能比较有效

#9

另外 看看这个 sys.dm_db_index_operational_stats

如果lz有baseline的话，对比一下baseline是否存在latch异常。 

还有当时额user connections 是多少？

#10

引用 1 楼 OrchidCat 的回复:

lz 说的latch是？ io latch 还是page latch?

问题是我老板反馈给我的，然后我刚在6#有发个截图。
大神两个都给说说呗。

#11

两句话执行结果：

引用 8 楼 DBA_Huangzj 的回复:

SELECT TOP 5 * FROM sys.dm_os_waiting_tasks ORDER BY wait_duration_ms DESC 
SELECT TOP 5 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

 再看看，另外我觉得你用【数据收集器集】那个收集一段时间可能比较有效

#12

引用 9 楼 OrchidCat 的回复:

另外 看看这个 sys.dm_db_index_operational_stats

如果lz有baseline的话，对比一下baseline是否存在latch异常。 

还有当时额user connections 是多少？

没有baseline呢。。。

#13

SELECT TOP 5 * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL  ORDER BY wait_duration_ms DESC  这个看看有没有数据

#14

没用过数据收集器呢。。。
补充说一下：我这个是电商的系统，每天的访问量有两个固定的高峰9:30 到11:30 和 13:30到16:00 ，每次这两个高峰的时候都会出现latch等待，由此判断存在latch瓶颈。。。
主要是想有什么办法可以降低数据库的latch

引用 8 楼 DBA_Huangzj 的回复:

引用 9 楼 OrchidCat 的回复:

#15

阻塞和死锁我经常检查，只偶尔会出现自己阻塞自己的现象，但是不频繁。

引用 13 楼 DBA_Huangzj 的回复:

SELECT TOP 5 * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL  ORDER BY wait_duration_ms DESC  这个看看有没有数据

#16

SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddios.[object_id])
        + '].[' + OBJECT_NAME(ddios.[object_id]) + ']' AS [object_name] ,
        i.[name] AS index_name ,
        ddios.page_io_latch_wait_count ,
        ddios.page_io_latch_wait_in_ms ,
        ( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count ) AS avg_page_io_latch_wait_in_ms
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
                                    AND i.index_id = ddios.index_id
WHERE   ddios.page_io_latch_wait_count > 0
        AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ddios.page_io_latch_wait_count DESC ,
        avg_page_io_latch_wait_in_ms DESC

#17

引用 15 楼 hwhmh2010 的回复:

阻塞和死锁我经常检查，只偶尔会出现自己阻塞自己的现象，但是不频繁。

Quote: 引用 13 楼 DBA_Huangzj 的回复:

SELECT TOP 5 * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL  ORDER BY wait_duration_ms DESC  这个看看有没有数据

自己阻自己就可能出现latch

#18

引用 14 楼 hwhmh2010 的回复:

没用过数据收集器呢。。。
补充说一下：我这个是电商的系统，每天的访问量有两个固定的高峰9:30 到11:30 和 13:30到16:00 ，每次这两个高峰的时候都会出现latch等待，由此判断存在latch瓶颈。。。
主要是想有什么办法可以降低数据库的latch

Quote: 引用 8 楼 DBA_Huangzj 的回复:

引用 9 楼 OrchidCat 的回复:

出现latch等待并不就等于latch瓶颈啊，你要看latch等待占总等待的百分比来决定是不是latch瓶颈。

#19

很高深，学习了

#20

这个语句的执行结果：

引用 16 楼 DBA_Huangzj 的回复:

SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddios.[object_id])
        + '].[' + OBJECT_NAME(ddios.[object_id]) + ']' AS [object_name] ,
        i.[name] AS index_name ,
        ddios.page_io_latch_wait_count ,
        ddios.page_io_latch_wait_in_ms ,
        ( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count ) AS avg_page_io_latch_wait_in_ms
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
                                    AND i.index_id = ddios.index_id
WHERE   ddios.page_io_latch_wait_count > 0
        AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ddios.page_io_latch_wait_count DESC ,
        avg_page_io_latch_wait_in_ms DESC

#21

select * from sys.dm_os_latch_stats 

看看能不能提供点什么有用的信息

#22

谢谢。
话虽如此，但是我这里目前基本上可以定位在latch瓶颈呢，因为latch的等待比较严重，性能监视器里面已经比较高的超过了他的阀值。

引用 18 楼 SQL_Beginner 的回复:

Quote: 引用 14 楼 hwhmh2010 的回复:

没用过数据收集器呢。。。
补充说一下：我这个是电商的系统，每天的访问量有两个固定的高峰9:30 到11:30 和 13:30到16:00 ，每次这两个高峰的时候都会出现latch等待，由此判断存在latch瓶颈。。。
主要是想有什么办法可以降低数据库的latch

Quote: 引用 8 楼 DBA_Huangzj 的回复:

引用 9 楼 OrchidCat 的回复:

出现latch等待并不就等于latch瓶颈啊，你要看latch等待占总等待的百分比来决定是不是latch瓶颈。

#23

首先感谢回复。
我把你的语句加了个order by 得出结果依然是让人很心碎的：

引用 21 楼 yoan2014 的回复:

select * from sys.dm_os_latch_stats 

看看能不能提供点什么有用的信息

#24

LCK_M_U等待比较大，敢动索引吗？

#25

瞬间的等待并不能代表什么，所以我暂时先不从你的闩锁开始找问题

#26

可以的话分别贴出下面脚本的结果：
第一个脚本：

SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE   i.index_id NOT IN ( SELECT  ddius.index_id
                            FROM    sys.dm_db_index_usage_stats AS ddius
                            WHERE   ddius.[object_id] = i.[object_id]
                                    AND i.index_id = ddius.index_id
                                    AND database_id = DB_ID() )
        AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC;

 

第二个脚本：

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC;

第三个：

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,
        dbmigs.last_user_seek ,
        dbmid.[statement] AS [Database.Schema.Table] ,
        dbmid.equality_columns ,
        dbmid.inequality_columns ,
        dbmid.included_columns ,
        dbmigs.unique_compiles ,
        dbmigs.user_seeks ,
        dbmigs.avg_total_user_cost ,
        dbmigs.avg_user_impact
FROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
        INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE   dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC;

第四个：

SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                     DB_ID()) + '].['
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc] ,
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]

#27

黄老板这些解决问题的代码都是平时积攒下来的吧

#28

刚才吃饭去了。。。
敢动索引。到时候也看看索引是否有问题。

引用 24 楼 DBA_Huangzj 的回复:

LCK_M_U等待比较大，敢动索引吗？

#29

没法猜到原因，实际诊断才不会抽象。。
若不影响用户体验或并发问题，由得它
若要优化，是个综合性问题，而不是单一的LATCH问题

#30

非常感谢黄哥的思路，这个太给力了，索引建好了，应该也是降低latch的方法之一，


第一个脚本结果：有查出来几个表是没有主键，也没有聚集索引，到时候看看酌情加上。

第二个脚本结果：有查到几个索引不合理，写的很多，查询用到的很少，这个我打算把他删了。

第三个脚本结果：有查到缺失索引的情况，avg_user_impact --- 百分比收益 高达99.5以上的有几个，而且last_user_seek --- 使用后影响上次结果 就在刚才，打算到时候加上索引。

第四个脚本结果：我没看懂第四个脚本是干啥的，呵呵，也没用过，所以我直接贴出结果了咯：
statement index_name index_type_desc partition_number alloc_unit_type_desc index_depth index_level avg_frag_% avg_frag_size_in_pages fragment_count page_count
[JinRIAV].[dbo].[OrderPriceVerify] PK__OrderPri__3213E83F68487DD7 CLUSTERED INDEX 1 IN_ROW_DATA 3 0 26 3 4195 14986
[JinRIAV].[dbo].[TblQetVerifyLog] index_pnrcode_codeid NONCLUSTERED INDEX 1 IN_ROW_DATA 3 0 99 1 18986 19002

引用 26 楼 DBA_Huangzj 的回复:

可以的话分别贴出下面脚本的结果：
第一个脚本：

第二个脚本：

第三个脚本：

第四个脚本：

#31

谢谢提供的建议。
目前打算整体调整一下，各方面都排查一下，不过目前首要想解决的问题就是降低数据库的latch 。。。

引用 29 楼 luckyrandom 的回复:

没法猜到原因，实际诊断才不会抽象。。
若不影响用户体验或并发问题，由得它
若要优化，是个综合性问题，而不是单一的LATCH问题

#32

第四个是索引碎片程度

#33

第一个：看看表大不大，访问频繁不频繁。如果频繁且大，建议加主键。
第二个：这个要衡量一下
第四个：是碎片大于15%，且索引规模大于500页的

另外：有些主键PK，可能会被认为“无用”，但是为了维护数据的一致性等功能，即使从来未被使用，也是要保留。

#34

黄哥稍等一下，刚才给你的第四个脚本的结果，我自己看着都头大，等会我马上弄个截图出来，看的清晰。

引用 26 楼 DBA_Huangzj 的回复:

#35

嗯，黄哥的几点建议，到时候我会跟我这边其他几个DBA和我们技术总监一起讨论决定如何操作。
另外补上第四个截图（你已经告诉我第四个脚本的用途了，所以这个问题我自己应该也可以衡量了 ）：

引用 33 楼 DBA_Huangzj 的回复:

第一个：看看表大不大，访问频繁不频繁。如果频繁且大，建议加主键。
第二个：这个要衡量一下
第四个：是碎片大于15%，且索引规模大于500页的

另外：有些主键PK，可能会被认为“无用”，但是为了维护数据的一致性等功能，即使从来未被使用，也是要保留。

#36

99%那个可以考虑重建一下

#37

引用 36 楼 DBA_Huangzj 的回复:

99%那个可以考虑重建一下

嗯嗯。。。

黄哥，还有什么其他方法能降低latch的吗？

我刚才给的那个服务器是目前我手上的一个辅助的服务器，其他DBA手里还有latch等待更加严重的，那些服务器我没权限登。。。

我家老板目前给我们的任务就是找时间讨论关于降低latch的事情。

#38

先要找到latch的根源，比如是否缺失索引或者索引无效或者代码太烂导致非预期数量的数据加载引起latch，还是说硬件资源有瓶颈。最少改动的方法可能是找出资源消耗较大的10~20%查询，进行优化，然后看看结果，

#39

1）首先为什么直接定位是latch瓶颈？没有其他的瓶颈？内存、IO、CPU？
2）latch瓶颈往往伴随着其他的瓶颈，是从其他等待衍生的
3）如果目前服务器健康运行，这些等待指示短暂的
4）如果其他的等待都在可控范围，都处理的很好，latch减少很多，而且持续时间很短
5）应该从其它等待入手，等其它的等待都处理好了，达到理想的程度，再回头看latch 一开始就定位latch是不科学的
   

#40

引用 38 楼 DBA_Huangzj 的回复:

先要找到latch的根源，比如是否缺失索引或者索引无效或者代码太烂导致非预期数量的数据加载引起latch，还是说硬件资源有瓶颈。最少改动的方法可能是找出资源消耗较大的10~20%查询，进行优化，然后看看结果，

嗯嗯，就先从这个层面开始着手查起。。。


黄哥，知不知道还是否有什么数据库设置也能降低latch的呢？

#41

最常见的是最大内存那里设得太低

#42

额，看错了，以为什么配置容易引起latch。这样吧，先别把问题过于早地集中在latch。latch大部分情况下还是因为不合理的索引使用引起大面积的数据加载，另外要看看内存是否有压力，比如lpe太短

#43

#44

谢水锅捧场。。。
1）内存和CPU都很充裕的，硬盘用的是存储。
2）目前从性能监视器来看的话，是latch瓶颈，不过也有可能是其他等待衍生的，目前正在制定排查方向（已定方向有：表主键，聚集索引，无效索引和缺失索引这几块。
3）服务器运行还行，也不能算很健康吧，这个不太好说，呵呵
4、5）这个我们也会逐一排查的。。。

呵呵，其实是老板给我们出的解决方案：让我和另外几个DBA一起讨论解决降低latch的问题。。。也许正如水哥所言，导致latch的是其他等待。

引用 39 楼 wufeng4552 的回复:

1）首先为什么直接定位是latch瓶颈？没有其他的瓶颈？内存、IO、CPU？
2）latch瓶颈往往伴随着其他的瓶颈，是从其他等待衍生的
3）如果目前服务器健康运行，这些等待指示短暂的
4）如果其他的等待都在可控范围，都处理的很好，latch减少很多，而且持续时间很短
5）应该从其它等待入手，等其它的等待都处理好了，达到理想的程度，再回头看latch 一开始就定位latch是不科学的
   

#45

引用 42 楼 DBA_Huangzj 的回复:

额，看错了，以为什么配置容易引起latch。这样吧，先别把问题过于早地集中在latch。latch大部分情况下还是因为不合理的索引使用引起大面积的数据加载，另外要看看内存是否有压力，比如lpe太短

嗯，好吧。那我这边就先从其他等待和索引入手开始整理。

#46

打个标记，为了26#的4个脚本。

#47

#48

64G内存，数据库文件不足64G？
数据能全部加载到内存？
日常运行的慢、不满意的现象、表现是什么呢？

#49

看着学习一下

#50

引用 48 楼 sz_haitao 的回复:

64G内存，数据库文件不足64G？
数据能全部加载到内存？
日常运行的慢、不满意的现象、表现是什么呢？

数据库文件不止64G的，大于64G，不过我负责的那台也不是很大 。。。
常用数据应该是可以加载到内存的。。。
日常运行不快，有点慢，不过勉强可以接受，表现就是业务高峰期的时候latch 等待比较严重