性能测试—SQL Profiler监控查询（上）

　　SQL Server Profiler是一个功能丰富的界面，用于创建和管理跟踪，并分析和重播跟踪结果。对SQL Server Profiler的使用取决于您出于何种目的监视SQL Server数据库引擎实例。例如，如果正处于生产周期的开发阶段，则会更关心如何尽可能地获取所有的性能测试详细信息，而不会过于关心跟踪多个事件会造成多大的开销。相反，如果正在监视生产服务器，则会希望跟踪更加集中，并尽可能占用较少的时间，以便尽可能地减轻服务器的跟踪负载。本小节中将会介绍如何及何时使用SQL Profiler收集并分析查询性能数据。

　　一、SQL Trace相关术语

　　在设置一个跟踪之前，需要理解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选项

　　使用SQL Server Profiler跟踪数据库事件时有一些设置选项，一些选项对特定用例场景是有益的，并且在实际执行环境中对于使用跟踪将消耗多少系统资源也是很敏感的。本章节主要介绍SQL Trace输出数据选项、File选项影响、收集时间范围选项和Duration列配置。

　　1) SQL Trace输出数据选项

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

　　表11-7 SQL Trace输出数据选项

• 　　选项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跟踪以及如何保存结果由以下两个重要因素决定：

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

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

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

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

　　图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数据

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

　　1) SQL Server Management Studio

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

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

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

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

　　图11-21 图形查询计划

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

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

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

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

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

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

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

　　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 Profiler监控查询（上）”的内容，大家觉得有用的话，记得每天来这里和小编一起学习哦。