【等待优化】sql server WRITELOG 等待排查

【0】故障信息

几乎每个insert / update 都会有writelog等待。

而且我查看了一下，并没有任何阻塞。

【1】WRITELOG的分析

当SQL Server会话等待WRITELOG等待类型时，它将等待将日志缓存的内容写入存储事务日志的磁盘。

为了更详细地说明该过程，假定会话启动一个事务，该事务将执行多个INSERT语句。插入数据时，将发生两个操作：

缓冲区高速缓存中的数据页将使用新数据进行更新。
数据被写入日志缓存，这是用于记录将用于回滚事务或写入日志文件的数据的内存段。

此过程将继续进行，直到事务完成（提交）为止，此时日志缓存中的数据立即被写入物理日志文件。当SQL Server将日志缓存刷新到磁盘时，会话将以WRITELOG等待类型等待。

获取更多信息

如果会话始终在WRITELOG等待类型上等待，请查看以下Perfmon数据以获取存储事务日志的磁盘瓶颈迹象：

物理磁盘对象
　　1.平均 磁盘队列长度–已排队的IO请求的平均数量。如果该值始终大于1，则可能表明存在磁盘瓶颈。
　　2.平均 磁盘秒/读取和平均 磁盘秒/写入–如果其中任何一个大于15-20毫秒，则可能表示事务日志存储在速度较慢的设备上
SQLServer：缓冲区管理器
　　1.检查点页面数/秒–需要将所有脏缓冲区写入磁盘的检查点操作刷新的页面数

使用SolarWinds Database Performance Analyzer或其他工具，还可以确定等待WRITELOG事件的顶部SQL语句。

如果发现许多语句正在等待，则可能表明上述问题之一是问题所在。如果仅发现一些等待WRITELOG的SQL语句，则可能表明事务使用效率低下（在下面的示例中进行讨论）。

WRITELOG等待类型：解决问题

磁盘子系统性能 –在有关WRITELOG等待类型的许多文档中，该问题似乎常常被误解为磁盘子系统问题。在出现磁盘问题的情况下，Perfmon中的PhysicalDisk对象的计数器将很高，并且修复程序通常包括：

向存储事务日志的磁盘子系统添加额外的IO带宽。
从磁盘移动非事务日志IO。
将事务日志移到不太忙的磁盘上。
在某些情况下，减少事务日志的大小也有所帮助。

频繁提交数据–在性能咨询期间，在很多情况下，过度热心使用事务可能会导致对WRITELOG等待类型的过多等待，即过于频繁地提交数据。为了说明此问题，请考虑以下代码示例：

示例1：以下代码执行了418秒，并在WRITELOG等待类型上等待了410秒。请注意，COMMIT语句如何位于循环内部并执行了100,000次。

DECLARE @i INT
 SET @i = 1
 WHILE @i < 100000
 BEGIN
 BEGIN TRANSACTION
 INSERT INTO [splendidCRM].[dbo].[product]
 ([productid],
 [category],
 [name],
 [descn])
 VALUES (@i,
 floor(@i / 1000),
 'PROD' + REPLACE(str(@i),' ',''),
 'PROD' + REPLACE(str(@i),' ',''))
 SET @i = @i + 1
 COMMIT
 END

示例2：以下代码（如上所述也插入了100,000行）耗时3秒，并且在WRITELOG等待类型上等待的时间少于一秒。请注意，COMMIT语句如何位于循环外部，并且仅执行一次。

DECLARE @i INT
 SET @i = 1
 BEGIN TRANSACTION
 WHILE @i < 100000
 BEGIN
 INSERT INTO [splendidCRM].[dbo].[product]
 ([productid],
 [category],
 [name],
 [descn])
 VALUES (@i,
 floor(@i / 1000),
 'PROD' + REPLACE(str(@i),' ',''),
 'PROD' + REPLACE(str(@i),' ',''))
 SET @i = @i + 1
 END
 COMMIT

最后的想法

当发现会话正在等待WRITELOG等待类型时，必须对情况进行全面分析，包括检查磁盘性能数据以及查看所有在WRITELOG上找到的查询语句。只有这样，您才能放心，您正在解决正确的问题，而不仅仅是添加无法解决根本原因的昂贵硬件。

【2】解决思路参考

有时系统卡住，因此首先尝试清除等待状态，或查看系统日志是否磁盘有问题
1.dbcc sqlperf('sys.dm_os_wait_stats',clear)
2.分离数据库数据文件和日志文件是单独的驱动器。
3.如果日志文件大小太大，则限制其大小并插入另一个LDF。
4.如果平均磁盘队列长度大于1，则它将导致磁盘瓶颈。还需要检查DISK突袭等级。

5.设置恢复模式为简单模式