SQL Server阻塞与死锁的诊断方法

-- 查找当前所有被阻塞的会话
select 
    waiting_task_address,
    session_id as blocked_session,
    exec_context_id,
    wait_duration_ms,
    wait_type,
    resource_address,
    blocking_task_address,
    blocking_session_id as blocking_session,
    blocking_exec_context_id,
    resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null
order by wait_duration_ms desc;

blocked_session: 56
blocking_session: 53
wait_type: lck_m_s (等待共享锁)
wait_duration_ms: 1103500 (约18分钟)

-- 查看指定数据库的所有锁
select 
    request_session_id as spid,
    resource_type as lock_type,
    resource_database_id as db_id,
    case resource_type
        when 'object' then object_name(resource_associated_entity_id, resource_database_id)
        when 'database' then ' '
        else (select object_name(object_id, resource_database_id)
              from sys.partitions
              where hobt_id = resource_associated_entity_id)
    end as object_name,
    resource_description as resource_desc,
    request_mode as lock_mode,
    request_status as lock_status
from sys.dm_tran_locks
where resource_database_id = db_id('yourdatabasename')
order by request_session_id, resource_type;

-- 查看所有被阻塞的请求及其sql文本
select 
    r.session_id as blocked_spid,
    r.blocking_session_id as blocking_spid,
    r.wait_type,
    r.wait_time / 1000.0 as wait_time_sec,
    r.wait_resource,
    r.command,
    db_name(r.database_id) as database_name,
    r.cpu_time,
    r.logical_reads,
    r.total_elapsed_time / 1000.0 as elapsed_time_sec,
    substring(st.text, (r.statement_start_offset/2)+1,
        ((case r.statement_end_offset
            when -1 then datalength(st.text)
            else r.statement_end_offset
        end - r.statement_start_offset)/2) + 1) as blocked_sql_text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.blocking_session_id > 0
order by r.wait_time desc;

-- 综合诊断：显示阻塞链、sql文本、锁信息
with blockingchain as (
    select 
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000.0 as wait_sec,
        r.wait_resource,
        db_name(r.database_id) as db_name,
        substring(st.text, (r.statement_start_offset/2)+1,
            ((case r.statement_end_offset
                when -1 then datalength(st.text)
                else r.statement_end_offset
            end - r.statement_start_offset)/2) + 1) as sql_text,
        s.login_name,
        s.host_name,
        s.program_name,
        r.status,
        r.cpu_time,
        r.logical_reads
    from sys.dm_exec_requests r
    inner join sys.dm_exec_sessions s on r.session_id = s.session_id
    cross apply sys.dm_exec_sql_text(r.sql_handle) st
    where r.session_id <> @@spid
)
select 
    bc.session_id as [被阻塞会话],
    bc.blocking_session_id as [阻塞源会话],
    bc.wait_sec as [等待秒数],
    bc.wait_type as [等待类型],
    bc.wait_resource as [等待资源],
    bc.db_name as [数据库],
    bc.sql_text as [被阻塞sql],
    blocker.sql_text as [阻塞者sql],
    bc.login_name as [被阻塞登录],
    blocker.login_name as [阻塞者登录],
    bc.host_name as [被阻塞主机],
    bc.program_name as [被阻塞程序]
from blockingchain bc
left join blockingchain blocker on bc.blocking_session_id = blocker.session_id
where bc.blocking_session_id > 0
order by bc.wait_sec desc;

-- 禁用表的锁升级
alter table tablename set (lock_escalation = disable);

-- 启用分区级锁升级（仅升级到分区级别，而非表级）
alter table tablename set (lock_escalation = auto);

-- 恢复默认行为（表级锁升级）
alter table tablename set (lock_escalation = table);

select 
    name as table_name,
    lock_escalation_desc
from sys.tables
where lock_escalation_desc <> 'table'
order by name;

-- 设置阻塞阈值为20秒
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'blocked process threshold', 20;
reconfigure;

-- 创建扩展事件会话
create event session blockedprocessreport on server
add event sqlserver.blocked_process_report
add target package0.ring_buffer
with (
    max_memory = 4096 kb, 
    event_retention_mode = allow_single_event_loss
);

-- 启动会话
alter event session blockedprocessreport on server state = start;

select 
    event_data.value('(event/@timestamp)[1]', 'datetime2') as event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 as duration_sec,
    event_data.value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
    event_data.value('(event/data[@name="blocked_process"]/value)[1]', 'nvarchar(max)') as blocked_process_report
from (
    select convert(xml, event_data) as event_data
    from sys.dm_xe_session_targets t
    join sys.dm_xe_sessions s on t.event_session_address = s.address
    where s.name = 'blockedprocessreport'
        and t.target_name = 'ring_buffer'
) as x
order by event_time desc;

