SQL SERVER 2005性能之跟踪

hq8501

　　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的图形界面的性能开销更小。