自动清理超时2分钟或CPU满负荷超过30秒的SQL语句

---- Exec MQTrcTimeOutAutoKillSPID 5,0
Create procedure MQTrcTimeOutAutoKillSPID 
    (@nMi as int,        --超时的最大分钟数
    @isCpuMax as int    --CPU满负荷的语句是否处理
    ) as 
Begin

    Declare @iStartTime as datetime 
    Declare @iminTime as datetime
    Declare @icSQLSTring as varchar(50)
 

    select 'HTSAC' as [DBName]
        ,spid
         ,'' as [TextData]
         ,0 as [Duration]
         ,last_batch as [StartTime]
         ,GETDATE() as EndTime
         ,physical_io as reads
         ,'' as RowCounts
         ,CPU
         ,cast(loginame as nvarChar(18)) as loginame
        ,Cast([HostName] as nvarchar(16)) as [HostName]
        ,DATEDIFF(ss,last_batch,GETDATE()) as nTime
        ,Year(last_batch)*100+MONTH(last_batch)  as nYM
        ,'' as cTrcFile
        ,[SYSPROCESSES].cmd
            into #tmp_ReadTrack
     FROM
    [Master].[dbo].[SYSPROCESSES] WHERE 
      status IN('runnable','suspended') And not loginame IN('sa')
    And ((Datediff(mi,last_batch,GETDATE())>@nMi ) --最后处理时间大于最大分钟数
        Or (CPU >=2147483000 AND @isCpuMax >0 And  Datediff(SS,last_batch,GETDATE())>30))    --CPU满负荷的语句超过30秒的
    And datepart(hh,last_batch)<=19            --只在工作时间段处理
    And datepart(hh,last_batch)>=8

    --显示满足条件的语句
    Select *
         FROM  #tmp_ReadTrack
 

    IF Exists(SELECT * FROM #tmp_ReadTrack)
    Begin        
    

        /* 保存日志
         insert into dbo.MQTRCOutTimeKill(
             [DBName]
             ,spid
            ,[TextData]
            ,[Duration]
            ,[StartTime]
            ,EndTime
            ,reads
            ,RowCounts
            ,CPU
            ,[LoginName]
            ,[HostName]
            ,nTime
            ,nYM
            ,cTrcFile
            ,cMD
            )
            Select Max([DBName]) as [DBName]
            ,spid
            ,Max([TextData]) as [TextData]
            ,Max([Duration]) as [Duration]
            ,Max([StartTime]) as [StartTime]
            ,Max(EndTime) as EndTime
            ,Max(reads) asreads 
            ,Max(RowCounts) as RowCounts
            ,Max(CPU) as CPU
            ,Max(loginame) as loginame
            ,Max([HostName]) as [HostName]
            ,Max(nTime) as nTime
            ,MAX(nYM) AS nYM
            ,Max(cTrcFile) as cTrcFile
            ,Max(cmd) as cmd
         FROM  #tmp_ReadTrack 
        Group by SPID
        */

        -- 放入游标
        DECLARE c_TrctimeOutX CURSOR FOR      
        SELECT SPID,MAX([TextData]) as [TextData],Min([StartTime]) as [StartTime],MAX(CPU) as CPU,Max(cMD) as cMD 
            FROM #tmp_ReadTrack 
            Where 1=1 Group BY SPID
     
        DECLARE @SPID as int,@RUNSQL as varchar(max),@StTime as datetime,@NewRunSQL as varchar(Max),@CPU as int,@cMD as varchar(100)
        create table #_tmp_MQGetSPidSQLExec(cEventType varchar(100),cParameters int,cSQLInfo varchar(max))
        OPEN c_TrctimeOutX
        FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
        WHILE (@@FETCH_STATUS = 0)
        BEGIN    
            
        
            insert #_tmp_MQGetSPidSQLExec exec MQGetSPidSQLExec @SPID
            Select Top 1 @NewRunSQL =cSQLInfo  from #_tmp_MQGetSPidSQLExec
            IF @NewRunSQL <>''  
            Begin
                IF  ((PATindex('%select%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi
                    OR (PATindex('%exec%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi*2
                    OR (PATindex('%update%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi*3
                    OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(SS,@StTime,GETDATE())> @nMi*30/2 And PATindex('%select%',@cMD) >0 )
                    OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(SS,@StTime,GETDATE())>@nMi*30 And PATindex('%exec%',@cMD) >0 )
                    OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(MI,@StTime,GETDATE())>@nMi And PATindex('%exec%',@NewRunSQL) >0 )
                    )        --OR PATindex('%exec%',@NewRunSQL) >0
                Begin
                    /*    更新日志的KILL情况
                    Update MQTRCOutTimeKill Set isKill = 1 ,dKillDate = GETDATE(),[TextData] = @NewRunSQL
                                            ,cKill = cast(datediff(ss,@StTime,GETDATE()) as varchar(30))+'秒钟'+' 已执行KILL.' +'RUN: Kill SPID:' + cast(@SPID as varchar(12))
                        Where  SPID = @SPID And [StartTime] = @StTime
                    */
                    set @icSQLSTring = 'Kill ' + cast(@SPID as varchar(12))
                    Exec (@icSQLSTring)    
 
                End

                /*    更新日志的KILL情况
                Else
                Begin
                
                    
                    Update MQTRCOutTimeKill Set isKill = 0 ,dKillDate = GETDATE(),[TextData] = @NewRunSQL
                        ,cKill = cast(datediff(ss,@StTime,GETDATE()) as varchar(30))+'秒钟'+' 未执行KILL!!!' + case when datediff(ss,@StTime,GETDATE())>20 then '非select语句('+@cMD+')' else '小于20秒' End
                        Where  SPID = @SPID And [StartTime] = @StTime
 
                    Print '====================================='
                
                    
                End
                */
            End
            
            truncate table #_tmp_MQGetSPidSQLExec
            
            FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
        End            
        
         
        
        CLOSE c_TrctimeOutX
        DEALLOCATE c_TrctimeOutX    
        
        Drop table #_tmp_MQGetSPidSQLExec    
    End
 
    Drop table #tmp_ReadTrack
    
    Print '执先完成! MQ'
     
    
End
     

=======================

Create proc MQGetSPidSQLExec(@spid int)
 WITH ENCRYPTION AS
BEGIN
    dbcc inputbuffer (@spid)
End