服务器端跟踪和收集

服务器端跟踪和收集

 

SQL Server性能分析器只是少量系统存储过程中相当轻量的封装，这些系统存储过程显示了SQL跟踪的真正功能。

 

下列系统存储过程用来定义和管理跟踪：

Sp_trace_create 用来定义一个跟踪，并和接下来将要讲的其他选项一样指定一个输出文件位置。这个存储过程以整型跟踪ID的形式返回一个所创建跟踪的句柄。

Sp_trace_setevent 的作用是基于跟踪ID将事件或列组合添加至跟踪，如果需要，也可以将这些组合从已定义的跟踪里移除。

Sp_trace_setfilter 用来定义基于跟踪列的事件过滤器。

Sp_trace_setstatus 被调用以启动、停止一个跟踪，也可以在完成这个跟踪后删除其定义。跟踪在其生存期内可以被启动或停止若干次。

 

编辑服务器端跟踪

 

首先，打开SQL Server性能分析器，启动默认模板的一个新跟踪，并取消除SQL:BatchCompleted之外的所有事件选择。

 

 

下一步，移除ApplicationName列上的默认过滤器（设置为不收集SQL Server性能分析器事件），在Duration上添加一个大于或等于200毫秒的过滤器。

 

 

一旦完成以上工作，就点击“运行”启动跟踪，然后点击“停止”按钮。由于SQL Server性能分析器用户界面需要工作流程，因此，必须启动一个跟踪才能编辑它。选择文件菜单上的“导出”，然后依次选择“编辑跟踪定义”。生成以下脚本。

 

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 2014/12/04 16:21:25 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @bigintfilter = 200000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

 

这个脚本是一个十分简单且完整定义的跟踪，它使用文件提供者。在这里要修改两个占位符的值，但是大部分都还是原有的功能。考虑到直接同SQL跟踪存储过程工作的复杂性，通常定义一个使用SQL Server性能分析器用户界面的跟踪，然后编辑它并开始工作。采用这种方法可以使操作的简易型同使用文件提供者的服务器端跟踪的高效性像结合。

 

查询服务器端跟踪元数据

 

在适当修改文件名称占位符并在服务器上运行测试脚本后，可得到跟踪ID的值：2。

 

 

如下列查询所示，通过使用一个跟踪ID，可以从sys.traces目录视图中检索到关于跟踪的各种元数据：

 

 

该查询返回了跟踪状态，该状态要么为1（已启动）要么为0（已停止），去往跟踪文件的服务器路径（如果跟踪使用的是行集提供者，则值为NULL），最大文件容量（同样，如果使用行集提供者，则值为NULL），处理I/O所使用缓冲区的大小和数目的相关信息，捕捉到的事件数目，以及丢失事件的数目（在这种情况下，如果跟踪使用的是文件提供者，则值为NULL）。

 

除了sys.traces目录视图之外，SQL Server还提供了一些其他的视图和函数来帮助获得服务器上运行的跟踪相关信息。

 

Fn_trace_geteventinfo

该函数以表格形式返回跟踪中被选中事件和列的数值组合，以下Transact-SQL返回跟踪ID 2的数据：

SELECT * FROM fn_trace_geteventinfo(2);

 

 

Sys.trace_events 和 sys.trace_columns

跟踪事件和列的数值表示，单独使用并没有多大用处。为了能够适当地查询该数据，sys.trace_events和sys.trace_columns不仅分别包含了描述事件和列的文本，还包含了其他诸如列的数据类型及可过滤性等信息。将上面的查询和这些视图结合起来并取代fn_trace_geteventinfo函数，可以得到同样输出的一个更易读的版本：

 

SELECT
e.name AS Event_Name,
c.name AS Column_Name
FROM fn_trace_geteventinfo(2) ei
JOIN sys.trace_events e ON ei.eventid = e.trace_event_id
JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id;

 

 

Fn_trace_getfilterinfo

使用fn_trace_getfilterinfo函数可以跟踪所使用的过滤器。该函数返回正在被过滤的列的ID（可以加入到sys.trace_columns视图中以获得更多的信息）、逻辑运算符、比较运算符及过滤器的值。下面是该函数的一个使用实例：

 

SELECT
Columnid,
Logical_operator,
Comparison_operator,
Value
FROM fn_trace_getfilterinfo(2);

 

 

由服务器端跟踪检索数据

 

如果跟踪启动，显然下一步就应当实际读取收集的数据。这可以用fn_trace_gettable函数完成。这个函数有两个参数：从中读取数据的第二个文件的名称，以及可读取的滚动文件的最大数目（如果存在）。下面的Transact-SQL代码用来读取E:\SQL-DATA\BatchTrace.trc路径下的跟踪文件：

 

SELECT * FROM fn_trace_gettable('E:\SQL-DATA\BatchTrace.trc',1)

 

 

任何时候都可以读取一个跟踪文件，甚至当一个跟踪正往文件里写数据时也可以。注意，在大多数情况下这并不可取，因为它会增加磁盘竞争，从而也就降低将事件写入表中的速度并增加阻塞的可能性。然而，在收集数据并不频繁的情况下—例如当已过滤了一个不常调用的特定存储过程模式时，就可以方便用户查找当前已收集的数据。

 

由于fn_trace_gettable是一个表值函数，因此在Transact-SQL内部的用途无限。可以用来公式化表示查询，或者插入到一个表里以便创建索引。在后一种情况下，使用SELECT INTO可能会更合适一些，因为这样可以利用最小限度的日志：

 

SELECT *
INTO BatchTrace
SELECT * FROM fn_trace_gettable('E:\SQL-DATA\BatchTrace.trc',1);

 

如果数据被载入表里，就可以任意切割分析，以排查故障或解决问题。

 

停止和关闭跟踪

 

当初次创建一个跟踪时，它的状态值为0，即停止（这种情况下，或者说是尚未启动）。任何时候都可以用sp_trace_setstatus将一个跟踪带回到停止状态。可以使用下列Transact-SQL代码将跟踪ID 2设置为停止状态：

 

EXEC sp_trace_setstatus 2, 0;

 

除了跟踪不再收集数据这样一个明显的好处之外，还有另外一个原因：如果跟踪处于停止状态，用户就可以修改使用适当存储过程的事件或列选择及过滤器，而不用重新创建跟踪。如果只须做小的调整，这就很有用了。

 

如果要结束跟踪，并且接下来也不想再继续，就可以将它的状态设置为2，把跟踪定义从系统中全部移除：

 

EXEC sp_trace_setstatus 2, 2;

 

注意：在SQL Server服务重启的情况下，跟踪定义将会自动被移除。因此，如果要再次运行同一个跟踪，就要保存为一个性能分析器模板或保存用来启动它的脚本。