性能测试—SQL Profiler监控查询

SQL Server Profiler是一个功能丰富的界面，用于创建和管理跟踪，并分析和重播跟踪结果。对SQL Server Profiler的使用取决于您出于何种目的监视SQL Server数据库引擎实例。

• 例如：如果正处于生产周期的开发阶段，则会更关心如何尽可能地获取所有的性能测试详细信息，而不会过于关心跟踪多个事件会造成多大的开销。相反，如果正在监视生产服务器，则会希望跟踪更加集中，并尽可能占用较少的时间，以便尽可能地减轻服务器的跟踪负载。本小节中将会介绍如何及何时使用SQL Profiler收集并分析查询性能数据。
  

SQL Trace相关术语

• 服务器实例：是指Profiler跟踪时需要连接到的SQL Server的一个实例，需要指定实例名称，如果SQL Server运行在windows，那么需要知道其虚拟名称。

• SQL跟踪事件：是指在整个性能监控过程中需要跟踪哪些方面的问题，该项设置直接影响到监控过程中将获得的数据，一个跟踪事件代表SQL Server产生的不同的活动，将问题症状与可跟踪的事件进行匹配是一项比较难的工作。

• 事件类别：在SQL Profiler中可跟踪事件有很多，事件类别是对事件类进行分组，相同类的事件归纳到一个事件类别中，例如所以关于lock的事件类都分组到Locks事件类别中。SQL Profiler中的事件类别如图11-10所示。

图11-10 事件类别

• 事件类：是指能够被SQL Server实例跟踪的特定类型事件，事件类包含描述一个事件的所有数据，在SQL Server中，大约有200个事件类，而一个事件类别中又包含多个事件类，每个事件类都有自身的数据列集(如TextData、ApplicationName、NTUserName等)，如图11-11所示。

图11-11 事件类

• 数据列：数据列与一个跟踪中所捕获的事件类相关联，它是每个事件类中的属性，一个事件类可能有多个不同的相关联的数据列。在SQL Server Profilere有一种内部逻辑处理数据列与事件类之间的依赖关系，这样每个事件类只显示与其相关联的一些数据列，而并不是每个事件类都显示出所有的数据列。如图11-12所示。

图11-12 数据列

• 跟踪：是指由SQL Server的一个实例返回的可跟踪的事件类及数据的集合，它是一个动作，是对一个SQL Server中的一个实例进行监控，并且在监控过程中收集事件类的相关数据，跟踪会根据选中的事件类、数据列以及筛选器来收集监控过程中的数据。

• 跟踪文件：是指保存跟踪结果的文件，在设置跟踪过程可以指定跟踪文件的位置。

• 跟踪表：是指将保存跟踪结果保存到一个数据库的表中，在设置跟踪过程中可以将跟踪数据保存到指定的数据库表中。

• 筛选器：在创建一个跟踪或模板时，如果不对列进行筛选，那么在跟踪过程中SQL Profiler会收集事件类中所有列的数据值，为了防止跟踪变得过于庞大，通过设置筛选器可以对事件数据进行筛选，确保只收集其中一部分数据。例如：对SPID进行筛选，可以指定跟踪的SPID，这样跟踪过程中只会收集所指定的SPID的相关数据。筛选器设置如图11-13所示。

图11-13 筛选器

• 模板：模板用于定义一个跟踪的默认配置，SQL Server Profiler可以自定义模板也可以使用自建的模板，自建模板包括：Standard、TSQL、TSQL_Duration、TSQL_Grouped、TSQL_Replay、TSQL_SPs和Tuning。在测试过程中可以使用自建模板也可以自己配置模板，模板中包括监控的事件类，如果自定义模板，需要确定监控的事件、数据列以及筛选器。

常见的跟踪场景以及建议使用的事件类，见表11-6。

表11-6 跟踪场景及建议事件类

SQL Trace选项

1) SQL Trace输出数据选项

• 关于跟踪输出数据有5种可选方式，输出的对象通常有三种：Profiler屏幕、跟踪输出文件和跟踪数据库表，见表11-7所示。

