数据库堵塞问题解决

--察看堵塞任务 blocking_session_id 为null表示没有堵塞
SELECT
blocking_session_id 堵塞等待任务的id, session_id 等待任务id, wait_duration_ms/1000 --堵塞时间(s)
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL

--堵塞数量统计
SELECT
blocking_session_id ,COUNT(*) counts
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GROUP BY blocking_session_id
ORDER BY counts desc
--2. 堵塞sql语句或者存储过程
SELECT t.*
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id in(blocking_session_id )--堵塞任务id

--杀掉进程
SELECT
blocking_session_id ,COUNT(*) counts
into #spid
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GROUP BY blocking_session_id
ORDER BY counts desc

declare @spid int
DECLARE @Sql NVARCHAR(max)
declare mycursor cursor for --声明游标mycursor
--select字段个数必须要和游标取出的变量个数相同且对应
select blocking_session_id from #spid
open mycursor --打开游标
--从游标里取出数据赋值到声明的变量中
fetch next from mycursor into @spid
while (@@fetch_status=0)
begin

SET @Sql = ''
select @Sql =@Sql +'kill '+CAST(@spid as varchar)
exec (@sql)
--用游标去取下一条记录
fetch next from mycursor into @spid
end
close mycursor --关闭游标
DEALLOCATE mycursor --撤销游标
drop table #spid