SQL2005 性能之跟踪

SQL Server Profiler这个工具大家应该都知道，就是SQL Server的事件跟踪器，通过这个工具可以收集你想要的SQL内部运行的事件。
但当数据库在运营中时，使用Profiler进行跟踪是要付出一定的性能代价的，因此今天给大家介绍下使用T-SQL语句进行事件的跟踪。
SQL SERVER 2005为我们提供了事件跟踪的几个相关的存储过程：
1.sp_trace_create:创建一个跟踪
2.sp_trace_setevent:设置需跟踪的事件
3.sp_trace_setstatus:设置跟踪的状态
4.sp_trace_setfilter：为跟踪设置过滤器
这四个存储过程的说明请大家参照MSDN，下面我们通过Profiler来设置一个跟踪，然后生成跟踪的T-SQL脚本。来看下这个4个存储过程的使用方法。
1.打开Profiler，然后选择几个常见的事件后，按下图生成T-SQL脚本

2.生成的T-SQL如下：
 
/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 2011/08/16  09:40:55         */
/****************************************************/
 
 
-- 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
 
/*  
 *  创建跟踪，此存储过程返回新建跟踪的ID，同时此存储过程还要设置个trc文件的保存的路径。
 *  比如放在:C:\trace中，文件名为：sp_trace.trc，那需要将InsertFileNameHere替换为:C:\trace\sp_trace
*/
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
 
/*
 *  为新建的跟踪设置需要跟踪的事件.各参数的意义如下：
 *  1.@TraceID：需要操作的跟踪的ID
 *  2.75:需要跟踪的事件
 *  3.7：需要跟踪的列
 *  4.@on:该事件的开启状态
*/
exec sp_trace_setevent @TraceID, 75, 7, @on --
exec sp_trace_setevent @TraceID, 75, 4, @on
exec sp_trace_setevent @TraceID, 75, 8, @on
exec sp_trace_setevent @TraceID, 75, 12, @on
exec sp_trace_setevent @TraceID, 75, 60, @on
exec sp_trace_setevent @TraceID, 75, 64, @on
exec sp_trace_setevent @TraceID, 75, 9, @on
exec sp_trace_setevent @TraceID, 75, 41, @on
......
exec sp_trace_setevent @TraceID, 166, 35, @on
exec sp_trace_setevent @TraceID, 166, 51, @on
exec sp_trace_setevent @TraceID, 166, 4, @on
exec sp_trace_setevent @TraceID, 166, 12, @on
exec sp_trace_setevent @TraceID, 166, 60, @on
exec sp_trace_setevent @TraceID, 166, 5, @on
exec sp_trace_setevent @TraceID, 166, 21, @on
exec sp_trace_setevent @TraceID, 166, 29, @on
exec sp_trace_setevent @TraceID, 166, 61, @on
 
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 3c203bc8-5030-405d-bf49-7acf75f762f6'   --设置过滤器
-- 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
其中39-56行都在为新建的跟踪设置需要跟踪的事件，可以通过MSDN来查找你想要跟踪的事件ID后，在此处进行添加。
65行启动了新建的跟踪，跟踪的文件将保存在C:\trace\sp_trace.trc文件中，如果想要停止跟踪，请执行如下语句：
exec sp_trace_setStatus @TraceID,0
比较下，启动的时候参数为1，停止的时候参数为0。
另外还有个参数2表示：关闭指定的跟踪并从服务器中删除其定义。
通过上面的介绍，我们可以使用T-SQL语句进行SQL的跟踪，该跟踪方式将比使用Profiler的图形界面的性能开销更小。