自动对数据库中执行耗时超过10秒的语句自动保存到日志表,后续可以分析出哪些语句存在超时异常,如超时30分钟,10分钟,5分钟等

/*
--建立人：    易登科
--建立日期：2013/08/23
--修改日期：
--功能目的：创建一个新跟踪 
--exec [BeginNewTrack]
--fn_trace_getinfo 参数为0可以看到所以的跟踪，也可指定traceID
--SELECT * FROM fn_trace_getinfo (0)  
--停止 ID 为 2 的跟踪
--EXECUTE sp_trace_setstatus 2, 0
--删除 ID 为 2 的跟踪
--EXECUTE sp_trace_setstatus 1, 2
--EXECUTE sp_trace_setstatus 2, 2
*/
ALTER PROCEDURE [dbo].[BeginNewTrack]  WITH ENCRYPTION
  AS
DECLARE @TraceID        int             --跟踪编号
DECLARE @fileAddress    nvarchar(240)    --跟踪文件的地址加文件名
DECLARE @maxFileSize    bigint          --跟踪文件的最大文件大小
DECLARE @endDateTime    datetime        --跟踪结束的时间
DECLARE @fileCount      int             --跟踪文件的最大个数
DECLARE @result         int             --建立新跟踪的返回值

--跟踪文件保存在C盘我的文档下面，文件名加上当天的年月日 ' + Convert(char(8),GetDate(),112) C:\HTTRC\MQTraceSQL_16879.trc
SET @fileAddress = N'C:\HTTRC\MQTraceSQL'
SET @maxFileSize = 500  --每个跟踪文件最大500M
SET @endDateTime = GetDate() + 0.041666 * 8 -- 跟踪8小时后结束 未设为永久执行直到文件过大
SET @fileCount = 5  --此处未使用此参数，不限定文件个数
 
--定义一个跟踪
EXECUTE @result = sp_trace_create @TraceID output,2, @fileAddress, @maxFileSize,null,2 

IF (@result = 0)
BEGIN
  DECLARE @on   bit -- 1 sp_trace_setevent 必要参数
  SET @on = 1
  --设定需要跟踪的事件，跟踪远程调用存储过程
  

  
 
  EXECUTE sp_trace_setevent @TraceID, 10,  1, @on   --TextData
  EXECUTE sp_trace_setevent @TraceID, 10,  8, @on   --HostName
  EXECUTE sp_trace_setevent @TraceID, 10, 10, @on   --ApplicationName
  EXECUTE sp_trace_setevent @TraceID, 10, 11, @on   --LoginName
  EXECUTE sp_trace_setevent @TraceID, 10, 12, @on   --SPID
  EXECUTE sp_trace_setevent @TraceID, 10, 13, @on   --Duration
  EXECUTE sp_trace_setevent @TraceID, 10, 14, @on   --StartTime
  EXECUTE sp_trace_setevent @TraceID, 10, 15, @on   --EndTime
  EXECUTE sp_trace_setevent @TraceID, 10, 16, @on   --Reads
  EXECUTE sp_trace_setevent @TraceID, 10, 18, @on   --CPU
  EXECUTE sp_trace_setevent @TraceID, 10, 48, @on   --RowCounts
  EXECUTE sp_trace_setevent @TraceID, 10, 35, @on   --DatabaseName
  
  
  EXECUTE sp_trace_setevent @TraceID, 11,  1, @on   --TextData
  EXECUTE sp_trace_setevent @TraceID, 11,  8, @on   --HostName
  EXECUTE sp_trace_setevent @TraceID, 11, 10, @on   --ApplicationName
  EXECUTE sp_trace_setevent @TraceID, 11, 11, @on   --LoginName
  EXECUTE sp_trace_setevent @TraceID, 11, 12, @on   --SPID  
  EXECUTE sp_trace_setevent @TraceID, 11, 13, @on   --Duration
  EXECUTE sp_trace_setevent @TraceID, 11, 14, @on   --StartTime
  EXECUTE sp_trace_setevent @TraceID, 11, 15, @on   --EndTime
  EXECUTE sp_trace_setevent @TraceID, 11, 16, @on   --Reads
  EXECUTE sp_trace_setevent @TraceID, 11, 18, @on   --CPU
  EXECUTE sp_trace_setevent @TraceID, 11, 48, @on   --RowCounts
  EXECUTE sp_trace_setevent @TraceID, 11, 35, @on   --DatabaseName
  --跟踪 Transact-SQL
  
  EXECUTE sp_trace_setevent @TraceID, 12,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 12,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 12, @on  
  EXECUTE sp_trace_setevent @TraceID, 12, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 12, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 12, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 12, 18, @on  
  EXECUTE sp_trace_setevent @TraceID, 12, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 12, 35, @on

 
  EXECUTE sp_trace_setevent @TraceID, 13,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 13,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 13, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 13, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 13, 12, @on  
  EXECUTE sp_trace_setevent @TraceID, 13, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 13, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 13, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 13, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 13, 18, @on  
  EXECUTE sp_trace_setevent @TraceID, 13, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 13, 35, @on
  
  EXECUTE sp_trace_setevent @TraceID, 40,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 40,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 12, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 40, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 40, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 40, 18, @on  
  EXECUTE sp_trace_setevent @TraceID, 40, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 40, 35, @on
  
  EXECUTE sp_trace_setevent @TraceID, 41,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 41,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 12, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 41, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 41, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 41, 18, @on   
  EXECUTE sp_trace_setevent @TraceID, 41, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 41, 35, @on
  

