性能测试—SQL Profiler监控查询(下)

作者：川石学院　日期：2021-07-05 15:18:11　点击数：

上一篇我们讲解了“性能测试—SQL Profiler监控查询(上)”的内容，本章节我们接着上一篇继续介绍性能测试—SQL Profiler监控查询(下)的内容。

五、捕获死锁图

　　死锁有时也称为抱死，不只是关系数据库管理系统，任何多线程系统上都会发生死锁，并且对于数据库对象的锁之外的资源也会发生死锁。例如，多线程操作系统中的一个线程要获取一个或多个资源(例如，内存块)。如果要获取的资源当前为另一线程所拥有，则第一个线程可能必须等待拥有线程释放目标资源，这就是说，对于该特定资源，等待线程依赖于拥有线程，在数据库引擎实例中，当获取非数据库资源(例如，内存或线程)时，会话会出现死锁。

性能测试—SQL Profiler监控查询(下)(图1)

　　SQL Server中有一个锁管理器负责检测死锁，当检测到死锁时，为了打破死锁，锁管理器会选择一个SPID作为牺牲者，锁管理器会取消牺牲SPID当前的批作业，回滚它的事务。

　　如果经常出现死锁，那么数据库的性能将会受到影响，通过配置SQL Server Profiler跟踪，可以捕获数据库列锁事件的相关信息并进行分析，

　　关于死锁跟踪，应该包含以下事件的设置，如图11-24所示：

　　Locks-Deadlock graph：提供死锁的XML描述，这个类和Lock:Deadlock事件类同时发生;
　　Locks-Lock:Deadlock：标识哪个SPID被选为死锁牺牲者;
　　Locks-Lock:Deadlock Chain：监控死锁状况何时发生;
　　Stored Procedures-RPC:Completed：指示一个远程过程调用已经完成;
　　TSQL-SQL:BatchCompleted：指示Transact-SQL批作业已经完成;

　　图11-24 死锁事件Profiler配置

　　实例：创建一张如图11-18所示的简单在表结构，接下来使用脚本来触发死锁，打开一个查询窗口(查询1)，输入以下代码：

　　use test
　　set nocount on
　　select @@spid as spid
　　begin tran
　　update test
　　set name = 'efg123'
　　where id = 1
　　waitfor delay '00:0:30'
　　update test
　　set name = 'abc456'
　　where id = 2

　　以上代码，在一个事务中有两个T-SQL UPDATE语句，第一UPDATE语句是修改ID号为1的数据行，并等待30秒，第二个UPDATE修改ID号为2的数据行，暂时先不执行这段代码。

　　在打开另一个查询窗口(查询2)并输入以下代码：

　　use test
　　set nocount on
　　select @@spid as spid
　　begin tran
　　update test
　　set name = 'abc456'
　　where id = 2
　　waitfor delay '00:0:30'
　　update test
　　set name = 'efg123'
　　where id = 1

　　这段代码的逻辑以第一个查询窗口类似，第一UPDATE语句是修改ID号为2的数据行，并等待30秒，第二个UPDATE修改ID号为1的数据行，暂时先不执行这段代码。

　　现在执行如下步骤：

　　1) 配置好SQL Server Profiler并启动;
　　2) 执行查询1窗口中的代码;
　　3) 在查询1窗口开始执行的30秒时间内，执行查询2中的代码。

　　查询1执行的结果如图11-25所示，本实例中SPID号为54。

　　图11-25 查询1执行的结果

　　查询2执行的结果如图11-26所示。

　　图11-26 查询2执行的结果

　　查询2执行时，显示如下错误信息：

　　消息1205，级别13，状态45，第5 行

　　事务(进程ID 53)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

　　SQL Server Profiler捕获到的死锁事件信息，如图11-27所示。

　　图11-27 Profiler捕获的死锁事件

　　在死锁图中，显示了SPID53是牺牲者，并且被删除，当光标移动圆圈内时，会显示所执行的语句。

　　如果对上例的查询语句进行修改，使两次更新ID号的顺序一致，那么则不会发生死锁现象。

　　六、 SQL Profiler识别长时间查询

　　在很多情况下需要监控长时间运行查询的结果，在一些时候数据库用户可能会抱怨执行查询的响应时间并不一致，有时快点，有时慢点，当查询慢的时候就会导致应用程序超时。通过SQL Server Profiler可以监控哪个查询的时间最长或哪个查询时间最短。使用SQL Server Profiler可以监控负荷中的数据库长时间运行的查询，使用SQL Profiler识别长时间查询包括四个步骤：确定监控、设置模板、跟踪、分析与调校。

　　1) 确定监控

　　在监控之前需要确定两个问题：第一确定事件类;第二设置筛选器;

　　事件类必须与待分析的问题匹配，查询执行的时间显示在Duration列，通常使用的事件类如下：

　　Stored Procedures-RPC:Completed：在完成远程过程调用时发生，该事件可以捕获客户端调用的存储过程。

　　Stored Procedures-SP:StmtCompleted：指示存储过程中的 Transact-SQL 语句已完成执行。

