C.net_transport, C.client_net_address, C.client_tcp_port, C.local_tcp_port,
R.start_time, R.wait_time, R.wait_type, R.last_wait_type, R.wait_resource,
R.open_transaction_count,GETDATE()
FROMsys.dm_exec_sessions S
LEFTJOINsys.dm_exec_connections C
ONS.session_id =C.session_id
LEFTJOINsys.dm_exec_requests R
ONS.session_id =R.session_id
ANDC.connection_id =R.connection_id
OUTERAPPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE S.is_user_process =1 -- 如果不限制此条件，则查询所有进程（系统和用户进程）
ANDcommand isnotnull
--记录当前阻塞信息
insertintodc_info_BlockedInfo
select@banch_id,t1.resource_type as[lock type],db_name(resource_database_id) as[database]
,t1.resource_associated_entity_id as[blk object]
,t1.request_mode as[lock req] -- lock requested
,t1.request_session_id as[waiter sid] -- spid of waiter
,t2.wait_duration_ms as[wait time]
,(selecttextfromsys.dm_exec_requests asr --- get sql for waiter
crossapply sys.dm_exec_sql_text(r.sql_handle)
wherer.session_id =t1.request_session_id) aswaiter_batch
,(selectsubstring(qt.text,r.statement_start_offset/2,
(casewhenr.statement_end_offset =-1thenlen(convert(nvarchar(max), qt.text)) *2
elser.statement_end_offset end-r.statement_start_offset)/2)
fromsys.dm_exec_requests asr
crossapply sys.dm_exec_sql_text(r.sql_handle) asqt
wherer.session_id =t1.request_session_id) aswaiter_stmt --- statement executing now
,t2.blocking_session_id as[blocker sid] --- spid of blocker
,(selecttextfromsys.sysprocesses asp --- get sql for blocker
crossapply sys.dm_exec_sql_text(p.sql_handle)
wherep.spid =t2.blocking_session_id) asblocker_stmt,getdate()
fromsys.dm_tran_locks ast1, sys.dm_os_waiting_tasks ast2
wheret1.lock_owner_address =t2.resource_address
--记录资源信息
insertintodc_Blocked_Resource_Info
select convert(smallint, req_spid) Asspid,
rsc_dbid Asdbid,
rsc_objid AsObjId,
rsc_indid AsIndId,
substring(v.name, 1, 4) AsType,
substring(rsc_text, 1, 32) asResource,
substring(u.name, 1, 8) AsMode,
substring(x.name, 1, 5) AsStatus ,@banch_id
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type =v.number
andv.type ='LR'
andmaster.dbo.syslockinfo.req_status =x.number
andx.type ='LS'
andmaster.dbo.syslockinfo.req_mode +1=u.number
andu.type ='L'
andsubstring(x.name, 1, 5) ='WAIT'
orderbyspid
execusp_blocker_info @banch_id
end
GO
3. 创建信息收集和邮件报警的SP：
说明：
SP定义了一个邮件发送的过程，需要先配置好数据库的邮件发送（google一下很多的）；
SP需要做到JOB里面，一分钟运行一次收集信息；
SP收集信息时的条件（不同的系统触发条件不一样）：
a. 用户链接数大于550，活动链接数大于40；
b. 阻塞进程比率大于10%；
USE[master]
GO
--创建触发监控条件，并发邮件
CREATEproc[dbo].[ConAlert]
as
begin
DECLARE@connfloat,@activeconnfloat,@blockedcountfloat,@spidcountfloat,@spidblockedfloat,@countint
DECLARE@bodyNVARCHAR(MAX),@subject nvarchar(200)
-- User Connections
Select@