/*
 
  EXECUTE sp_trace_setevent @TraceID, 33,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 33,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 33, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 33, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 33, 12, @on  
  EXECUTE sp_trace_setevent @TraceID, 33, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 33, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 33, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 33, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 33, 18, @on   
  EXECUTE sp_trace_setevent @TraceID, 33, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 33, 35, @on
 

*/ 
/*  
  EXECUTE sp_trace_setevent @TraceID, 44,  1, @on
  EXECUTE sp_trace_setevent @TraceID, 44,  8, @on
  EXECUTE sp_trace_setevent @TraceID, 44, 10, @on
  EXECUTE sp_trace_setevent @TraceID, 44, 11, @on
  EXECUTE sp_trace_setevent @TraceID, 44, 13, @on
  EXECUTE sp_trace_setevent @TraceID, 44, 14, @on
  EXECUTE sp_trace_setevent @TraceID, 44, 15, @on 
  EXECUTE sp_trace_setevent @TraceID, 44, 16, @on   
  EXECUTE sp_trace_setevent @TraceID, 44, 48, @on 
  EXECUTE sp_trace_setevent @TraceID, 44, 35, @on
*/

  --设定需要过滤的条件，不跟踪 SQL Server Profiler 和 SQL Server Agent
  --EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL%'
  --过滤开发者使用MS工具产生的语句，如SQL Server Management Studio和查询分析器等
  --EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft%'

  --过滤执行时间小于60* 1秒的语句
 -- DECLARE @value    bigint  --毫秒数
 -- SET @value = 10 * 1000000
 -- EXECUTE sp_trace_setfilter @TraceID, 13, 0, 2, @value

  --启用跟踪
  EXECUTe sp_trace_setstatus @TraceID, 1
  PRINT @TraceID
END
ELSE
BEGIN
  PRINT ('未能创建跟踪')
END

RETURN @TraceID --将新跟踪的编号做为存储过程的返回值
 

 

--==================================