　　TSQL- BatchCompleted：表示存储过程内部的 Transact-SQL 语句完成时发生。

　　筛选器设置主要需要确定使用哪能些筛选器以及确定哪些阀值，目的是在跟踪运行时更好的收集准确数据。如何确定筛选器中的阀值是设置筛选器的重点，通常可以使用这种方法，首先测试系统处于小负荷状态下，各查询所消耗的时间，将所消耗的时间记录下来，假设查询消耗的时间绝对大部分都大于2秒，那么可以将该值定义为阀值，这样可以屏蔽查询时间少于2秒的查询，可以更好的收集数据。

　　2) 设置模板

　　第一步：启动SQL Profiler，单击【文件】菜单，在下拉菜单中选择【新建跟踪】选项，弹出跟踪属性对话框。
　　第二步：在跟踪属性对话框中选择“常规”标签页，单击【使用模板】下拉框，选择“Standard(默认值)”选项。
　　第三步：在跟踪属性对话框中选择“事件选择”标签页，选择“Stored Procedures-RPC:Completed”、“Stored Procedures-SP:StmtCompleted”和“TSQL- BatchCompleted”事件类，如图11-28所示。

　　图11-28 设置事件类

　　第四步：设置阀值，单击【列筛选器】按钮，选择“Duration”选项，并设置其阀值大于50毫秒，如图11-29所示。

　　图11-29 设置阀值

　　第五步：单击【组织列】按钮，选择“Duration”选项，单击【向上】按钮，将其置顶，即在SQL Profiler显示界面上第一列显示为“Duration”的值，如图11-30所示。

　　图11-30 Duration列置顶

　　1) 跟踪

　　实例：首先创建一个表，代码如下：

　　CREATE TABLE test
　　(
　　num varchar(255),
　　soc int,
　　id int
　　)

　　然后向该表中添加100万条记录，代码如下：

　　DECLARE @max AS INT, @rc AS INT;
　　SET @max = 1000000;
　　SET @rc = 1;
　　WHILE @rc <= @max
　　BEGIN
　　INSERT INTO TEST values('2012001',90,@rc);
　　SET @rc = @rc + 1;
　　END

　　接着启动跟踪程序，再打开一个查询窗口，执行一个查询语句，代码如下：

　　select * from test

　　SQL Profiler跟踪结果如图11-31所示。

　　图11-31 SQL Profiler跟踪结果

　　“Duration”列显示查询的时间，依次从小到大升序排序，一般首先分析时间长的查询语句。

　　1) 分析与调校

　　一般分析最后一行的数据，因为最后一行的数据是模拟脚本查询的语句，因些在该实例中先分析倒数第二行的数据，以分析倒数第二行数据为例，分析的工具一般使用SQL Server 2005 Database Tuning Advisor(DTA)。

　　在SQL Server Management Studio中新建一个查询窗口，输入如下查询语句：

　　select * from test

　　然后单击右键，选择“在数据库引擎优化顾问中分析查询”，如图11-32所示。

　　图11-32 执行优化分析查询

　　之后弹出“Database Engine Tuning Advisor”对话框，如图11-33所示。

　　图11-33 Database Engine Tuning Advisor分析

　　单击“优化选项”标签页，可以对优化项进行详细的设置，之后单击【开始分析】按钮，分析结束后显示分析结果，分析可以得知，DTA建议为test表创建一个索引。

　　注意：如果调用DTA来分析查询，需要使用SQL Server身份认证在SQL Server Management Studio中连接SQL Server，这样在DTA管理界面单击“开始分析”之前必须重新连接到一个数据库实例，并选择需要调校的数据库，如果使用Windows身份验证，那么在分析时就会报错，因为它不会要求重新连接到一个数据库实例。

　　七、Profile Trace与System Monitor关联

　　在SQL Server 2005之前，并没有可用的工具将SQL Trace事件数据与Windows System Performance(Perfmon)计数器数据相关联，这样有一个弊端，如果发现查询时间过长，需要分析当前系统资源使用的情况，那么就无法进行分析。在SQL Server 2005版本增加了这个新特征，在SQL Server Profiler可以将Microsoft Windows系统监视器计数器与SQL Server事件或SQL Server 2005 Analysis Services (SSAS)事件关联，Windows系统监视器将指定计数器的系统活动记录在性能日志中。

　　注意：在收集数据时，必须要求SQL Profiler与Windows计数器收集数据的时间戳是相同的，因为SQL Profiler是通过时间戳来同步数据的，如果时间戳不同，那么在导入性能数据时，会弹出错误的提示信息。

　　将跟踪与性能日志数据关联的步骤如下：

　　1) 在SQL Server Profiler中，打开保存的跟踪文件或跟踪表，不能关联仍在收集事件数据的运行中的跟踪，为实现与系统监视器数据的准确关联，跟踪必须同时包含StartTime和EndTime数据列。进入SQL Server Profiler主界面，单击【文件】菜单，在弹出的下拉菜单中选择【打开】菜单项，在弹出的级联菜单中选择【跟踪文件】菜单项，如图11-34所示。