• 选项1：User SQL Profiler to trace without saving data(使用SQL Profile进行跟踪，但不保存结果)：在执行跟踪过程中，SQL Server Profiler将从本地的或远程的SQL Server中收集事件，产生的跟踪数据由配置的事件类决定，并且立即显示在SQL Profiler屏幕上，而并不保存跟踪数据。一般情况下跟踪大数据库事务实际执行过程时，不使用这种设置，当服务器处于资源压力下时，一般使用服务器端跟踪比SQL Profiler更合适。

• 选项2：User SQL Profiler to trace and save data to a file system(使用SQL Profile进行跟踪，并将跟踪数据保存到文件系统中)：该选项设置有着选项1一样的优缺点，此外，使用该选项设置会将跟踪数据保存到Windows文件系统中，并且生成一个后缀名为.trc的文件，但同时也这增加了写磁盘的开销。如果选择将跟踪数据保存到文件，则必须指定跟踪文件的最大大小，默认值为5MB，最大大小仅受保存该文件的文件系统(NTFS、FAT)的限制。

当跟踪的数据大于5MB时，可以选中“启动文件滚动更新”选项，选择此选项允许在达到最大文件大小时创建其它文件来接受跟踪数据，每个新文件名都由原始.trc 文件名按顺序编号而成。例如，当NewTrace.trc达到最大文件大小时，将关闭该文件，并打开一个新文件NewTrace_1.trc，在新文件达到最大文件大小时将打开NewTrace_2.trc，依此类推。默认情况下，在将跟踪保存到文件时将启用文件滚动更新。

• 选项3：User SQL Profiler to trace and save data to a database table(使用SQL Profile进行跟踪，并将跟踪数据保存到数据库表中)：该选项设置有着选项1一样的优缺点，此外，它将跟踪数据保存到数据库表中，这个进程直接增加了被监视SQL Server的开销。当选中该选项时，同时需要设置数据库表中所允许保存数据的最大行数，缺省值为1000行。

• 选项4：User SQL Profiler to trace and save data to both the file system and the database table(使用SQL Profile进行跟踪，并将跟踪数据保存文件系统和数据库表中)：该选项将跟踪数据同时保存到文件系统和数据库表中，这是一种冗余的做法，在一些特定的条件下使用，一般不使用这种模式。

• 选项5：Use server-side trace and save data to a file system(服务器端跟踪，并将跟踪数据保存到文件系统中)：一般情况下不对事件类进行实时监控，那么可以使用该方式，同时该方式提供了为数据库性能调校及故障诊断而收集事件效率的最佳方式，在跟踪过程中服务器端使用缓存I/O将跟踪的数据写入到文件系统中。

2) File选项影响

• SQL Profiler可以通过本地或远程连接到被监视的服务器上，那么如果启动SQL Profiler Trace跟踪以及如何保存结果由以下两个重要因素决定：

1. SQL Profiler对被监控SQL Server的开销影响;

2. 遗漏跟踪事件对跟踪数据收集的影响;

SQL Profiler是一个调用T-SQL函数集以及系统过程的GUI前端工具，在启动SQL Profiler时，Windows系统会启动一个名为PROFILER90.exe的进程，而执行PROFILER90.exe进程也是需要开销的，这个通过系统自带的性能监视器可以测量，如果为了将监视SQL Server上的Profiler跟踪的开销最小化，那么可以从远程服务器或工作站上启动Profiler。

为了确保在跟踪过程中所收集的数据不被遗漏，可以选中跟踪属性常规设置中的“服务器处理跟踪数据”选项，该选项要求在被监视的SQL Server上创建一个系统文件来保存跟踪数据的结果，即使在服务器处于压力条件下，这种方式也能保证不会遗漏任何事件，但这样可能使服务器的性能受到影响。

常见配置利弊见表11-8所示。

表11-8 常见配置利弊

3) 收集时间范围选项

• 由于收集事件数据会给系统增加开销，所以明确指定跟踪的时间范围可以将系统开销减少到最小，如果在故障诊断过程中，已经明确知道性能问题所发生的时间范围，那么可以定位到该特定的时间范围，如果不明确性能问题所发生的时间范围，那么尝试较短的跟踪时间间隔。如果收集数据是出于基线目的，则需要考虑目的和频度，如表11-9是具体的实例。

