【等待优化】SQL SERVER常见等待——解决会话等待产生的系统问题

【1】常见等待

等待分类与解决基本流程：

 

 

• CXPACKET ： 当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时，可以考虑降低并行度。

• IO_COMPLETION ： 　　在等待 I/O 操作完成时出现。通常，该等待类型表示非数据页 I/O。

• PAGEIOLATCH_ : 在任务等待 I/O 请求中缓冲区的闩锁时发生。

• PAGELATCH_ : 在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。

• LCK_ :等待闩锁时出现。

• ASYNC_NETWORK_IO : 当任务被阻止在网络之后时出现在网络写入中。验证客户端是否正在处理来自服务器的数据。　

• OLEDB ：当 SQL Server 调用 Microsoft SQL Native Client OLE DB 访问接口时出现。该等待类型不用于同步。而是用于指示调用 OLE DB 访问接口的持续时间　

• WRITELOG :等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。　

 

【2】详细分析

CPU：CXPACKET 

参考：https://www.cnblogs.com/gered/p/12539368.html

CXPACKET 这个等待可以简单理解成CPU相关的等待，主要发生在并行计划中。由于并行计划需要协同多个task同时工作，那么“协同”分配等等操作的时候出现的就是这个等待。

如果 CXPACKET 在你系统中是最为严重的等待，这时候一般的表现是你的CPU很高。

　　

解决方案：适当调整并行度

　　

一般建议系统如果超过32个CPU 那么设置成8或者4，如果系统中都是特别短小且频繁的语句建议设置成1（取消语句并行，要慎重真的符合你的场景才好）

并行开销的阀值，主要控制SQL优化器何时选用并行计划，建议默认值，此值设置的越小优化器越容易选择并行计划。

并行度的设置是针对实例级别的设置（2016中可以对单独数据库设置）

IO类（IO_COMPLETION、PAGEIOLATCH_ 、WRITELOG）

　　IO_COMPLETION 和 PAGEIOLATCH_和 WRITELOG 这三个等待是最为常见的和磁盘相关的等待。

　　他们的不同点是 　　

　　　　IO_COMPLETION  主要针对非数据页 I/O ，如备份操作所需的磁盘交互。

　　　　PAGEIOLATCH_ 是数据页相关的磁盘等待。

　　　　WRITELOG 是日志相关。

　　如果系统中这三个等待是主要等待，说明系统磁盘存在压力或已经成为瓶颈。

　　PAGEIOLATCH_的 官方解释：在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“XX”模式。长时间的等待可能指示磁盘子系统出现问题。

　　　　PAGEIOLATCH_的相关等待：

 

PAGEIOLATCH_DT	在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“破坏”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_EX	在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“独占”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_KP	在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“保持”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_NL	仅供内部使用。
PAGEIOLATCH_SH	在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“共享”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_UP	在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“更新”模式。长时间的等待可能指示磁盘子系统出现问题。

     怎么来理解这个官方解释呢？　首先明确一点，操作系统CPU操作的任何数据都是从内存中读取的，也就是说读取数据要经过这样的一条路：

• 磁盘中 ——>  内存中 ——>  最终使用 　

这里的PAGEIOLATCH_ 就是发生在， 磁盘中 ——>  内存中 

　　以读取为例：要读取的数据页不在内存中，所以就要去磁盘上读取这部分数据页，去磁盘读取数据的时候就会产生PAGEIOLATCH_的相关等待，如果磁盘压力大，长时间不能反回数据，那么PAGEIOLATCH_的时间也会越长，语句执行的时间也会越长。

　　

 

注 : 当你的系统出现大量的 PAGEIOLATCH_ 类等待，说明你磁盘可能存在压力（磁盘速度不能满足当前业务需求）或你的内存不够用，不能缓存业务常用数据而经常要与磁盘交互！

WRITELOG  和磁盘有关的另一个等待状态，正在等待写日志记录，意味着写入速度也明显跟不上。而速度跟不上一般有两种情况：磁盘压力大响应时间长或真的速度不能满足读写需要。

PAGELATCH_  和 LATCH_

 

