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 Queue declare @rc int declare @TraceID int declare @maxfilesize bigint declare @DateTime datetime declare @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 share set @FileName = DateName(year,getdate()) + DateName(month, getdate()) + DateName(day, getdate()) --以日期做文件名 set @Prefix = N'D:\ClockingProfiler\' + @FileName select @Prefix --参数表示允许文件滚动更新 exec @rc = sp_trace_create @TraceID output, 2, @Prefix, @maxfilesize, @Datetime 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, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 1, @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 -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1073afc5-8f3b-4f95-a683-006cd83f9bc2' --设置cpu大于等于ms set @intfilter = 100 exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter -- 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
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 NULL BEGIN SELECT * INTO DigitalLibDB_trc From fn_trace_gettable(@Prefix, default); END ELSE BEGIN INSERT INTO DigitalLibDB_trc SELECT * FROM fn_trace_gettable(@Prefix, default); END
5.创建定时作业，第一步执行步骤3中的SQL, 执行完成后跳转到第二步，第二步执行步骤4中的SQL，如下图。
三、小结
至此，就可以通过web后台远程查看外网数据库表的耗性能耗io的sql，方便优化。当然，前提是你公司先得有这个web后台。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~ ~
~如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
