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后台。

 

 

 　  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

　　~如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~               ~

　　~如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~