数据库系统异常排查之DMV(3)

,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter   
cross apply sys.dm_exec_sql_text(r.sql_handle)    
where r.session_id = t1.request_session_id) as waiter_batch   
,(select substring(qt.text,r.statement_start_offset/2,    
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2    
else r.statement_end_offset end - r.statement_start_offset)/2+1)    
from sys.dm_exec_requests as r with(nolock)    
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt   
where r.session_id = t1.request_session_id) as waiter_stmt    --- statement executing now   
,t2.blocking_session_id as [blocker sid]                --- spid of blocker   
,(select text from sys.sysprocesses as p with(nolock)    --- get sql for blocker   
cross apply sys.dm_exec_sql_text(p.sql_handle)    
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time  
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)     
where t1.lock_owner_address = t2.resource_address

2. 查看阻塞其他进程的进程（阻塞源头）：
--阻塞其他session的session
select  t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)   
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2

3. 被阻塞时间最长的进程：
--被阻塞时间最长的session
select top 10  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]         
,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter   
cross apply sys.dm_exec_sql_text(r.sql_handle)    
where r.session_id = t1.request_session_id) as waiter_batch