深入参考：PAGELATCH_x和PAGEIOLATCH_x介绍

PAGELATCH_和 上面讲述的PAGEIOLATCH_  看似很像，但中间少了 IO 这个关键。

• 磁盘中 ——>  内存中 ——>  最终使用  

磁盘中——>内存中 的等待为PAGEIOLATCH_   而 内存中——> 最终使用 的等待为 PAGELATCH_　当数据已经在内存中的时候SQL SERVER 想要使用这个数据页就要给这个数据页加锁。当等待中出现很多PAGELATCH_ 等待，那么可以说明：

1. SQL Server没有明显的内存和磁盘瓶颈。

2. 应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。

3. 这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。

 

TempDB造成的 PAGELATCH_（其实也是一种Hot Page），这里简单的看一个例子：

　　

系统中存在大量的 PAGELATCH_UP等待那么是什么成为了Hot Page 呢？为什么说和TempDB有关呢？

 　　　　

 等待资源 “2:X:X： ”开头是TempDB，系统中存在大量且高并发的语句使用临时表和表变量，所以引起TEMPDB瓶颈。请参见：TempDB的诊断和优化。

LCK_ 

　LCK_类型中的所有很多，如果这种等待在系统中大量存在，可以说明，系统语句间的相互阻塞严重。如大家都知道的当你update一张表的时候，你的select会被阻塞直到update完成。这里就不过多介绍场景了，主要看一下解决此类等待的主要方法：

1. 语句优化，让语句执行的更快，减少等待时间。

2. 采用批量操作代替循环方式。

3. 尽量减少事务的长度。

4. 尝试降低事务隔离级别。

5. 上述都不能缓解...请选用读写分离。

 

 LCK_类型中包含：（这里不做详细解读了）

LCK_M_RIn_NL	当某任务正在等待获取当前键值上的 NULL 锁以及当前键和上一个键之间的插入范围锁时出现。键上的 NULL 锁是指立即释放的锁。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RIn_S	当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RIn_U	任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的插入范围锁。有关锁兼容性矩阵，请参阅sys.dm_tran_locks。
LCK_M_RIn_X	当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RS_S	当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的共享范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RS_U	当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的更新范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RX_S	当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RX_U	当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_RX_X	当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_S	当某任务正在等待获取共享锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_SCH_M	当某任务正在等待获取架构修改锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_SCH_S	当某任务正在等待获取架构共享锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_SIU	当某任务正在等待获取共享意向更新锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_SIX	当某任务正在等待获取共享意向排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_U	当某任务正在等待获取更新锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_UIX	当某任务正在等待获取更新意向排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。
LCK_M_X	当某任务正在等待获取排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks。

 

ASYNC_NETWORK_IO 

　　此等待状态出现在SQLServer已经把数据准备好，但是网络没有足够的发送速度跟上，所以SQLServer的数据没地方存放。

1. 出现这种情况一般不是数据库的问题，调整数据库配置不会有大的帮助。

2. 网络层的瓶颈当然是一个可能的原因：对此要考虑是否真有必要返回那么多数据？

3. 应用程序端的性能问题，也会导致SQLServer里的ASYNC_NETWORK_IO等待。如果见到了这个类型的等待，就要检查应用程序的健康状况，也要检查应用是否有必要想SQLServer申请这么大的结果集。

4. 程序返回结果集的方式 。

WRITE LOG

参考：https://www.cnblogs.com/gered/p/12449594.html

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

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

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

WRITELOG等待类型：解决问题

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

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

解决思路：

有时系统卡住，因此首先尝试清除等待状态，或查看系统日志是否磁盘有问题

1.dbcc sqlperf('sys.dm_os_wait_stats',clear)

2.分离数据库数据文件和日志文件是单独的驱动器。

3.如果日志文件大小太大，则限制其大小并插入另一个LDF。

4.如果平均磁盘队列长度大于1，则它将导致磁盘瓶颈。还需要检查DISK突袭等级。

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

 

【参考文档】

【1】-【5】转自： https://blog.csdn.net/z_cloud_for_SQL/article/details/55051215

官网：sys.dm_os_wait_stats ：https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver15