记录消耗超过200MB内存的语句,自动清理消耗超过1GB内存的语句

---- Exec MQTrcMemoryOutAutoKillSPID 20000,1000
Create procedure MQTrcMemoryOutAutoKillSPID 
  (@nMem as int,        --需要保存记录的内存值(KB)
    @nMaxMem as int        --最大内存值(MB),超过将KILL进程
    )
 WITH ENCRYPTION as 
Begin

    Declare @iStartTime as datetime 
    Declare @iminTime as datetime
    Declare @icSQLSTring as varchar(50)
    IF cast(getdate() as date) >'2022-11-01'
    Begin
        Print getdate()
        REturn 
    End
 

 Select DQM.*,SPR.cpu,SPR.hostname,SPR.loginame,SPR.physical_io,SPR.program_name,SPR.cmd  
    into #tmp_memoryOut
 From (
    SELECT (case when mg.max_used_memory_kb > mg.granted_memory_kb then mg.max_used_memory_kb else mg.granted_memory_kb end )
                /(1024.0) as nMemoryMB, mg.session_id as SPID, t.text as cSQLText ,mg.request_time as dRequest
              FROM sys.dm_exec_query_memory_grants AS mg
              CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
              CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
               Where mg.granted_memory_kb >= @nMem 
            
             ) DQM
             Left join ( Select  spid,Max(cpu) as cpu
            ,Max(hostname) as hostname
            ,Max(loginame) as LogiName
            ,Max(physical_io) as physical_io
            ,Max(program_name) as program_name
            ,Max(cmd) as cmd  From  [Master].[dbo].[SYSPROCESSES] SPR group by spid ) SPR on SPR.spid = DQM.sPID
             Where not SPR.loginame in ('sa')

IF Exists(SELECT * FROM #tmp_memoryOut)
    Begin
        Insert into MQTRCMemoryTimeKill (nMemoryMB,SPID,cSQLText,dRequest,cpu, hostname,LoginName,physical_io,program_name,cmd)
        Select Max(nMemoryMB) as nMemoryMB,SPID, Max(cSQLText) as cSQLText
            ,Max(dRequest) as dRequest
            ,Max(cpu) as cpu
            ,Max(hostname) as hostname
            ,Max(loginame) as LoginName
            ,Max(physical_io) as physical_io
            ,Max(program_name) as program_name
            ,Max(cmd) as cmd 
            From #tmp_memoryOut 
            Where not cSQLText is null 
            Group by SPID

        IF Exists(SELECT SPID,MAX(cmd) as CPU,Max(nMemoryMB) as nMemoryMB,Max(dRequest) as dRequest FROM #tmp_memoryOut 
            Where nMemoryMB >=@nMaxMem 
                And datepart(hh,dRequest)<=19
                And datepart(hh,dRequest)>=8 
                And not cSQLText is null 
                Group BY SPID)
        Begin
            DECLARE c_TrcMemoryOutX CURSOR FOR      
            SELECT SPID,MAX(cmd) as CPU,Max(nMemoryMB) as nMemoryMB,Max(dRequest) as dRequest FROM #tmp_memoryOut 
            Where nMemoryMB >=@nMaxMem 
                And datepart(hh,dRequest)<=19
                And datepart(hh,dRequest)>=8 
                And not cSQLText is null      Group BY SPID

            Select * From #tmp_memoryOut Where nMemoryMB >=@nMaxMem 
                And datepart(hh,dRequest)<=19
                And datepart(hh,dRequest)>=8 
                And not cSQLText is null      

            DECLARE @SPID as int,@cmd as nvarchar(18),@nMemoryMB as numeric(12,2) ,@dRequest as datetime
             
            OPEN c_TrcMemoryOutX
            FETCH NEXT FROM c_TrcMemoryOutX INTO @SPID,@cmd,@nMemoryMB,@dRequest
            WHILE (@@FETCH_STATUS = 0)
            BEGIN    
                if @cmd = 'SELECT'
                Begin
                    Update MQTRCMemoryTimeKill Set isKill = 1 ,dKillDate = GETDATE() 
                                                    ,cKill = '消耗内存超标!!!'+' 已执行KILL.' +'RUN: Kill SPID:' + cast(@SPID as varchar(12))
                                Where  SPID = @SPID And dRequest = @dRequest
                            set @icSQLSTring = 'Kill ' + cast(@SPID as varchar(12))
                            Exec (@icSQLSTring)    
                            Print '=====================================' 
                            Print GETDATE()
                            Print @dRequest
                            Print  '消耗内存超标!!!'
                            Print @icSQLSTring+' 完成!'
                 
                            Print '====================================='
                End
                FETCH NEXT FROM c_TrcMemoryOutX INTO @SPID,@cmd,@nMemoryMB,@dRequest
            End
        End
    End
    
    Print '执先完成! MQ'
     
    
End