-- 查找锁等待最严重的表和索引
select 
    db_name(ios.database_id) as database_name,
    object_name(ios.object_id, ios.database_id) as table_name,
    i.name as index_name,
    ios.row_lock_count as [行锁次数],
    ios.row_lock_wait_count as [行锁等待次数],
    ios.row_lock_wait_in_ms as [行锁等待毫秒],
    case when ios.row_lock_count > 0 
        then ios.row_lock_wait_in_ms * 1.0 / ios.row_lock_count 
        else 0 end as [平均行锁等待ms],
    ios.page_lock_count as [页锁次数],
    ios.page_lock_wait_count as [页锁等待次数],
    ios.page_lock_wait_in_ms as [页锁等待毫秒],
    ios.page_latch_wait_count as [页闩锁等待次数],
    ios.page_latch_wait_in_ms as [页闩锁等待毫秒],
    ios.page_io_latch_wait_count as [页io闩锁等待次数],
    ios.page_io_latch_wait_in_ms as [页io闩锁等待毫秒]
from sys.dm_db_index_operational_stats(db_id(), null, null, null) ios
join sys.indexes i on ios.object_id = i.object_id and ios.index_id = i.index_id
where ios.row_lock_wait_in_ms > 0 
   or ios.page_lock_wait_in_ms > 0
order by (ios.row_lock_wait_in_ms + ios.page_lock_wait_in_ms) desc;

-- 排除无关的等待类型，聚焦业务相关等待
select top 20
    wait_type as [等待类型],
    waiting_tasks_count as [等待任务数],
    wait_time_ms as [总等待毫秒],
    max_wait_time_ms as [最大单次等待ms],
    signal_wait_time_ms as [信号等待ms],
    wait_time_ms - signal_wait_time_ms as [资源等待ms],
    cast((wait_time_ms - signal_wait_time_ms) * 100.0 / sum(wait_time_ms) over() as decimal(5,2)) as [占比%]
from sys.dm_os_wait_stats
where wait_type not in (
    -- 排除后台任务、空闲等待等无关等待类型
    'broker_eventhandler', 'broker_receive_waitfor', 'broker_task_stop',
    'broker_to_flush', 'broker_transmitter', 'checkpoint_queue',
    'chkpt', 'clr_auto_event', 'clr_manual_event', 'clr_semaphore',
    'dbmirror_dbm_event', 'dbmirror_events_queue', 'dbmirror_worker_queue',
    'dbmirroring_cmd', 'dirty_page_poll', 'dispatcher_queue_semaphore',
    'execsync', 'fsagent', 'ft_ifts_scheduler_idle_wait', 'ft_iftshc_mutex',
    'hadr_clusapi_call', 'hadr_filestream_iomgr_iocompletion', 'hadr_logcapture_wait',
    'hadr_notification_dequeue', 'hadr_timer_task', 'hadr_work_queue',
    'ksource_wakeup', 'lazywriter_sleep', 'logmgr_queue',
    'ondemand_task_queue', 'preemptive_xe_gettargetstate', 
    'pwait_all_components_initialized', 'qds_persist_task_main_loop_sleep',
    'qds_async_queue', 'qds_cleanup_stale_queries_task_main_loop_sleep',
    'request_for_deadlock_search', 'resource_queue', 'server_idle_check',
    'sleep_bpool_flush', 'sleep_dbstartup', 'sleep_systemtask',
    'sleep_task', 'sleep_tempdbstartup', 'sni_http_accept',
    'sqltrace_buffer_flush', 'sqltrace_incremental_flush_sleep',
    'waitfor', 'waitfor_taskshutdown', 'wait_for_results',
    'xe_dispatcher_join', 'xe_dispatcher_wait', 'xe_timer_event'
)
and wait_time_ms > 0
order by wait_time_ms desc;

dbcc sqlperf('sys.dm_os_wait_stats', clear);

-- 杀掉长时间阻塞的会话（谨慎使用）
kill <blocking_session_id>;

-- 对于读操作，考虑使用read uncommitted（如果业务允许脏读）
set transaction isolation level read uncommitted;
select * from orders where orderid = 12345;

-- 启用snapshot隔离级别（避免读操作被阻塞）
alter database yourdatabase set allow_snapshot_isolation on;
alter database yourdatabase set read_committed_snapshot on;

第1步：快速定位阻塞链
→ 运行脚本1：sys.dm_os_waiting_tasks
→ 找出blocking_session_id

第2步：查看阻塞者和被阻塞者的sql
→ 运行脚本4：综合阻塞诊断
→ 确认是什么sql导致阻塞

第3步：分析锁状态
→ 运行脚本2：sys.dm_tran_locks
→ 确认锁类型（s/x/ix）和锁粒度（行/页/表）

第4步：评估影响范围
→ 运行脚本6：sys.dm_os_wait_stats
→ 确认阻塞是否是系统性问题

第5步：决策
→ 短期：kill阻塞会话（如果必要）
→ 中期：优化sql/索引/隔离级别
→ 长期：调整应用架构