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

[山寨DBA]

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

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

[發糞塗牆]

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

[發糞塗牆]

引用 15 楼 hwhmh2010 的回复:

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

引用 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

[魔者侠心之明星级程序员]

引用 14 楼 hwhmh2010 的回复:

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

引用 9 楼 OrchidCat 的回复:

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

[KeepSayingNo]

很高深，学习了

[山寨DBA]

这个语句的执行结果：

引用 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

[yoan2014]

select * from sys.dm_os_latch_stats 

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

[山寨DBA]

谢谢。 话虽如此，但是我这里目前基本上可以定位在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 的回复:

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

[山寨DBA]

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

引用 21 楼 yoan2014 的回复:

select * from sys.dm_os_latch_stats 

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

[發糞塗牆]

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

[發糞塗牆]

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

[發糞塗牆]

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

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]

[我纯洁全身都纯洁]

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

[专注or全面]

引用 50 楼 hwhmh2010 的回复:

[quote=引用 48 楼 sz_haitao 的回复:] 64G内存，数据库文件不足64G？ 数据能全部加载到内存？ 日常运行的慢、不满意的现象、表现是什么呢？

数据库文件不止64G的，大于64G，不过我负责的那台也不是很大 。。。 常用数据应该是可以加载到内存的。。。 日常运行不快，有点慢，不过勉强可以接受，表现就是业务高峰期的时候latch 等待比较严重[/quote] 一人一台服务器么？这个研究起来有意思哈

[HUSTYZHY]

看着学习一下

[山寨DBA]

引用 48 楼 sz_haitao 的回复:

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

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

[guguda2008]

到目前为止，见过九成九的数据库缓慢问题都是设计不合理，不合理的地方要么是语句写的有问题，要么是索引没做好。

[山寨DBA]

引用 52 楼 x_wy46 的回复:

[quote=引用 50 楼 hwhmh2010 的回复:] [quote=引用 48 楼 sz_haitao 的回复:] 64G内存，数据库文件不足64G？ 数据能全部加载到内存？ 日常运行的慢、不满意的现象、表现是什么呢？

数据库文件不止64G的，大于64G，不过我负责的那台也不是很大 。。。 常用数据应该是可以加载到内存的。。。 日常运行不快，有点慢，不过勉强可以接受，表现就是业务高峰期的时候latch 等待比较严重[/quote] 一人一台服务器么？这个研究起来有意思哈[/quote] 一人一台倒好了，一人好几台呢

[山寨DBA]

引用 51 楼 guguda2008 的回复:

到目前为止，见过九成九的数据库缓慢问题都是设计不合理，不合理的地方要么是语句写的有问题，要么是索引没做好。

我这边的数据库设计的确实不好，但是由于历史和风险原因，不敢轻举妄动啊。。。

[guguda2008]

引用 54 楼 hwhmh2010 的回复:

[quote=引用 51 楼 guguda2008 的回复:] 到目前为止，见过九成九的数据库缓慢问题都是设计不合理，不合理的地方要么是语句写的有问题，要么是索引没做好。

我这边的数据库设计的确实不好，但是由于历史和风险原因，不敢轻举妄动啊。。。[/quote] 如果不能从根源上解决问题，就把这个问题抛出来，让你的上级知道问题在哪，也能证明你的能力。上级不会要求你做能力范围之外的事的。 如果是SQL的问题，可能涉及到复杂的业务逻辑不能轻易改，就只能尽量加合适的索引了。

[herotangabc]

mark 26#