SQLServer通过DMV实现低影响的自动监控和历史场景追溯 - 数据库编程

l(convert(char(5),blocked),'0') When'0'Then' .'

Elseisnull(convert(char(5),blocked),'0') ENDBlkBy,BlockedSQLBuffer

,loginame Login

,db_name(dbid) DBName,convert(varchar,cpu) CPUTime

,convert(varchar,physical_io) DiskIO,Last_Batch LastBatch

,program_name ProgramName, cmd Command

from#Temp

orderbyBlkBy, spid

setnocount off

end

Go

USE[master]
GO

--预警SP
CREATEproc[dbo].[RecodeAndAlertInfo]
as
begin
declare@banch_idint
select@banch_id=isnull(MAX(batch_id),0)+1fromdc_info_SessionConn

--记录当前所有会话信息
insertintodc_info_SessionConn
SELECT@banch_id,S.session_id, R.blocking_session_id,
current_execute_sql =SUBSTRING(T.text,
R.statement_start_offset /2+1,
CASE
WHENstatement_end_offset =-1THENLEN(T.text)
ELSE(R.statement_end_offset -statement_start_offset) /2+1
END),
S.login_name,S.host_name,databaseName=DB_NAME(R.database_id),S.program_name,R.command,
S.status,S.cpu_time, memory_usage_kb =S.memory_usage *8, S.reads, S.writes,
S.transaction_isolation_level,C.connect

SQLServer通过DMV实现低影响的自动监控和历史场景追溯 (八)