SQL Server中追踪器Trace的介绍和简单使用

一.What is Trace？

对于SQL Profiler这个工具相信大家都不是很陌生，没用过的朋友可以在SQL Server Management Studio>工具>SQL Server Profiler处使用。这个工具是用来监控SQL，存储过程的执行，用户登录等等信息。但这个工具只是一个GUI，他的本质就是Trace。下面是Trace的架构：

数据库引擎会产生一系列事件，然后各个trace可以去订阅自己感兴趣的事件，一旦数据库产生了相关事件就会发给订阅该事件的trace,各个trace通过自己的过滤器对该事件的信息过滤（例如：捕获执行时间超过1秒的SQL语句）,然后放到一个缓冲队列里，最终写入文件或者是一些客户端应用。

简单介绍完理论，下面就开始实践：

How to Create a Trace?

要创建一个追踪器，总共分三步：

1.执行存储过程sp_trace_create创建一个追踪器

2.执行存储过程sp_trace_setevent添加自己想订阅的事件以及最终结果集的列名

3.执行存储过程sp_trace_setfilter设置过滤器来对过滤产生数据

下面是一个创建一个追踪器的SQL脚本

 DECLARE @return_code INT;

 DECLARE @TraceID INT;

 DECLARE @maxfilesize BIGINT;

 SET @maxfilesize = 5;

 --step 1: create a new empty trace definition

 EXEC sp_trace_create

                 @traceid OUTPUT

                , @options = 2

                , @tracefile = N'C:\TraceFiles\LongRunningQueries'

                , @maxfilesize = @maxfilesize

     , @stoptime =NULL

     , @filecount = 2;

 -- step 2: add the events and columns

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 10 -- RPC:Completed

                , @columnid = 1 -- TextData

                , @on = 1;--include this column in trace

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 10 -- RPC:Completed

                , @columnid = 13 --Duration

                , @on = 1;--include this column in trace

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 10 -- RPC:Completed

                , @columnid = 15 --EndTime

                , @on = 1;--include this column in trace

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 12 -- SQL:BatchCompleted

                , @columnid = 1 -- TextData

                , @on = 1;--include this column in trace

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 12 -- SQL:BatchCompleted

                , @columnid = 13 --Duration

                , @on = 1;--include this column in trace

 EXEC sp_trace_setevent

                 @traceid = @TraceID

                , @eventid = 12 -- SQL:BatchCompleted

                , @columnid = 15 --EndTime

                , @on = 1;--include this column in trace

 -- step 3: add duration filter

 DECLARE @DurationFilter BIGINT;

 SET @DurationFilter = 10000000; --duration in microseconds

 EXEC sp_trace_setfilter

                 @traceid = @TraceID

                , @columnid = 13

                , @logical_operator = 0 --AND

                , @comparison_operator = 4 -- greater than or equal to

                , @value = @DurationFilter; --filter value

 SELECT @TraceID AS TraceID;

对于第九行中的C:\TraceFiles\LongRunningQueries，请确保TraceFiles文件夹存在，而LongRunningQueries是文件名，创建后会自动加上.trc后缀。

对于过滤器中具体的事件以及列名，大家可以参考：http://technet.microsoft.com/zh-cn/library/ms186265(v=sql.105).aspx

对于像我一样的一些初学者，写上面的脚本可能比较吃力，那么我们可以通过SQL Server Profiler配置各种需求，然后导出脚本：

然后把路径等一系列其他参数设置下即可。

三.How to Operate a Trace?

上面我们已经把创建了一个追踪器，但这个追踪器目前并未开始运行，我们可以通过下面的脚本来查看trace的状态

select * from sys.traces

执行之后你会发现有2个trace记录，第一个是SQL Server默认的trace,它提供极其有限的功能，第二个就是我们刚刚创建的trace

status就是指追踪器的状态

@status	Action
0	Stops the trace
1	Starts the trace
2	Closes the trace and deletes its definition

操作status的脚本:

-- stop  the trace

 DECLARE @TraceID int ;

 SET @TraceID = 2 ; -- specify value from sp_trace_create

 EXEC sp_trace_setstatus

    @traceid = @TraceID

    ,@status = 0 ;-- stop trace

 -- delete the trace

 EXEC sp_trace_setstatus

    @traceid = @TraceID

  ,@status = 2 ;-- delete trace

 -- start the trace

 EXEC sp_trace_setstatus

    @traceid = @TraceID

  ,@status = 1 ;-- start trace

四.How to Viewing Trace Data?

前面已经说过，追踪器最终把追踪到的信息写到了一个文件里，也就是我们创建时指定的路径。通过一个系统函数就可以查看分析这些数据了：

SELECT *

FROM fn_trace_gettable(N'C:\TraceFiles\LongRunningQueries.trc',DEFAULT);

五.Summary

SQL Trace对象提供服务器端的追踪的技术，包括一些存储过程，数据库,视图来创建trace，通过使用T-SQL和trace暴露的元数据来管理trace数据，这些都是SQL Profiler所无法提供的

巴特西