如何捕获阻塞事件以及捕获Showplan XML数据|性能测试提升篇

作者：川石学院　日期：2021-07-13 16:10:37　点击数：

　　做性能测试的小伙伴应该都知道在SQL Server 2005之前的版本，分析哪些进程产生阻塞以及哪些进程被阻塞，都需要使用脚本手工执行。现在使用SQL Profiler可以直接监控阻塞的情况，使用SQL Profiler监控哪些进程被阻塞，首先需要确定SQL Trace的事件类，监控阻塞的事件类为Errors and Warnings-Blocked process report，如图11-16所示。　　

如何捕获阻塞事件以及捕获Showplan XML数据|性能测试提升篇(图1)

　　图11-16 Blocked process report事件类

　　Blocked process report：表示某个任务已被阻塞，导致超过指定的时间，此事件类不包括系统任务和正在等待未发现死锁(non-deadlock-detectable)的资源任务。若要配置报告的频率，请使用 sp_configure 命令配置阻塞的进程阈值选项。

　　通过sp_configure命令可以重新配置“blocked process threshold”选项的值，单位为秒，但默认情况下并不会显示阻塞进程的具体报告，下面是一个实例，修改默认情况下阻塞进程的报告并将“blocked process threshold”选项的值设置为12，即每12秒钟报告一次阻塞事件，脚本如下：

　　sp_configure 'show advanced option', 1
　　go
　　reconfigure
　　go
　　sp_configure 'blocked process threshold', 12
　　go
　　reconfigure
　　go
　　sp_configure

　　运行结果如图11-17所示。

　　如何捕获阻塞事件以及捕获Showplan XML数据|性能测试提升篇(图2)

　　图11-17 运行结果

　　实例：创建一个如图11-18所示的简单的表结构，表名为test。

　　图11-18 test表结构

　　按上面的配置，创建一个SQL Trace跟踪，之后打开一个查询窗口，执行如下更新的脚本：

　　begin tran
　　update TEST
　　set name = 'abc123'
　　where id = 1

　　再打开第二个查询窗口，执行查询操作，查询的是同一行数据，脚本如下：

　　select *
　　from test
　　where id = 1

　　由于第一个查询窗口中开始了一个事务并且一直没有将该事务关闭，所以导致第二个查询语句脚本阻塞，如果出现阻塞，SQL Profiler会每隔10秒报告一次阻塞事件的情况，跟踪的结果如图11-19所示。

　　如何捕获阻塞事件以及捕获Showplan XML数据|性能测试提升篇(图4)

　　图11-19 跟踪事件报告

　　该报告是以XML的格式显示，在该报告中详细的显示产生阻塞的进程以及被阻塞的进程。

　　捕获Showplan XML数据

　　在数据库调优过程中，分析执行计划前对执行计划进行优化是很重要的内容，在SQL Server中通常有两种方式可以捕获执行计划的相关信息：SQL Server Management Studio和SQL Server Profiler的Showplan XML事件类。

　　1) SQL Server Management Studio

　　提供显示实际执行计划和显示估计执行计划等功能，这将用图形方式来呈现计划，这些功能为直接检查提供了最适合的解决方案，是目前最常用的显示和分析执行计划的方法。

　　使用SQL Server Management Studio连接数据库服务器，新建一个查询号窗口，选中查询语句单击右键，在弹出菜单中选择“显示估计的执行计划”选项，如图11-20所示。

　　如何捕获阻塞事件以及捕获Showplan XML数据|性能测试提升篇(图5)

　　图11-20 显示估计执行计划

　　之后会生成一个图形计划，如图11-21所示。

　　图11-21 图形查询计划

　　图形查询计划阅读的方法是由右至左，通常由底部向上，最左边、最上方的步骤是计划中的最后步骤，阅读图形计划需要注意以下几个方面的内容：

　　计划中的每个步骤由图标表示，不同的图标表示不同的含义;

　　计划中每个步骤都有一个开销百分比，表示该步骤占整个查询计划成本的百分比;

　　查询计划会在查询框中为每条语句显示一个执行计划，这些计划会在结果框中顺序列出，每个计划均有相对于批处理的成本，这个成本是通过百分比表示;

　　步骤之间通过箭头连接，显示查询执行时采用的顺序以及操作之间的数据流;

　　将光标悬停在任意步骤之上时，会弹出一个信息框，显示指定步骤的详细信息和影响到的记录条数;

　　步骤间的连接箭头有着不同的厚度，其厚度表示每个步骤之间移动数据行的数量、大小等相对成本，厚度越大表示相对成本越高，通过这个指示器可以快速的衡量查询计划的成本，如果返回过多的数据行，那么说明当前的查询计划可能不是最优的;

　　1) SQL Server Profiler中的Showplan XML事件类

　　当Microsoft SQL Server执行SQL语句时，会发生Showplan XML事件类，包括Showplan XML事件类以标识Showplan运算符，此事件类将每个事件存储为定义完善的XML文档。

　　注意：使用SQL Server Profiler中的Showplan XML事件类可以查看查询执行计划，当跟踪中包含Showplan XML事件类时，其开销将明显影响性能，查询优化后，Showplan XML将存储查询计划，若要将引起的开销降到最低，请将限制该事件类，仅在监视主要时段内的特定问题的跟踪中使用它。

　　在配置Profiler跟踪时，使用新的事件类Showplan XML，它包含以下事件的设置：