【精华】SQL SERVER——阻塞与等待

前言

　　应用系统承载着大量的业务，随之而来的是复杂的业务逻辑，在数据库上的表现就是有着大量的不同种类的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专家云工具的主要目的在于全面的定位问题，图表统计等形式清晰的展现问题，并根据工具提供的解决方案快速解决问题。

　　如果是想学习的看官也可以在体检的【检查项】及平台的帮助中了解更多的知识和更完善的思路，同时也可以拿着这份“电子病历”和更多人交流学习。