SQL阻塞原因造成系统多功能无响应的分析解决思路

-- 查询并确认阻塞源头
select t.spid, t.kpid, t.blocked, t.status, t.waittype, t.lastwaittype, t.waitresource
    , t.hostname, t.hostprocess, t.loginame, t.program_name, t.waittime
    , DB_NAME(t.dbid) DbName, t.login_time, t.last_batch
    , t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text
from    master.sys.sysprocesses t
    outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
where  t.blocked = 0
    and t.spid in (select blocked from master.sys.sysprocesses where blocked != 0)

-- 查看所有会话的状态、等待类型及当前正在执行SQL脚本
select t.spid, t.kpid, t.blocked, t.status, t.waittype, t.lastwaittype, t.waitresource, t.waittime
    , DB_NAME(t.dbid) DbName, t.login_time, t.last_batch, t.loginame, t.program_name, t.hostname, t.hostprocess
    , t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text
from    master.sys.sysprocesses t  outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
where    t.spid >= 50

-- 查看所有会话当前持有和申请的锁资源
select  l.request_session_id,
        l.resource_type,
        l.resource_subtype,
        l.request_status,
        l.request_mode,
        l.resource_description,
        case l.resource_type 
            when 'database' then DB_NAME(l.resource_database_id)
            when 'object' then object_name(l.resource_associated_entity_id)
            else OBJECT_NAME(p.object_id)
        end as obj_name,
        p.index_id
from sys.dm_tran_locks l
    left join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
order by l.request_session_id, l.resource_type