表11-9 数据收集时间范围

4) Duration列配置

• 在SQL Profiler 2005之前的版本中，无论是在Profiler屏幕中显示的持续时间还是保存在输出文件或保存在数据库中的持续时间，Duration列的量度都是以毫秒为单位，如图11-14所示。

图11-14 Duration列度量

但是在SQL Profiler 2005之后的版本，如果一个跟踪是将事件数据保存到文件或数据库，那么这个列的单位默认为微秒，关于Duration列的单位可以在【常规选项】对话框中进行设置，打开菜单【工具】->【选项】，弹出常规选项对话框，如图11-15所示。

图11-15 常规选项对话框

选中“在“持续时间”列中以微秒为单位显示值(仅限SQL Server 2005)”复选框，那么SQL Profiler界面、文件和数据表中Duration列的单位都为微秒。

捕获阻塞事件

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

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所示。

图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所示。

图11-19 跟踪事件报告

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

捕获Showplan XML数据

1) SQL Server Management Studio

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

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

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

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

图11-21 图形查询计划

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

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

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

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

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

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

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

2) 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，它包含以下事件的设置：

Showplan XML：XML Showplan输出可作为一个单独的文件进行保存，如图11-22所示;
Stored Procedures-RPC:Completed：指示一个远程过程调用已经完成;
TSQL-SQL:BatchCompleted：指示Transact-SQL批作业已经完成;

图11-22 提取事件设置

在提取事件设置对话框中有提取XML计划和死锁XML两种，保存文件的后缀名为.sqlplan，而关于生成的提取文件又有两种设置：

1. 单个文件中的所有XML显示计划批(P)：表示所有的查询执行计划结果只保存在一个文件中;

2. 不同文件中的每个XML显示计划批(E)：表示不同的查询执行计划使用不同的文件来保存;

Profiler跟踪设置完成后，执行一个跟踪，Profiler捕获到的查询计划结果如图11-23所示。

图11-23 Profiler捕获到的查询计划信息

捕获死锁图

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列，通常使用的事件类如下：

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

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

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

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

2) 设置模板

• 第一步：启动SQL Profiler，单击【文件】菜单，在下拉菜单中选择【新建跟踪】选项，弹出跟踪属性对话框。

• 第二步：在跟踪属性对话框中选择“常规”标签页，单击【使用模板】下拉框，选择“Standard(默认值)”选项。

• 第三步：在跟踪属性对话框中选择“事件选择”标签页，选择“Stored Procedures-RPC:Completed”、“Stored Procedures-SP:StmtCompleted”和“TSQL- BatchCompleted”事件类，如图11-28所示。

• 第四步：设置阀值，单击【列筛选器】按钮，选择“Duration”选项，并设置其阀值大于50毫秒，如图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所示。

图11-34 打开跟踪文件

• 2) 进入SQL Server Profiler主界面，单击【文件】菜单，在弹出的下拉菜单中选择【导入性能数据】菜单项，导入一个Windows计数器文件，如图11-35所示。

• 3) 在“性能计数器限制”对话框中，选中与要显示在跟踪旁边的系统监视器对象和计数器相对应的复选框，单击【确定】按钮，如图11-36所示。

图11-36 选择关联计数器

• 4) 关联后，结果如图11-37所示。

图11-37 关联结果

• 5) 在跟踪事件窗口中选择一个事件，或者使用方向键在跟踪事件窗口的几个相邻行中导航。“系统监视器数据”窗口中的红色竖线指明与所选跟踪事件关联的性能日志数据。

• 6) 在系统监视器图形中单击一个相关点，选中时间最接近的相应跟踪行，若要扩大时间范围，请在系统监视器图形中按住并拖动鼠标指针。

本章节主要介绍了关于“SQL Profiler监控查询”的内容，大家觉得有用的话，记得每天来这里和小编一起学习哦。

温馨提示：想要自学转行的伙伴可以扫下方二维码进行在线重复学习!

添加老师微信：13691729932 可以获取全套软件测试自学资料!

给大家推荐一个软件测试自学群，识别下方二维码，免费领取学习课件、视频哦。