与 SQL Server Profiler 有关的几个存储过程和函数

--收藏，太有用了

这个月新系统正式上线，半个月来出现很多问题，麻烦！真是麻烦！麻烦不断啊！最要命的是公司的DBA因为需要参与新项目的设计居然把所有问题交我解决，自己一走了知。我本就是一懒人，什么事情都要重复做可不行，是什么原因出现的错误我分析不出来，可是我能把所有的语句都跟踪记录下来。 SQL Server Profiler 没错！就是它了，这是这半个月来我每天都要手点一下选择一个模板出来运行，这也够无聊的了，交给 SQLAgent 出做不是很好吗，为什么一定要我去每天点呢？

可惜的很 SQLAgent 里面没有那个选项能做跟踪的，想想实在没有办法看了一天的联机帮助，参考了几个例子总算是写了个存储过程，勉强是做出来了，看来下面的半个月里我不用每天都打开 SQL Server Profiler 点一点鼠标做无聊的事情了。高兴！

首先还是先介绍一下系统里面与 SQL Server Profiler 有关的几个存储过程和函数，自己写的例子在下面。(查看示例代码)

下面的说明都是从联机帮助上面Copy下来的，个人整理了一下，然后加了一点点自己的理解。

sp_trace_create

 创建跟踪定义。新的跟踪将处于停止状态。

语法：

sp_trace_create [ @traceid = ] trace_id OUTPUT           , [ @options = ] option_value           , [ @tracefile = ] 'trace_file'      [ , [ @maxfilesize = ] max_file_size ]      [ , [ @stoptime = ] 'stop_time' ]      [ , [ @filecount = ] 'max_rollover_files' ]

参数：

@traceid
跟踪的编号。数据类型为 int，默认为 NULL。用户使用 trace_id 值标识、修改和控制此存储过程定义的跟踪。

@options
指定为跟踪设置的选项。option_value 的数据类型为 int，无默认设置。

选项名	值	说明
TRACE_FILE_ROLLOVER	2	指定当达到 max_file_size ，将关闭当前跟踪文件并创建新文件。常用的使用这个值。
SHUTDOWN_ON_ERROR	4	指定无论任何原因，如果不能将跟踪写入文件，则 SQL Server 将关闭。
TRACE_PRODUCE_BLACKBOX	6	同时启用上面两个选项，2和4
TRACE_PRODUCE_BLACKBOX	8	指定服务器产生的最后 5MB 跟踪信息记录将由服务器保存。

@tracefile
指定跟踪将写入的位置和文件名。trace_file 的数据类型为 nvarchar(245)，没有默认设置。trace_file 可以是本地目录，也可以是到共享或路径。

@maxfilesize
指定跟踪文件可以增长到的最大文件大小 (MB)。max_file_size 的数据类型为 bigint，默认设置为 5。

@stoptime 
指定停止跟踪的日期和时间，stop_time 的数据类型为 datetime，默认值为 NULL。如果为 NULL，该跟踪将一直运行，直到它被手动停止或服务器关闭。

@filecount
指定使用同一基准文件名维护的最大跟踪文件数。max_rollover_files 的数据类型为 int，其值大于 1。这个选项通常不用设置，当然要保证有足够的磁盘空间，否则磁盘空间填满的时候跟踪会自动停止。

返回值：

返回代码	说明
0	没有错误。
1	未知错误
10	无效选项。指定的选项不兼容时返回此代码。
12	文件未创建。
13	内存不足。在没有足够内存执行指定的操作时返回此代码。
14	无效停止时间。在指定的停止时间已发生时返回此代码。
15	参数无效。在用户已提供不兼容的参数时返回此代码。

sp_trace_setevent

在跟踪中添加或删除事件或事件列。只能对已停止的现有跟踪（status 为 0）执行 sp_trace_setevent。如果对不存在的或其 status 值不为 0 的跟踪执行此存储过程，则将返回错误。

语法：

sp_trace_setevent [ @traceid = ] trace_id           , [ @eventid = ] event_id           , [ @columnid = ] column_id           , [ @on = ] on

参数：

@traceid
要修改的跟踪的 ID。

@eventid
要打开的事件的 ID。event_id 的数据类型为 int，无默认值。可以跟踪的事件有几百个，这里只列出较常用的几个，其他的MSDN上有完整的介绍。

事件号	事件名称	说明
10	RPC:Completed	在完成了远程过程调用 (RPC) 时发生。
12	SQL:BatchCompleted	在完成了 Transact-SQL 批处理时发生。
14	Audit Login	在用户成功登录到 Microsoft SQL Server 时发生。
17	ExistingConnection	在检测所有用户在启动跟踪前连接到 SQL Server 的活动。
25	Lock:Deadlocktd>	指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。
59	Lock:Deadlock Chain	为导致死锁的每个事件而生成。

