Sql Server 阻塞的常见原因和解决办法
阻塞的常见原因和解决办法:
1. 由于语句运行时间太长而导致的阻塞,语句本身在正常运行中,只须等待某些系统资源
解决办法:
a. 语句本身有没有可优化的空间
b. Sql Server 整体性能如何,是不是有资源瓶颈影响了语句执行速度,如 内存、硬盘 和 CPU 等
2. 由于一个未按预期提交的事务导致的阻塞
这一类阻塞的特征,就是问题连接早就进入了空闲状态(sysprocesses.status='sleeping'和sysprocesses.cms='awaiting command'),但是,如果检查 sysprocesses.open_tran,就会发现它不为0,以及事务没有提交。这类问题很多都是因为应用端遇到了一个执行超时,或者其他原因,当时执行的语句倍提前终止了,但是连接还保留着。应用没有跟随发来的事务提交或回滚指令,导致一个事务被遗留在 Sql Server 里。
解决办法:
应用程序本身必须意识到任何语句都有可能遇到意外终止的情况,做好错误处理工作。这些工作包括:
· 在做 Sql Server 调用的时候,须加上错误捕捉和处理语句:If @@Trancount>0 RollBack Tran;(在程序中设置If @@Error<>0 Rollback Tran; 并不总是能执行到该语句)
· 设置连接属性"Set XACT_ABORT ON"。如果没有办法很规范应用程序的错误扑捉和处理语句,一个最快的方法就是在每个连接建立以后,或是容易出问题的存储过程开头,运行 "Set XACT_ABORT ON"
·考虑是否要关闭连接池。发一句 sp_reset_connection 命令清理当前连接上次遗留下来的所有对象,包括回滚未提交的事务。
3. 由于客户端没有及时把结果集取出而导致的语句长时间运行
语句在 Sql Server 内执行总时间不仅包含 Sql Server 的执行时间,还包含把结果集发给客户端的时间。如果结果集比较大,Sql Server 会分几次打包发出,没发一次,都要等待客户端的确认。只有确认以后,Sql Server 才会发送下一个结果集包。所有结果都发完以后,Sql Server才认为语句执行完毕,释放执行申请的资源(包括锁资源)。如果出于某种原因,客户端应用处理结果非常缓慢甚至没有响应,或者干脆不理睬 Sql Server 发送结果集的请求,则 Sql Server 会耐心的等待,银次会导致语句长时间执行而产生阻塞。
解决办法:
a. 慎重返回大结果集
b. 如果a短期内不能实现,则尝试大结果集的连接使用 Read Uncommitted 事务隔离级别,这样查询就不会申请 S 锁了
4. 阻塞的源头一直处于 RollBack 状态
这种情况是由第一类情况衍生来的。有时候发现一个连接阻塞住了别人,为了解决问题,直接让连接主动退出或强制退出(直接 Kill 连接)。对于大部分情况,这些措施会消除阻塞。但是也有例外。在连接退出的时间,为了维护数据库事务的一致性, Sql Server都会对连接还没有来得及完成提交的事务做回滚动作。Sql Server要找到所有当前事务修改过的记录,把它们改回原来的状态。所以,如果一个 Delete、Insert 或 Update 运行了1个小时,可能回滚也需要一个小时。
有些用户可能等不及,直接重启 Sql Server。当 Sql Server 关闭的时候,回滚动作会被中断,Sql Server 会被很快关掉。但是这个回滚动作在下次 Sql Server 重启的时候会重新开始。重启的时候如果回滚不能很快结束,整个数据库都会不可用。
解决办法:
最好的方法是在工作时间尽量不要做这种大的修改操作。这些操作要尽量安排在半夜或周末的时间完成。如果操作已经进行了很久,最好耐心等它做完。如果一定要在有工作负荷的时候做,最好把一个大操作分成若干小操作分布完成