数据库阻塞，关闭5分钟后负责的会话。
从过去的几个月开始，我一直在努力解决数据库服务器上的数据库阻塞问题。试图用多种方法捕捉他们，同时也意识到谁是阻拦的领头人。此应用程序使用典型的主键-外键关系，所有表都是紧密耦合的。很少有表有多达30个外键，因此每当有insert/update/delete时，所有的底层表都会被阻塞。我已经向供应商传达了这一点，他们还试图为可能的表删除PK，并部署在测试环境中，但是，由于批量需要测试，它需要很长时间才能投入生产，并且在某些情况下或其他情况下失败。
由于我支持基于供应商的应用程序，任何更改都需要由多个涉众进行测试，所以我不知道它何时才能真正投入生产。阻塞在时间上变得如此严重，以至于整个门户都关闭了，我别无选择，只能关闭阻塞的SPID。
我使用下面的查询来通过使用sp_whoisactive过程检查阻塞，Adam先生：
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC',到目前为止，我正在研究反应性方法，这意味着，我杀死了负责阻塞的SPID，但是这似乎不太顺利，有时阻塞升级，我必须像消防队员一样跑。我想把我的立场从被动转变为积极主动，并计划建立一个程序，它将从上述程序中接受作为SPID的输入，检查持续时间，如果阻塞超过5分钟，则杀死它。
有时，我看到有许多阻滞剂负责阻塞，如下所示：
我真的不知道如何处理这些阻塞。读一些类似的东西，杰森霍尔先生在这个链接和第二个在这个论坛，然而，我正在寻找一些有基地的sp_whoisactive。
这是为了增加关于数据库隔离的非常相关的一点，我已经将数据库隔离级别从默认更改为“读取提交的快照”。关于同样的话题，我在加入这个论坛的时候问过这的问题，但是这个论坛已经结束了，因为我对这个论坛非常陌生，对我的问题也不清楚。
如对此有任何意见或帮助，将不胜感激。
版本: Microsoft 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 2018年9月7日01:37:51版权(c)微软公司企业版(64位)WindowsNT6.3 (Build 14393：) (Hypervisor)
回答 3
Database Administration用户
发布于 2020-11-04 02:23:15
您可以使用我在下面编写的SQL脚本构建一个动态SQL字符串，该字符串将杀死所有已运行至少5分钟的阻塞会话。
您将希望创建一个执行此SQL脚本的SQL作业(SQL代理作业文档)。(如果需要，可以将SQL Agent作业安排为每秒钟运行一次，但Agent job只允许您在调度时至少选择1分钟，因此如果需要更高的频率，就必须将作业编写出来。)
-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';
-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
)
-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER
INNER JOIN CTE_BlockingSessions AS BS
ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active
-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQLDatabase Administration用户
发布于 2019-04-08 11:46:23
--获取5分钟以上正在运行的进程列表
SELECT spid,
status,
loginame,
hostname,
DB_NAME(dbid),
cmd,
cpu,
physical_io,
DATEDIFF(MI, last_batch, GETDATE()) EventTime ,
program_name
FROM SYS.SYSPROCESSES
WHERE
1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
--Transactions that are open not yet committed or rolled back
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'
AND DATEDIFF(MI,LastBatch,GETDATE())>5 -- greater than 5 mins--通过上述查询对每个SPID获取详细信息
DBCC INPUTBUFFER( @SPID ) KILL @SPIDNB Last_Batch而非LastBatch
Database Administration用户
发布于 2020-11-04 11:33:34
- 试着用NOLOCK来解决你的问题。它可能不会通过执行Select来创建任何类型的锁定。
- NOLOCK的缺点，您可能面临肮脏的阅读问题。所以非常小心地使用NOLOCK。
https://dba.stackexchange.com/questions/234175
复制相似问题
腾讯云开发者
