SQL Server 监控统计阻塞脚本信息

    数据库产生阻塞（blocking）的本质原因

：sql语句连续持有锁的时间过长 ，数目过多， 粒度过大。阻塞是事务隔离带来的副作用，它是不可避免的，而且是一个数据库系统常见的现象。

但是阻塞的时间和出现频率要控制在一定的范围内，阻塞持续的时间过长或阻塞出现过多（过于频繁），就会对数据库性能产生严重的影响。

    很多时候，dba需要知道数据库在出现性能问题时，有没有发生阻塞？ 什么时候开始的？发生在那个数据库上？ 阻塞发生在那些sql语句之间？ 阻塞的时间有多长？ 阻塞发生的频率？ 阻塞有关的连接是从那些客户端应用发送来的？.......

    如果我们能够知道这些具体信息，我们就能迅速定位问题，分析阻塞产生的原因，  从而找出出现性能问题的根本原因，并根据具体原因给出相应的解决方案（索引调整、优化sql语句等）。

方法1：查看那个引起阻塞，查看blk不为0的记录，如果存在阻塞进程，则是该阻塞进程的会话 id。否则该列为零。

    exec sp_who active

方法2：查看那个引起阻塞，查看字段blkby，这个能够得到比sp_who更多的信息。

    exec sp_who2 active

方法3：sp_lock 系统存储过程，报告有关锁的信息，但是不方便定位问题

方法4：sp_who_lock存储过程

方法5：右键服务器-选择“活动和监视器”，查看进程选项。注意“任务状态”字段。

方法6：右键服务名称-选择报表-标准报表-活动-所有正在阻塞的事务。

但是上面方法，例如像sp_who、 sp_who2，sp_who_lock等，都有或多或少的缺点：例如不能查看阻塞和被阻塞的sql语句。不能从查看一段时间内阻塞发生的情况等；没有显示阻塞的时间....... 我们要实现下面功能：

    1:  查看那个会话阻塞了那个会话

    2：阻塞会话和被阻塞会话正在执行的sql语句

    3：被阻塞了多长时间

    4：像客户端ip、proagram_name之类信息

    5：阻塞发生的时间点

    6：阻塞发生的频率

    7：如果需要，应该通知相关开发人员，dba不能啥事情都包揽是吧，那不还得累死，总得让开发人员员参与进来优化（有些问题就该他们解决），多了解一些系统运行的具体情况，有利于他们认识问题、解决问题。

    8：需要的时候开启这项功能，不需要关闭这项功能

于是为了满足上述功能，有了下面sql 语句

我们做一个测试例子来验证一下

1：打开第一会话窗口1，执行下面语句

2：打开第二个会话窗口2，执行下面语句

3：打开第三个会话窗口3，执行下面语句

如下图所，我们可以看到阻塞其它会话以及被阻塞会话的信息，如下所示

现在上面sql已经基本实现了查看阻塞具体信息的功能，但是现在又有几个问题：

          1：上面sql脚本只适合已经出现阻塞情况下查看阻塞信息，如果没有出现阻塞情况，我总不能傻傻的一直在哪里点击执行吧，因为阻塞这种情况有可能在那段时间都不会出现，只会在特定的时间段出现。

          2：我想了解一段时间内数据库出现的阻塞情况，那么需要将阻塞信息保留下来。

         3：有时候忙不过来，我想将这些具体阻塞信息发送给相关开发人员，让他们了解具体情况。

于是我想通过一个存储过程来实现这方面功能，通过设置参数@outtype，默认为输出阻塞会话信息，当参数为"table" 时，将阻塞信息写入数据库表，如果参数为 "email"表示将阻塞信息通过邮件发送开发人员。

正好这段时间，我在yoursqldba上扩展一些功能，于是我将这个存储过程放置在yousqldba数据库中。

存储过程如下所示：

最后在数据库新建一个作业，调用该存储过程，然后在某段时间启用作业监控数据库的阻塞情况，作业的执行频率是个比较难以定夺的头痛问题，具体要根据系统情况来决定，我习惯2分钟执行一次。

最后，这个脚本还有一个问题，如果阻塞或被阻塞的sql语句是某个存储过程里面的一段脚本，显示的sql是

整个存储过程，而不是正在执行的sql语句，目前还没有想到好的方法解决这个问题。我目前手工去查看阻塞情况，如果非要查看存储过程里面被阻塞的正在执行

的sql，一般结合下面sql语句查看（输入阻塞或被阻塞会话id替代@sessionid）