SQL Server 2019故障转移后log_table阻塞问题的排查与解决咨询

我有一张名为log_table的表，以及两个存储过程：用于插入日志的insert_sp和执行更新的update_sp。部署了双节点SQL Server故障转移集群，每次手动或自动故障转移后，log_table会出现30-50分钟的阻塞，其他时间无此问题。

已尝试的操作：

• 杀死阻塞SPID，但不断出现新的阻塞ID，无法解决
• 重编译两个存储过程，无效
• 尝试修改update_sp但未实际执行，此时存储过程已处于阻塞状态

环境：SQL Server 2019 Enterprise（CU19）

存储过程代码 #

update_sp #

ALTER PROCEDURE [dbo].[update_sp]
    @ID varchar(32),
    @Resp varchar(max),
    @RespDate datetime,
    @ServTime float = null
AS
BEGIN
    UPDATE [dbo].[log_table]
    SET Resp = @Resp,
        RespDate = @RespDate,
        ServTime = DATEDIFF(MILLISECOND,RequestDate,@RespDate)
    WHERE
        ID = @ID AND
        RecordTime > DATEADD(HOUR, -1 , GETDATE())  
END

insert_sp #

ALTER PROCEDURE [dbo].[insert_sp]
    @ID varchar(32),
    @Resp varchar(max),
    @RequestDate datetime,
    @RespDate datetime,
    @ServTime float=null,
    @TranCode varchar(64) = NULL
AS
BEGIN
    INSERT INTO [dbo].[log_table] ([ID], [Resp], [RequestDate],[RespDate], [ServTime], TranCode)
     VALUES (@ID, @Resp, @RequestDate, @RespDate, @ServTime, @TranCode)
END

一、故障转移时阻塞的核心原因 #

1. 执行计划缓存失效引发编译风暴：故障转移后SQL Server会清空缓存的执行计划，当大量请求同时调用update_sp时，每个请求都会触发执行计划编译，耗尽CPU资源，进而导致阻塞链形成。
2. 缺失针对性索引：update_sp的WHERE条件包含ID和RecordTime，如果没有组合索引，故障转移后大量更新请求会执行全表扫描，锁定更多行资源，引发连锁阻塞。
3. 锁升级触发表级锁：默认隔离级别下，大量并发更新操作容易触发锁升级（从行锁升级为表锁），故障转移后缓存缺失、资源竞争加剧，锁升级概率大幅提升，直接导致整表阻塞。
4. 故障转移后的事务恢复竞争：故障转移时SQL Server需要重做未提交的事务，log_table作为日志表本身有大量未完成写入，与新的更新请求争夺IO和锁资源，进一步加重阻塞。

二、临时终止阻塞的方法 #

1. 批量清理阻塞会话：执行以下脚本批量终止针对log_table的阻塞会话（紧急场景下使用，注意评估业务影响）：

DECLARE @killStmt NVARCHAR(MAX) = ''
SELECT @killStmt += 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';'
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
AND resource_associated_entity_id = OBJECT_ID('dbo.log_table')
AND request_session_id <> @@SPID

EXEC sp_executesql @killStmt

2. 强制截断长事务：检查应用端是否存在调用存储过程的长事务，联系应用团队缩短事务时长，减少锁持有时间。
3. 临时启用快照隔离：临时切换数据库隔离级别为READ COMMITTED SNAPSHOT，用行版本控制替代锁，避免读写阻塞：

ALTER DATABASE [你的数据库名] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

三、长期预防措施 #

1. 创建针对性组合索引：为update_sp的查询条件创建组合索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_log_table_ID_RecordTime ON dbo.log_table (ID, RecordTime)
INCLUDE (RequestDate); -- 包含UPDATE需要读取的RequestDate，避免键查找

2. 优化存储过程执行计划：给update_sp添加WITH RECOMPILE选项，避免参数嗅探，同时减少故障转移后的编译压力：

ALTER PROCEDURE [dbo].[update_sp]
    @ID varchar(32),
    @Resp varchar(max),
    @RespDate datetime,
    @ServTime float = null
AS
BEGIN
    UPDATE [dbo].[log_table]
    SET Resp = @Resp,
        RespDate = @RespDate,
        ServTime = DATEDIFF(MILLISECOND,RequestDate,@RespDate)
    WHERE
        ID = @ID AND
        RecordTime > DATEADD(HOUR, -1 , GETDATE())  
END WITH RECOMPILE;

3. 启用READ COMMITTED SNAPSHOT：长期启用快照隔离，从根源上避免读写阻塞：

ALTER DATABASE [你的数据库名] SET READ_COMMITTED_SNAPSHOT ON;

4. 故障转移后预热执行计划：故障转移完成后，手动调用存储过程生成执行计划，避免并发编译风暴：

-- 预热update_sp
EXEC update_sp @ID='dummy_id', @Resp='dummy', @RespDate=GETDATE();
-- 预热insert_sp
EXEC insert_sp @ID='dummy_id', @Resp='dummy', @RequestDate=GETDATE(), @RespDate=GETDATE();

5. 调整锁升级策略：根据服务器内存情况，禁用log_table的锁升级（需评估内存占用风险）：

ALTER TABLE dbo.log_table SET (LOCK_ESCALATION = DISABLE);

6. 升级SQL Server累积更新：当前使用CU19，建议升级到最新CU，修复故障转移相关的锁机制、执行计划缓存bug。

内容的提问来源于stack exchange，提问作者HAKAN MAZI