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

,(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
order by t2.wait_duration_ms desc

 
    此文大致总结了通过DMV调查数据库异常的基本方法和步骤，如果大家在调查问题时能够灵活运用，相信对数据库异常情况的定位和解决能够更快更有效。



作者 飞洋过海