sqlserver drop index导致主从延迟问题

一、 问题背景

收到业务A主从延迟的告警，该业务是sqlserver一主两从架构，但告警时只有一个从库有延迟，另一个正常，另外也没有收到其他告警。

 

二、 问题分析及处理

登录检查，发现从库1有延迟而从库2正常

--主库查：
select replica_id,last_commit_time as 'primary_last_commit_tm' from sys.dm_hadr_database_replica_states where is_local=1;
select replica_id,last_commit_time as 'standby_last_commit_tm' from sys.dm_hadr_database_replica_states where is_local=0;

--根据replica_id找到延迟的从库
select * from sys.availability_replicas where replica_id=xxx

检查从库1 CPU,IO等均正常，但在查询从库1当前执行sql时，发现查询的语句被阻塞。

查询sysprocesses视图找到阻塞会话及等待资源

select * from sys.sysprocesses;

发现被阻塞进程基本都在等待后台会话27，而27在等待会话208，等待架构修改锁，等待资源为TAB: 8:1266871630:5。后台会话27在执行的sql可以通过sql_handle查到，这里是一个drop索引的语句。

查询sys.objects发现后台进程27在等待的资源是一个业务表的索引（即要drop的索引），而208进程正好在查询该表，也刚好用到该索引。

解决方法：kill掉208会话。27会话无阻塞后drop index语句可立即执行，其他会话的阻塞也消失，主从同步很快恢复。

 

三、 故障原理

回看这次问题梳理一下发生的流程：

• 主库执行drop index语句，从库1及从库2同步相应日志进行应用，也在从库执行drop index语句
• 从库1恰好有一个慢查询且用到了该索引；从库2并未执行此慢查询
• 从库1的drop index语句被阻塞，需等待慢查询执行完，等待类型为LCK_M_SCH_M；从库2未被阻塞，drop index成功执行
• 由于从库1的drop index语句是ddl，后面会阻塞相应sql，等待类型为LCK_M_SCH_S。由于同步进程被阻塞，主从出现延时
• kill掉慢查询后，从库1的drop index语句成功执行，阻塞消失，后续语句也成功执行，主从恢复同步

另外也需注意业务高峰期勿进行ddl操作，执行完注意检查是否有相关阻塞。