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

合集 - SQL Server数据库运维(59)

1.SQL SERVER日常运维巡检系列之-日志2024-12-30 2.关于SQL Server镜像的一个小误区2016-07-02 3.一切从“简”，解放IT运维人员2016-12-16 4.CPU问题定位与解决2017-02-07 5.数据库优化案例——————某市中心医院HIS系统2017-01-18 6.系统隐形杀手——阻塞与等待2017-01-11 7.SQL Server常见问题介绍及快速解决建议2016-12-20 8.tempdb日志文件暴增分析2022-11-09 9.数据库服务器CPU不能全部利用原因分析2022-11-07 10.如何快速定位TempDB产生问题2017-02-28 11.索引的重要性2017-02-16 12.SQL语句的优化建议2017-02-14 13.通过分析等待类型解决系统产生问题2017-02-13 14.磁盘问题定位与解决2017-02-09 15.内存问题定位与解决2017-02-08 16.写给数据库运维的兄弟2023-02-27 17.为什么带NOLOCK的查询语句还会造成阻塞2023-02-23 18.链接服务器查询导致的阻塞2023-02-21 19.CXPACKET等待类型分析2023-02-20 20.为什么sleeping的会话会造成阻塞(2)2023-02-17 21.为什么sleeping的会话会造成阻塞2023-02-14 22.为SQL Server配置连接加密2023-02-14 23.SQL SERVER——高可用技术概述2023-02-04 24.数据库服务器CPU不能全部利用原因分析2023-01-15 25.管理工具造成的阻塞2023-01-09 26.自动增长配置不合理导致的性能抖动2023-01-05 27.Profiler导致的严重性能问题2023-01-02 28.CPU持续100%分析并解决分析2022-12-26 29.tempdb大量闩锁等待问题分析2022-11-29 30.探究SQL SERVER 更改跟踪2022-11-15 31.tempdb数据文件暴增分析2022-11-10 32.SQL SERVER 2016 AlwaysOn 无域集群+负载均衡搭建与简测2024-12-16 33.Moebius兼容AlwaysOn2024-12-16 34.Moebius for SQL Server2024-12-16 35.SQL SERVER日常运维巡检系列——数据库备份2024-12-09 36.SQL SERVER日常运维巡检系列——结构设计2024-11-26 37.Moebius for SQL Server2024-11-20 38.SQL SERVER日常运维巡检系列之-数据库2024-10-09 39.【能力提升】SQL Server常见问题介绍及快速解决建议2024-06-19 40.CDC作业历史记录无法删除问题2024-03-04 41.为Oracle链接服务器使用分布式事务2023-11-21 42.大事务导致数据库恢复时间长2023-10-25 43.SQL Server关于AlwaysOn的理解-读写分离的误区(一)2023-09-14 44.链接服务器导致SQL Server停止响应2023-09-05 45.简单配置Sql专家云2023-07-31 46.SQL专家云回溯某时间段内的阻塞2023-07-04 47.SQL专家云快速解决阻塞2023-06-19 48.SQL专家云汇总低效的SQL语句2023-06-05 49.数据库优化案例—某市中心医院HIS系统2023-05-19 50.细说数据库协作运维2023-05-08 51.医院核心数据库一体化建设实践2023-04-24 52.【能力提升】SQL Server常见问题介绍及快速解决建议2023-04-17 53.Moebius数据库多活集群2023-04-10 54.30分钟带你熟练性能优化的那点儿事儿（案例说明）2023-04-03 55.SQL Server底层架构技术对比2023-03-28 56.Moebius兼容AlwaysOn2023-03-23 57.透过等待看数据库2023-03-21

58.数据库自动收缩造成的阻塞2023-03-09

59.CPU持续100%分析并解决2023-03-02

背景

今天上午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服务。

最佳实践

一定不要开启自动收缩选项；
不到万不得已，不要收缩数据文件，对性能影响极大；
需要做收缩的时候，一定要手工来做，而且是在维护窗口期间，尽量一次不要收缩太多空间，分几次收缩；
收缩完成后要重建或者重新组织索引；
同实例下的测试库也不要开启此选项，因为测试库删除数据操作比较多，运行自动收缩带来的I/O性能下降也会影响到业务库的性能。

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