数据库自动收缩造成的阻塞

背景

今天上午11点时客户打电话过来说医院的CIS系统一直有阻塞，导致系统卡慢严重，信息中心的电话都快被打爆了。赶紧要了远程登录到SQL专家云，看到了系统确实存在大量的阻塞。

点击紫色圆点进入活动会话原始数据，可以看到会话标识33是阻塞的源头，造成了大量的语句被阻塞，而且阻塞已经持续了很长的时间。

 

 分析定位

在SQL Server管理工具里查询会话标识为33的语句为自动收缩的命令，进度为79%。

 

 从SQL专家云体检项中可以看到该数据库自动收缩配置为开启状态。

   通过和医院工程师交流得知，昨天下午三点半有做过数据迁移的操作，删除了100多G的数据。

 

 结论

昨天下午三点半的时候删除了大量数据，触发了自动收缩数据库的任务。自动收缩的过程要对数据库的表进行数据整理，消耗巨大，执行的很慢，到今天上午11点的时候恰好整理到业务使用频繁的表，因此造成了阻塞，影响了业务的使用。

关于自动收缩触发条件

在数据库开启自动收缩选项的情况下，SQL Server每隔半小时会检查文件使用情况。如果空闲空间大于25%，SQL Server就会启动自动收缩。微软官方链接：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017&viewFallbackFrom=sql-server-2014。

带来的危害

对于一个磁盘空间很紧张的系统，收缩数据库无疑是有帮助的。但是从数据库自身的健康和性能考虑，并不建议使用，因为：

1、 数据文件收缩导致了索引的完全碎片化，索引的效率大大降低，严重影响性能；

2、 数据文件的收缩同样产生了大量的I/O操作，耗费大量的CPU，系统性能下降严重；

3、 在业务高峰期的时候可能会造成大量的阻塞。

英文资料：

https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

中文资料：

https://www.cnblogs.com/kerrycode/archive/2013/06/04/3116339.html

如何解决

自动收缩任务无法手动结束，只能等待收缩完成或者重启SQL Server服务。

最佳实践

1. 一定不要开启自动收缩选项；

2. 不到万不得已，不要收缩数据文件，对性能影响极大；

3. 需要做收缩的时候，一定要手工来做，而且是在维护窗口期间，尽量一次不要收缩太多空间，分几次收缩；

4. 收缩完成后要重建或者重新组织索引；

5. 同实例下的测试库也不要开启此选项，因为测试库删除数据操作比较多，运行自动收缩带来的I/O性能下降也会影响到业务库的性能。

 

北京格瑞趋势科技有限公司是聚焦于数据服务的高新技术企业，成立于2008年，创始团队及核心技术人员来自微软和雅虎。微软数据平台金牌合作伙伴，卫宁健康数据平台战略合作伙伴。通过产品+服务双轮驱动的业务模式，14年间累计服务4000+客户，覆盖互联网、市政、交通、电信、医疗、教育、电力、制造业等各个领域。