系统隐形杀手——阻塞与等待(SQL)

前言

应用系统承载着大量的业务，随之而来的是复杂的业务逻辑，在数据库上的表现就是有着大量的不同种类的SQL语句。

SQL语句执行的快慢又与阻塞等待有着密不可分的原因。

系统慢可能有很多种原因，硬件资源不足，语句不优化，结构设计不合理，缺少必要的运维方式。所有的这些问题都可以在阻塞与等待中看出端倪，发现并解决问题。

今天这篇我们主要讲述怎么样发现并解决系统的阻塞和等待。

场景描述

您的系统是否有这样的问题？

  1. 系统运行缓慢，很多功能需要几十秒才能呈现结果，用户体验极差，领导们不断施压，作为系统的负责人，只知道系统慢又不知道慢在哪里？我们迟迟不能解决问题，领导已经对我们怨声载道了或者已经慢习惯了，不再反馈了。
  2. 系统的功能运行缓慢，在生产环境中语句运行时间很长，但是在测试环境或者单独拿出这条语句运行的却很快？这好像不科学呀？
  3. 我对数据有较多的了解，我能查出系统的等待，但是我不知道这些等待意味着什么，百度的答案五花八门解决不了我的问题。
  4. 我能找到等待，也能解决这部分等待，但只是通过一些脚本，不能全面了解现状，只能东一锤子西一棒子的游击战。
  5. 我是专家问题我都能解决，但不能给领导一个直观的展现。

系统等待简介

一个好的SQL语句就好比一辆时速180的好车，好的系统硬件（CPU，内存，磁盘）就好比平坦宽阔的马路。看似好车配好路，一定可以开的很快了！其实还忽略了一点！当你驾驶一辆法拉利跑在北京宽阔的三环上，就算你是老炮中的“三环十二少“，早高峰你能开到多少? 北京的早高峰！北京的早高峰！

这个例子就引出了系统阻塞和等待的概念，红灯（硬件等待，如IO等待），这就是正常的等待。另外一辆车在你前面不走了或开的很慢，那么你也只能等待（也可以说成你被他阻塞了）！

一张图告诉你系统的主要等待类型及解决思路：

问题诊断

任何问题的诊断都要从全局的角度考虑，最忌讳的就是看到一个指标高就冒然定位问题，然后以偏概全的去分析问题。

一个问题点可能涉及到很多部分，所以我们首先要从全局的角度定位系统问题，阻塞也是一样，到底系统中存在哪些类型的阻塞，哪些是主因，哪些是关联原因，哪些是次要的。

全局定位阻塞与等待　　

首先我们要关心数据库中有哪些等待类型

注：这部分呈现的是系统中的等待情况，和使用脚本类似，已经排除了不必要关心的类型，同时对等待情况进行归类统计。

横坐标：等待类型

纵坐标：收集时间段内出现的次数

知道了等到类型，我们要了解这些类型中，哪种占用了大量的时间：

注：各种等待类型所等待的时间也是排查的主要方向，结合等待类型与等待时间，我们能了解到：系统中有哪些等待，哪些等待比较严重，哪个最严重。

横坐标：等待类型

纵坐标：平均等待时间

了解了主要的等待类型和时间，我们还要分析一下：什么数据库来的？哪些程序来的？什么用户请求导致的？什么时间阻塞最严重？

具体语句看等待

系统的整体等待情况了然于心，下面我们改看看具体哪些语句造成的等待，这也是解决问题的重要分析步骤。

哪些语类句等待最频繁

注：这里我们可以根据等待次数、等待时间、消耗的各种资源排序，来多维度分析阻塞的语句类型

语句具体的等待情况时怎样的呢？我们可以通过【原始视图】查看具体语句在执行过程中的真实阻塞情况

注：在阻塞的详细视图中我们可以清晰的看到语句的阻塞树，并且可以看到阻塞的语句、时间、资源已经阻塞等待的类型

阻塞树：本例中【会话68】被【会话66】阻塞，而【会话66】又被【会话104】阻塞，这样3个会话就构成了一个阻塞链也叫阻塞树

诊断结论

通过全局定位，语句类型分析，到具体的语句执行阻塞状态，根据阻塞类型、次数、时间、连接程序、资源消耗等多种维度综合分析，我们可以清楚的看出数据库中的阻塞问题。

