巧妙使用SQL Profiler监控服务器

如何用SQL Profiler监控服务器执行了哪些操作。

 

常规方法是在开始程序中，找到启动程序SQL Server Profiler启动然后执行监控，但这样做对生产型数据库带来严重性能影响，

通过使用以下存储过程，可以将监控得到的信息直接存储在硬盘文件中，可以把此文件拷贝到普通计算机上进行分析。

 

脚本如下：

/****************************************************/ /* Created by: wxf163 */ /* Date: 2010/10/31 06:12:00 PM */ /****************************************************/ -- 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, 2, N'e:/test.trc', @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, 14, 1, @on exec sp_trace_setevent @TraceID, 14, 9, @on exec sp_trace_setevent @TraceID, 14, 6, @on exec sp_trace_setevent @TraceID, 14, 10, @on exec sp_trace_setevent @TraceID, 14, 14, @on exec sp_trace_setevent @TraceID, 14, 11, @on exec sp_trace_setevent @TraceID, 14, 12, @on exec sp_trace_setevent @TraceID, 15, 15, @on exec sp_trace_setevent @TraceID, 15, 16, @on exec sp_trace_setevent @TraceID, 15, 9, @on exec sp_trace_setevent @TraceID, 15, 17, @on exec sp_trace_setevent @TraceID, 15, 6, @on exec sp_trace_setevent @TraceID, 15, 10, @on exec sp_trace_setevent @TraceID, 15, 14, @on exec sp_trace_setevent @TraceID, 15, 18, @on exec sp_trace_setevent @TraceID, 15, 11, @on exec sp_trace_setevent @TraceID, 15, 12, @on exec sp_trace_setevent @TraceID, 15, 13, @on exec sp_trace_setevent @TraceID, 17, 1, @on exec sp_trace_setevent @TraceID, 17, 9, @on exec sp_trace_setevent @TraceID, 17, 6, @on exec sp_trace_setevent @TraceID, 17, 10, @on exec sp_trace_setevent @TraceID, 17, 14, @on exec sp_trace_setevent @TraceID, 17, 11, @on exec sp_trace_setevent @TraceID, 17, 12, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 2, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 14, @on 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 exec sp_trace_setevent @TraceID, 13, 1, @on exec sp_trace_setevent @TraceID, 13, 9, @on exec sp_trace_setevent @TraceID, 13, 6, @on exec sp_trace_setevent @TraceID, 13, 10, @on exec sp_trace_setevent @TraceID, 13, 14, @on exec sp_trace_setevent @TraceID, 13, 11, @on exec sp_trace_setevent @TraceID, 13, 12, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 9230dcd0-8dd7-4448-9cfc-7ddf8bd84182' -- 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 /**** --如果要关闭这个Trace，需要运行下面的两句话： exec sp_trace_setstatus 2, 0 -- 停止Trace exec sp_trace_setstatus 2, 2 -- 完全关闭Trace，并且删除这个定义 *****/