巧妙使用SQL Profiler监控服务器

 

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

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

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

脚本如下：

-- Create a Queuedeclare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @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 shareexec @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 eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 14, 1, @onexec sp_trace_setevent @TraceID, 14, 9, @onexec sp_trace_setevent @TraceID, 14, 6, @onexec sp_trace_setevent @TraceID, 14, 10, @onexec sp_trace_setevent @TraceID, 14, 14, @onexec sp_trace_setevent @TraceID, 14, 11, @onexec sp_trace_setevent @TraceID, 14, 12, @onexec sp_trace_setevent @TraceID, 15, 15, @onexec sp_trace_setevent @TraceID, 15, 16, @onexec sp_trace_setevent @TraceID, 15, 9, @onexec sp_trace_setevent @TraceID, 15, 17, @onexec sp_trace_setevent @TraceID, 15, 6, @onexec sp_trace_setevent @TraceID, 15, 10, @onexec sp_trace_setevent @TraceID, 15, 14, @onexec sp_trace_setevent @TraceID, 15, 18, @onexec sp_trace_setevent @TraceID, 15, 11, @onexec sp_trace_setevent @TraceID, 15, 12, @onexec sp_trace_setevent @TraceID, 15, 13, @onexec sp_trace_setevent @TraceID, 17, 1, @onexec sp_trace_setevent @TraceID, 17, 9, @onexec sp_trace_setevent @TraceID, 17, 6, @onexec sp_trace_setevent @TraceID, 17, 10, @onexec sp_trace_setevent @TraceID, 17, 14, @onexec sp_trace_setevent @TraceID, 17, 11, @onexec sp_trace_setevent @TraceID, 17, 12, @onexec sp_trace_setevent @TraceID, 10, 15, @onexec sp_trace_setevent @TraceID, 10, 16, @onexec sp_trace_setevent @TraceID, 10, 9, @onexec sp_trace_setevent @TraceID, 10, 17, @onexec sp_trace_setevent @TraceID, 10, 2, @onexec sp_trace_setevent @TraceID, 10, 10, @onexec sp_trace_setevent @TraceID, 10, 18, @onexec sp_trace_setevent @TraceID, 10, 11, @onexec sp_trace_setevent @TraceID, 10, 12, @onexec sp_trace_setevent @TraceID, 10, 13, @onexec sp_trace_setevent @TraceID, 10, 6, @onexec sp_trace_setevent @TraceID, 10, 14, @onexec sp_trace_setevent @TraceID, 12, 15, @onexec sp_trace_setevent @TraceID, 12, 16, @onexec sp_trace_setevent @TraceID, 12, 1, @onexec sp_trace_setevent @TraceID, 12, 9, @onexec sp_trace_setevent @TraceID, 12, 17, @onexec sp_trace_setevent @TraceID, 12, 6, @onexec sp_trace_setevent @TraceID, 12, 10, @onexec sp_trace_setevent @TraceID, 12, 14, @onexec sp_trace_setevent @TraceID, 12, 18, @onexec sp_trace_setevent @TraceID, 12, 11, @onexec sp_trace_setevent @TraceID, 12, 12, @onexec sp_trace_setevent @TraceID, 12, 13, @onexec sp_trace_setevent @TraceID, 13, 1, @onexec sp_trace_setevent @TraceID, 13, 9, @onexec sp_trace_setevent @TraceID, 13, 6, @onexec sp_trace_setevent @TraceID, 13, 10, @onexec sp_trace_setevent @TraceID, 13, 14, @onexec sp_trace_setevent @TraceID, 13, 11, @onexec sp_trace_setevent @TraceID, 13, 12, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 9230dcd0-8dd7-4448-9cfc-7ddf8bd84182'-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: go/****--如果要关闭这个Trace，需要运行下面的两句话：exec sp_trace_setstatus 2, 0-- 停止Traceexec sp_trace_setstatus 2, 2-- 完全关闭Trace，并且删除这个定义*****/