本例中系统主要的阻塞类型为CXPACKET和LCK_M_U，阻塞时间很长，主要的阻塞产生时间为上午十一点左右，主要的阻塞语句是一条update 和一个复杂的select查询等信息。

问题解决

首先下面的这张图已经简单的说明了系统对应的等待需要怎么样的解决思路。　　

注：根据不同的情况降低阻塞的办法主要有：调整服务器、实例、数据库配置参数（如：调整并行度），更改隔离级别（如：快照读，nolock等），优化语句（如：添加索引，优化写法等）

本例中主要的CXPACKET是因为实例并行度参数配置不佳而导致，LCK_M_U主要是一条update被一个批处理的另一条update阻塞锁导致，优化update这类更新语句主要是保证update语句最优化，执行时间尽量缩短，另外高并发下的update比较常见的解决办法是使用索引利用key锁取代表锁以提高并发，可能被更新的表只有几十条记录，添加索引与不加索引的并发效率差别也会很大。另外程序的设计也是非常重要的，各种奥秘各位看官只能在实际环境中慢慢体会了，而使用SQL专家云工具的主要目的在于全面的定位问题，图表统计等形式清晰的展现问题，并根据工具提供的解决方案快速解决问题。

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持我们！

赞 (0)

相关推荐

  • sqlserver中几种典型的等待

    sqlserver中几种典型的等待

    为了准备今年的双11很久没有更新blog,在最近的几次sqlserver问题的排查中,总结了sqlserver几种典型的等待类型,类似于oracle中的等待事件,如果看到这样的等待类型时候能够迅速定位问题的根源,下面通过一则案例来把这些典型的等待处理方法整理出来: 第一种等待.memory等待 早上接到一用户反馈其RDS实例非常的慢,通过观察sqlserver活动会话监视器(active monitor)的waiting tasks(类似于mysql的thread running)可以看到有10

  • SQL语句练习实例之三——平均销售等待时间

    复制代码 代码如下: ---1.平均销售等待时间 ---有一张Sales表,其中有销售日期与顾客两列,现在要求使用一条SQL语句实现计算 --每个顾客的两次购买之间的平均天数 --假设:在同一个人在一天中不会购买两次 create table sales ( custname varchar(10) not null, saledate datetime not null ) go insert sales select '张三','2010-1-1' union select '张三','20

  • SQL Server误区30日谈 第2天 DBCC CHECKDB会导致阻塞

    误区 #2: DBCC CHECKDB会引起阻塞,因为这个命令默认会加锁 这是错误的! 在SQL Server 7.0以及之前的版本中,DBCC CHECKDB命令的本质是C语言实现的一个不断嵌套循环的代码并对表加表锁(循环嵌套算法时间复杂度是嵌套次数的N次方,作为程序员的你懂得),这种方式并不和谐,并且-.. 在SQL Server 2000时代,一个叫Steve Lindell的哥们(现在仍然在SQL Server Team)使用分析事务日志的方法来检查数据库的一致性的方式重写了DBCC C

  • mysql的udf编程之非阻塞超时重传

    MySQL的UDF(User Defined Function)类似于一种API, 用户根据一定的规范用C/C++(或采用C调用规范的语言)编写一组函数(UDF),然后编译成动态链接库,通过DROP FUNCTION语句来加载和卸载UDF.UDF被加载后可以像调用MySQL的内置函数一样来调用它,并且服务器在启动时会自动加载原来存在的UDF. 复制代码 代码如下: #ifdef STANDARD/* STANDARD is defined, don't use any mysql functio

  • SQL2008中SQL应用之－阻塞(Blocking)应用分析

    通常短时间的阻塞没有问题,且是较忙的应用程序所需要的.然而,设计糟糕的应用程序会导致长时间的阻塞,这就不必要地锁定了资源,而且阻塞了其他会话读取和更新它们. 在SQL Server中,一个阻塞的进程会无限期地保持阻塞,或者直到它超时(根据set lock_timeout).服务器关闭.进程被杀死.连接完成了更新或者其他发生在原始事务上的操作导致它释放了资源上的锁. 发生长时间阻塞的原因如下: 1.在一个没有索引的表上的过量的行锁会导致SQL Server得到一个锁,从而阻塞其他事务. 2.应用程

  • SQL语句实现查询当前数据库IO等待状况

    sys.dm_io_pending_io_requests可以返回当前IO Pending的状态,对于SQL Server 中每个挂起的I/O 请求,返回与其对应的一行,跟sys.dm_io_virtual_file_stats配合可以看到具体是哪个数据库IO出现问题. select DB_NAME(database_id) as DBNAME, database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address from

  • sql server 2000阻塞和死锁问题的查看与解决方法

    数据库发生阻塞和死锁的现象: 一.数据库阻塞的现象:第一个连接占有资源没有释放,而第二个连接需要获取这个资源.如果第一个连接没有提交或者回滚,第二个连接会一直等待下去,直到第一个连接释放该资源为止.对于阻塞,数据库无法处理,所以对数据库操作要及时地提交或者回滚.二.数据库死锁的现象:第一个连接占有资源没有释放,准备获取第二个连接所占用的资源,而第二个连接占有资源没有释放,准备获取第一个连接所占用的资源.这种互相占有对方需要获取的资源的现象叫做死锁.对于死锁,数据库处理方法:牺牲一个连接,保证另外

  • SqlServer中如何解决session阻塞问题

    SqlServer中如何解决session阻塞问题

    简介 对于数据库运维人员来说创建session或者查询时产生问题是常规情况,下面介绍一种很有效且不借助第三方工具的方式来解决类似问题. 最近开始接触运维工作,所以自己总结一些方案便于不懂数据库的同事解决一些不太紧要的数据库问题.类似方法很多理论也很多,我就不做深究,就是简单写一个方案,便于菜鸟使用的. 阻塞理解 在Sql Server 中当一个数据库会话中的事务正锁定一个或多个其他会话事务想要读取或修改的资源时,会产生阻塞(Blocking).通常短时间的阻塞没有问题,且是较忙的应用程序所需要的

  • 利用sys.sysprocesses检查SqlServer的阻塞和死锁

    MSDN:包含正在 SQL Server 实例上运行的进程的相关信息.这些进程可以是客户端进程或系统进程. 视图中主要的字段: 1. Spid:Sql Servr 会话ID 2. Kpid:Windows 线程ID 3. Blocked:正在阻塞求情的会话 ID.如果此列为 Null,则标识请求未被阻塞 4. Waittype:当前连接的等待资源编号,标示是否等待资源,0 或 Null表示不需要等待任何资源 5. Waittime:当前等待时间,单位为毫秒,0 表示没有等待 6. DBID:当前

  • 系统隐形杀手——阻塞与等待(SQL)

    系统隐形杀手——阻塞与等待(SQL)

    前言 应用系统承载着大量的业务,随之而来的是复杂的业务逻辑,在数据库上的表现就是有着大量的不同种类的SQL语句. SQL语句执行的快慢又与阻塞等待有着密不可分的原因. 系统慢可能有很多种原因,硬件资源不足,语句不优化,结构设计不合理,缺少必要的运维方式.所有的这些问题都可以在阻塞与等待中看出端倪,发现并解决问题. 今天这篇我们主要讲述怎么样发现并解决系统的阻塞和等待. 场景描述 您的系统是否有这样的问题? 系统运行缓慢,很多功能需要几十秒才能呈现结果,用户体验极差,领导们不断施压,作为系统的负责

  • SQL Server 移动系统数据库

    SQL Server 移动系统数据库

    说到这个问题,基本上有人就会想到三个问题: 1,什么是系统数据? 2,为什么要移动系统数据库? 3,移动系统数据库我们可以用附加和分离,为什么还要单独拿出来说呢? 对于这三个问题我一个一个讲吧,也算是自己做个笔记. 1,什么是系统数据? 所谓系统数据库就是我们在装SQL Server之后,系统自带的数据库(这样的回答是不是很白痴^_^). 如果你装SQL Server2005或2008在打开一个SQL实例后,就会看到一个数据库--->系统数据库文件夹,里边就是系统自带的数据库,如图: 对于每一个

  • 在WINXP系统上安装SQL Server企业版的方法

    先讲一下注意事项:必须启用"文件和打印共享"才能成功安装MSDE.否则安装进程可能会继续进行直到几乎完成, 然后又意外回滚,无法完成MSDE的安装. 我上次安装失败就是这个原因,平时我总是关闭SERVER服务,也就没有什么共享.顺便提一句,据说你试着把计算机名改成大写字母,那在安装SQL的时候就不会失败了,但是我没验证! XP系统下也可以安装 企业版本 安装办法如下: 一．在SQL服务器的安装盘中找到MSDE这个目录,并且点击setup.exe安装它,过程简单直接下一步就OK了.(这个

  • Linux系统通过Docker安装SQL Server数据库

    Linux系统通过Docker安装SQL Server数据库

    目录 一.前言 二.安装SQL Server 1.拉取SQL Server镜像 2.创建目录 3.运行容器 4.使用命令进入SQL Server 5.使用SSMS登录SQL Server数据库 三.总结 一.前言 现在.NET Core已经跨平台了,支持Windows.Linux.Mac系统,而我们也已经在Linux上面使用了Docker.使用.NET开发的人使用最多的就是SQL Server数据,以前是只能在Windows系统上使用,但是从SQL Server 2017开始支持运行在docke

  • Oracle 阻塞(blocking blocked)介绍和实例演示

    阻塞是DBA经常碰到的情形,尤其是不良的应用程序设计的阻塞将导致性能严重下降直至数据库崩溃.对DBA而言,有必要知道如何定位到当前系统有哪些阻塞,到底谁是阻塞者,谁是被阻塞者.本文对此给出了描述并做了相关演示. 1.阻塞及其类型 a.什么是阻塞    一个会话持有某个资源的锁,而另一个会话在请求这个资源,就会出现阻塞(blocking).也就是说新的会话会被挂起,直到持有锁的会话放弃锁定的资源.大多数情况下,在一个交互式应用中被严重阻塞,即可表明应用逻辑有问题,这才是阻塞的根源. b.阻塞得类型

  • Oracle阻塞(blockingblocked)实例详解

    一.概述: 阻塞是DBA经常碰到的情形,尤其是不良的应用程序设计所造成的阻塞将导致数据库性能的严重下降,直至数据库崩溃.对DBA而言,有必要知道如何定位到当前系统有哪些阻塞,到底谁是阻塞者,谁是被阻塞者.本文对此给出了描述并做了相关演示. 二.演示阻塞: --更新表,注,提示符scott@CNMMBO表明用户为scott的session,用户名不同,session不同. scott@CNMMBO> update emp set sal=sal*1.1 where empno=7788; 1 ro

  • Oracle中常见的33个等待事件小结

    一． 等待事件的相关知识 1.1 等待事件主要可以分为两类,即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件.1). 空闲等待事件指ORACLE正等待某种工作,在诊断和优化数据库的时候,不用过多注意这部分事件.2). 非空闲等待事件专门针对ORACLE的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件 是在调整数据库的时候需要关注与研究的. 在Oracle 10g中的等待事件有872个,11g中等待事件1116个. 我们可以通过v$event_name 视图来查看等待事件

  • 关于喜忧参半的SQL Server触发器详解

    关于喜忧参半的SQL Server触发器详解

    前言 SQL Server触发器在非常有争议的主题.它们能以较低的成本提供便利,但经常被开发人员.DBA误用,导致性能瓶颈或维护性挑战. 本文简要回顾了触发器,并深入讨论了如何有效地使用触发器,以及何时触发器会使开发人员陷入难以逃脱的困境. 虽然本文中的所有演示都是在SQL Server中进行的,但这里提供的建议是大多数数据库通用的.触发器带来的挑战在MySQL.PostgreSQL.MongoDB和许多其他应用中也可以看到. 什么是触发器 可以在数据库或表上定义SQL Server触发器,它允

  • 解决sql server2008注册表写入失败，vs2013核心功能安装失败

    前天入手了一块三星SSD,然后迫不及待的更换到电脑上,结果就在安装系统的时候出了差错,整个过程折磨了我整整一天,让我深深地感受到了来自地狱的恶意. 既然得到了块好硬盘,就打算装上win10系统,把之前的win8.1系统留着备用,可是安装系统选择分区的时候选错了区,选择了之前win8.1所在的C盘,ghost一键装机之后我才发现好像选择错了分区,赶紧点击取消,原以为会没事,后来发现C盘已经被格式化了,他奶奶个腿的,重新选择分区之后一直顺利的安装好了系统. 然后安装工作软件sql server200

随机推荐