首页 > 代码库 > [原创]SQL SERVER定时任务执行跟踪--供远程查看
[原创]SQL SERVER定时任务执行跟踪--供远程查看
一、背景
每次查需要优化的SQL都需要上外网，通过Profiler，报表或者DMV执行特定sql来查找，来回跑很麻烦，能不能在本地直接监控外网的好性能的SQL呢？方法是有的，我们可以通过把Profiler跟踪转换为T-SQL脚本，在外网做定时任务，就可以定时执行跟踪，然后通过执行特定的脚本将跟踪保存的文件中的数据导出到数据库的指定表中，这样，就可以web后台远程查看这个指定表中的耗性能的SQL了。详细见下面的操作步骤。
二、操作步骤
1.从SQL SERVER PROFILER按照自己指定的条件创建跟踪
2.选择文件-导出-编写跟踪对应的脚本-选择对应的版本号，得到跟踪对应的T-SQL脚本
3.修改最大文件大小，set @maxfilesize = 20
设置跟踪的时间，set @DateTime = DateAdd(mi, 20, getdate())
设置文件滚动更新，参数为2
以年月日作为文件名
set @FileName = DateName(year,getdate()) + DateName(month, getdate()) + DateName(day, getdate()) --以日期做文件名
set @Prefix = N‘D:\ClockingProfiler\‘ + @FileName
得到的参数放入下面的SQL中，创建跟踪：
exec @rc = sp_trace_create @TraceID output, 2, @Prefix, @maxfilesize, @Datetime
修改后得到下面的SQL:
-- Create a Queuedeclare @rc intdeclare @TraceID intdeclare @maxfilesize bigintdeclare @DateTime datetimedeclare @FileName nvarchar(20)declare @Prefix nvarchar(256)set @DateTime = DateAdd(mi, 20, getdate()) --跟踪分钟set @maxfilesize = 20-- 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 shareset @FileName = DateName(year,getdate()) + DateName(month, getdate()) + DateName(day, getdate()) --以日期做文件名set @Prefix = N‘D:\ClockingProfiler\‘ + @FileNameselect @Prefix--参数表示允许文件滚动更新exec @rc = sp_trace_create @TraceID output, 2, @Prefix, @maxfilesize, @Datetimeif (@rc != 0) goto error-- Client side File and Table cannot be scripted-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 10, 15, @onexec sp_trace_setevent @TraceID, 10, 16, @onexec sp_trace_setevent @TraceID, 10, 1, @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, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec sp_trace_setfilter @TraceID, 10, 0, 7, N‘SQL Server Profiler - 1073afc5-8f3b-4f95-a683-006cd83f9bc2‘--设置cpu大于等于msset @intfilter = 100exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: Go
4.导出得到的跟踪文件到指定的表中，若表不存在，先创建再导入；否则，直接导入。
declare @FileName nvarchar(20)declare @Prefix nvarchar(256)set @FileName = DateName(year,getdate()) + DateName(month, getdate()) + DateName(day, getdate()) --以日期做文件名set @Prefix = N‘D:\ClockingProfiler\‘ + @FileName + ‘.trc‘--从跟踪的文件中导入跟踪数据到表中IF OBJECT_ID(N‘DigitalLibDB.dbo.DigitalLibDB_trc‘, N‘U‘) IS NULLBEGIN SELECT * INTO DigitalLibDB_trc From fn_trace_gettable(@Prefix, default);ENDELSEBEGIN INSERT INTO DigitalLibDB_trc SELECT * FROM fn_trace_gettable(@Prefix, default);END
5.创建定时作业，第一步执行步骤3中的SQL, 执行完成后跳转到第二步，第二步执行步骤4中的SQL，如下图。
三、小结
至此，就可以通过web后台远程查看外网数据库表的耗性能耗io的sql，方便优化。当然，前提是你公司先得有这个web后台。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~ ~
~如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[原创]SQL SERVER定时任务执行跟踪--供远程查看