@columnid
要为该事件添加的列的 ID。column_id 的数据类型为 int，无默认值。这里也只列常用的。

列号	列名	说明
1	TextData	与跟踪内捕获的事件类相关的文本值。
8	HostName	发起请求的客户端计算机的名称。
10	ApplicationName	创建与 SQL Server 实例的连接的客户端应用程序的名称。此列由应用程序传递的值填充，而不是由所显示的程序名填充。
11	LoginName	客户端的 SQL Server 登录名。
12	SPID	SQL Server 分配给与客户端关联的进程的服务器进程 ID。
13	Duration	事件所花费的实耗时间（毫秒）。Hash Warning 事件不填充该数据列。
14	StartTime	事件开始的时间（如果可用）。
27	EventClass	被记录的事件类的类型。
35	DatabaseName	USE database 语句中指定的数据库名称。

@on
指定将事件设置为 ON (1) 还是 OFF (0)。on 的数据类型为 bit，无默认值。

sp_trace_setfilter

将筛选应用于跟踪。只能对已停止的现有跟踪（status 的值为 0）执行 sp_trace_setfilter。如果对不存在的跟踪或其 status 值不为 0 的跟踪执行此存储过程，则 SQL Server 将返回错误。

语法：

sp_trace_setfilter [ @traceid = ] trace_id           , [ @columnid = ] column_id           , [ @logical_operator = ] logical_operator           , [ @comparison_operator = ] comparison_operator           , [ @value = ] value

@traceid
要为其设置筛选器的跟踪的 ID。

@columnid
应用筛选器的列的 ID。

@logical_operator
指定将应用 AND (0) 运算符还是应用 OR (1) 运算符。logical_operator 的数据类型为 int，无默认值。

@comparison_operator
指定要执行的比较的类型。comparison_operator 的数据类型为 int，无默认值。

值	比较运算符
0	=（等于）
1	<>（不等于）
2	>（大于）
3	<（小于）
4	>=（大于或等于）
5	<=（小于或等于）
6	LIKE
7	NOT LIKE

@value
指定要在其上进行筛选的值。

sp_trace_setstatus

修改指定跟踪的当前状态。

语法：

sp_trace_setstatus [ @traceid = ] trace_id           , [ @status = ] status

@traceid
trace_id要修改的跟踪的 ID。

@status
指定要在跟踪上实现的操作。status 的数据类型为 int，无默认值。

状态	说明
0	停止指定的跟踪。
1	启动指定的跟踪。
2	关闭指定的跟踪并从服务器中删除其定义。

注意：在关闭跟踪前首先必须先停止它。在查看跟踪前首先必须先停止并关闭它。如果要删除则将状态指定为2。

sp_trace_generateevent

创建用户定义事件。

fn_trace_gettable

以表格格式返回一或多个跟踪文件的内容。

语法：

fn_trace_gettable ( filename , number_files )

@filename
指定要读取的初始跟踪文件。filename 的数据类型为 nvarchar(256)，无默认值。

@number_files
指指定要读取的滚动更新文件数。此数包括 filename 中指定的初始文件。number_files 的数据类型为 int。如果 number_files 指定为 “default”，fn_trace_gettable 将读取所有的滚动更新文件，直到到达跟踪结尾为止。

fn_trace_getinfo

返回有关指定跟踪或全部现有跟踪的信息。

语法：

fn_trace_getinfo ( trace_id )

@trace_id
跟踪的 ID。trace_id 的数据类型为 int。当传递特定跟踪的 ID 时，fn_trace_getinfo 将返回有关该跟踪的信息。传递 0 或字符串 'default' 时，此函数将返回有关所有活动跟踪的信息。

fn_trace_geteventinfo

返回有关所跟踪的事件的信息。

fn_trace_getfilterinfo

返回有关应用于指定跟踪的筛选器的信息。

以上基本是从联机帮助上Copy的，都是理论，下面是我自己写的应用。

示例：创建一个新跟踪，这个是用代码建的，效果和在 SQL Server Profiler 中用鼠标点出来的一样。

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
--建立人：  高升
--建立日期：2007/08/12
--修改日期：
--功能目的：创建一个新跟踪，由 SQL Server Agent 每天调用执行一次
--注意：    因为文件名的关系，一天只能执行一次，只跟踪了8个小时
CREATE PROCEDURE [dbo].[BeginNewTrack]
  AS
DECLARE @TraceID        int             --跟踪编号
DECLARE @fileAddress    nvarchar(245)    --跟踪文件的地址加文件名
DECLARE @maxFileSize    bigint          --跟踪文件的最大文件大小
DECLARE @endDateTime    datetime        --跟踪结束的时间
DECLARE @fileCount      int             --跟踪文件的最大个数
DECLARE @result         int             --建立新跟踪的返回值

