SQL Profiler监控查询

作者：川石学院　日期：2021-06-04 18:01:42　点击数：

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

SQL Profiler监控查询(图1)

　　一、SQL Trace相关术语

　　在设置一个跟踪之前，需要理解SQL Profiler和SQL Trace中常用的术语，以下是常用的术语：

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

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

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

SQL Profiler监控查询(图2)

　　图11-10 事件类别

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

SQL Profiler监控查询(图3)

　　图11-11 事件类

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

SQL Profiler监控查询(图4)

　　图11-12 数据列

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

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

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

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

SQL Profiler监控查询(图5)

　　图11-13 筛选器

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

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

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

SQL Profiler监控查询(图6)

SQL Profiler监控查询(图7)

　　二、 SQL Trace选项

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

　　1) SQL Trace输出数据选项

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

SQL Profiler监控查询(图8)

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

SQL Profiler监控查询(图9)

表11-8 常见配置利弊

　　3) 收集时间范围选项

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

SQL Profiler监控查询(图10)