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

摘要:
如果针对该等待类型的争用成为问题时,可以考虑降低并行度。并行度的设置是针对实例级别的设置IO类IO_COMPLETION和PAGEIOLATCH_和WRITELOG这三个等待是最为常见的和磁盘相关的等待。PAGEIOLATCH_是数据页相关的磁盘等待。如果系统中这三个等待是主要等待,说明系统磁盘存在压力或已经成为瓶颈。长时间的等待可能指示磁盘子系统出现问题。闩锁请求处于“破坏”模式。PAGEIOLATCH_EX在任务等待I/O请求中缓冲区的闩锁时发生。尽量减少事务的长度。
SQL SERVER——解决会话等待产生的系统问题
转自:https://blog.csdn.net/z_cloud_for_SQL/article/details/55051215
版权声明:SQL专家云- 国内唯一的SQL Server体检、诊断、监控一体化平台 注册用户即可永久免费使用 https://blog.csdn.net/z_cloud_for_SQL/article/details/55051215
等待分类与解决基本流程:
sql server等待,sql server常见等待

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

步骤1.定位问题 系统等待往往能直观的反映出系统问题。通过一些常见的等待类型,同样可以找到系统瓶颈,结合性能计数器往往定位更准确。
如:系统中存在大量IO类等待,那么可能表示你的磁盘或内存是语句运行缓慢的原因,也是系统的瓶颈所在。
常见的等待类型
      • 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.分析
问题与解决

CXPACKET

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

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

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

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

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

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

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

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

IO类

IO_COMPLETION和PAGEIOLATCH_和WRITELOG这三个等待是最为常见的和磁盘相关的等待。他们的不同点是IO_COMPLETION 主要针对非数据页 I/O,如备份操作所需的磁盘交互。PAGEIOLATCH_ 是数据页相关的磁盘等待。WRITELOG 是日志相关。

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

这里用PAGEIOLATCH_ 为例进行说明

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_的时间也会越长,语句执行的时间也会越长。

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

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

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

PAGELATCH_

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

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

磁盘中——>内存中的等待为PAGEIOLATCH_ 而内存中——>最终使用 的等待为PAGELATCH_

当数据已经在内存中的时候SQL SERVER 想要使用这个数据页就要给这个数据页加锁。

当等待中出现很多PAGELATCH_ 等待,那么可以说明:

  1. SQL Server没有明显的内存和磁盘瓶颈。
  2. 应用程序发来大量的并发语句在修改同一张表格里的记录,而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面,或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。
  3. 这种瓶颈是无法通过提高硬件配置解决的,只有通过修改表格设计或者业务逻辑,让修改分散到尽可能多的页面上,才能提高并发性能。

TempDB造成的PAGELATCH_(其实也是一种Hot Page),这里简单的看一个例子:

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

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

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

等待资源 “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. 程序返回结果集的方式 。

免责声明:文章转载自《【等待优化】SQL SERVER常见等待——解决会话等待产生的系统问题》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇【转载】只有.dbf数据文件进行数据库恢复【Amba系列】之安霸新推出的计算机视觉芯片CV22简介下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

MySQL Error--Error Code

mysql error code(备忘)1005:创建表失败1006:创建数据库失败1007:数据库已存在,创建数据库失败1008:数据库不存在,删除数据库失败1009:不能删除数据库文件导致删除数据库失败1010:不能删除数据目录导致删除数据库失败1011:删除数据库文件失败1012:不能读取系统表中的记录1020:记录已被其他用户修改1021:硬盘剩余...

达梦数据库:第一章:MySQL数据库与达梦数据库的区别

达梦数据库管理系统是达梦公司推出的具有完全自主知识产权的高性能数据库管理系统,简称DM,它具有如下特点: 1、通用性达梦数据库管理系统兼容多种硬件体系,可运行于X86、X64、SPARC、POWER等硬件体系之上。DM各种平台上的数据存储结构和消息通信结构完全一致,使得DM各种组件在不同的硬件平台上具有一致的使用特性。达梦数据库管理系统产品实现了平台无关性...

mysql之面试问题总结

问题1、char 与varchar的区别?        varchar是变长而char的长度是固定的。如果你的内容是固定的大小,char性能更好。       char[4] 与varchar[4]   存储字母a a占一个字符  varchar长度为4但是占此时为一个字符长度   而char则占4个字符 问题2、truncate 和delete 区别是...

mysql三种删除方式

一般来说mysql有三种删除数据方式: 1. delete(常用) 2. truncate(慎用) 3. drop 以上三种方式都可以删除数据,但是使用场景是不同的。 从执行速度来说: drop > truncate >> DELETE 深入底层来说: 一、DELETE DELETE是属于数据库的DML操作语言,使用delete删除数据时...

(十三)MySQL锁机制

1.常见问题 MySQL支持的锁有哪些?有哪些使用场景? 什么是读写锁?什么是排他锁? 行锁是什么?有哪些分类,原理是什么? 死锁是如何产生的? 如何解决死锁? 2.锁的分类 从锁的粒度上分MySQL支持的锁 表级锁 行级锁(InnoDB) 页级锁(BDB) 从锁的操作上可以分为 读锁 写锁 从实现方式上分 乐观锁 悲观锁 使...

statement 、prepareStatement的用法和解释

转自:http://blog.csdn.net/QH_JAVA/article/details/48245945 一、prepareStatement 的用法和解释 1.PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程 2.使用 Statement 对象。在对数据库只执行一次性存取的时侯,用 Stateme...