--跟踪文件保存在C盘我的文档下面，文件名加上当天的年月日
SET @fileAddress = N'C:/Documents and Settings/Administrator/My Documents/TraceSQL' + Convert(char(8),GetDate(),112)
SET @maxFileSize = 200  --每个跟踪文件最大200M
SET @endDateTime = GetDate() + 0.041666 * 8 --跟踪8小时后结束
SET @fileCount = 5  --此处未使用此参数，不限定文件个数

--定义一个跟踪
EXECUTE @result = sp_trace_create @TraceID output, 2, @fileAddress, @maxFileSize, @endDateTime

IF (@result = 0)
BEGIN
  DECLARE @on   bit -- 1 sp_trace_setevent 必要参数
  SET @on = 1
  --设定需要跟踪的事件，跟踪远程调用存储过程
  EXECUTE sp_trace_setevent @TraceID, 10,  1, @on   --TextData
  EXECUTE sp_trace_setevent @TraceID, 10,  8, @on   --HostName
  EXECUTE sp_trace_setevent @TraceID, 10, 10, @on   --ApplicationName
  EXECUTE sp_trace_setevent @TraceID, 10, 11, @on   --LoginName
  EXECUTE sp_trace_setevent @TraceID, 10, 13, @on   --Duration
  EXECUTE sp_trace_setevent @TraceID, 10, 14, @on   --StartTime
  EXECUTE sp_trace_setevent @TraceID, 10, 35, @on   --DatabaseName
  --跟踪 Transact-SQL
  EXECUTE sp_trace_setevent @TraceID, 12,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 12,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 35, @on

  --设定需要过滤的条件，不跟踪 SQL Server Profiler 和 SQL Server Agent
  EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL%'
  --过滤开发者使用MS工具产生的语句，如SQL Server Management Studio和查询分析器等
--  EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft%'

  --过滤执行时间小于1秒的语句
  DECLARE @value    bigint  --毫秒数
  SET @value = 1000
  EXECUTE sp_trace_setfilter @TraceID, 13, 0, 2, @value

  --启用跟踪
  EXECUTe sp_trace_setstatus @TraceID, 1
  PRINT @TraceID
END
ELSE
BEGIN
  PRINT ('未能创建跟踪')
END

RETURN @TraceID --将新跟踪的编号做为存储过程的返回值
GO

 下面定义的这个函数是为了方便返回跟踪信息建立的。

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
--建立人：  高升
--建立日期：2007/08/12
--修改日期：
--功能目的：返回指定日期的跟踪记录
--参数：    日期格式yyyyMMdd
CREATE FUNCTION [dbo].[ReadTrackFile] (@readDate char(8))
RETURNS @traceLog table(
[DBName]    nvarchar(50),
[TextData]  nvarchar(max),
[Duration]  bigint,
[StartTime] datetime,
[LoginName] nvarchar(50),
[HostName]  nvarchar(256))
  AS
BEGIN
  DECLARE @fileAddress nvarchar(245)  --文件地址

  SET @fileAddress = N'C:Documents and SettingsAdministratorMy DocumentsTraceSQL' + @readDate + '.trc'

  INSERT INTO @traceLog SELECT DataBaseName,TextData,Duration,StartTime,LoginName,HostName
    FROM fn_trace_gettable(@fileAddress, default)   --SPID,ApplicationName,EventClassselect

  RETURN
END

跟踪开始后随时可以查看跟踪到的信息，下面是查看示例。

--查看 8/12 执行时间大于10秒的跟踪信息
SELECT * FROM ReadTrackFile('20070812')
 WHERE Duration > 10000

--查看 8/12 用户 GaoS 执行的所有语句
SELECT * FROM ReadTrackFile('20070812')
 WHERE LoginName = 'GaoS'

如果要删除跟踪需要先将跟踪停止，然后才能删除，就是将状态改为2。另外跟踪定义的结束时间到了以后也会自动删除。

--fn_trace_getinfo 参数为0可以看到所以的跟踪，也可指定traceID
SELECT * FROM fn_trace_getinfo (0)

--停止 ID 为 2 的跟踪
EXECUTE sp_trace_setstatus 2, 0

--删除 ID 为 2 的跟踪
EXECUTE sp_trace_setstatus 2, 2

我这人真的很懒，这篇文章对大部分人来说一点意思都没有。因为使用 SQL Server Profiler 定义跟踪要简单直观的多了，写这个存储过程完全是没事找事。不过对我来说就算是总学习吧，毕竟我现在对 SQL Server Profiler 的使用更清楚了。好了现在把这个存储过程交给 Sql Server Agent 去做吧，我每天可以少点一下鼠标了，呵呵！

跟踪这些 Sql 语句对我们优化程式，提升数据库的性能还是很有帮助的，至于如何优化我还在学习中。毕竟这些性能分析是更高层次的 DBA 所做的工作，我现在只算是个学徒罢了。现在重主要的就是把技术含量低的重复工作交给电脑自己去做，让我有更多时间去研究更复杂的东西。