--建立人：  易登科
--建立日期：2013/08/23
--修改日期：
--功能目的：返回指定日期的跟踪记录
--参数：    日期格式yyyyMMdd
--Select * from ReadTrackFile2('') Order by Duration desc
--
Create FUNCTION  [dbo].[ReadTrackFile] (@readDate char(8))
RETURNS @traceLog table(
[DBName]    nvarchar(50),
[TextData]  nvarchar(max),
[Duration]  bigint,
[StartTime] datetime,
EndTime  datetime,
reads bigint,
RowCounts  bigint,
[LoginName] nvarchar(50),
[HostName]  nvarchar(256),
[applicationName]   nvarchar(256),
[SPID]   bigint,
[CPU]   bigint,
cTrcFile varchar(50)
)
 WITH ENCRYPTION AS
BEGIN
    DECLARE @fileAddress nvarchar(245)   
    
    SELECT @fileAddress = Cast(value as nvarchar(245)) FROM fn_trace_getinfo (0) Where  Convert(varchar(150),value) like '%MQTraceSQL%'
    IF @fileAddress is null
    begin
        return 
    End
    

    
  INSERT INTO @traceLog SELECT DataBaseName,TextData,Duration,StartTime,EndTime,reads,RowCounts,LoginName,HostName,applicationName,Spid,Cpu,@fileAddress as cTrcFile
    FROM fn_trace_gettable(@fileAddress, default)   --SPID,ApplicationName,EventClassselect

  RETURN
END

--===================================

  Create procedure  [dbo].[SaveMQTrc]  WITH ENCRYPTION as 
 begin
    Declare @nYM as int,@icTrcFile as varchar(50)
    Declare @iStartTime as datetime 
    Declare @iminTime as datetime
    SELECT top 1
           @icTrcFile = cTrcFile
         FROM ReadTrackFile('') 
    IF @icTrcFile is null
    begin 
        Exec dbo.BeginNewTrack
        return 
    End       
    
  SELECT @iminTime =  max([StartTime])
         FROM [MQTRC]
             Where not [StartTime] is null
                 
    Set @iStartTime = CAST(year(getdate()) as CHAR(4))+'-'+CAST(datepart(mm,getdate()) as CHAR(2))+'-01'
    if @iminTime > @iStartTime
    begin 
        set @iStartTime = @iminTime
    End    
    
    Set @nYM = convert(varchar(6),getdate(),112 )
    select @iStartTime
    Select @icTrcFile
    
    
    SELECT * INTO #tmp_SaveTrack        
         FROM ReadTrackFile('') 
         where [StartTime] > @iStartTime 
         
    Create  INDEX IX_tmp_SaveTrack    ON #tmp_SaveTrack (StartTime,SPID)

     Update     #tmp_SaveTrack SET EndTime  = A.EndTime
        From (SELECT * FROM #tmp_SaveTrack Where Not Endtime IS null)    A
        Where     #tmp_SaveTrack.StartTime = A.StartTime
            And #tmp_SaveTrack.SPID = A.SPID
 
     insert into [MQTRC] (
         [DBName]
        ,[TextData]
        ,[Duration]
        ,[StartTime]
        ,EndTime
        ,reads
        ,RowCounts
        ,CPU
        ,[LoginName]
        ,[HostName]
        ,nTime
        ,nYM
        ,cTrcFile
        ,[SPID]
        ,[ApplicationName]
        )SELECT cast([DBName] as nvarchar(10))
        ,[TextData]
        ,[Duration]
        ,[StartTime]
        ,EndTime
        ,reads
        ,RowCounts
        ,CPU
        ,cast([LoginName] as nvarChar(18))
        ,Cast([HostName] as nvarchar(16))
        ,DATEDIFF(ss,[StartTime],EndTime) as nTime
        ,Year([StartTime])*100+MONTH([StartTime])  as nYM
        ,cast(cTrcFile as varchar(30))
        ,[SPID]
        ,[ApplicationName]
         From #tmp_SaveTrack
        where [StartTime] > @iStartTime 
            And  [StartTime] <> EndTime
             
        
    Drop table #tmp_SaveTrack      
 end