資料庫自動收縮造成的阻塞

格瑞趨勢技術團隊發表於2023-03-09
資料庫

背景

今天上午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+客戶，覆蓋網際網路、市政、交通、電信、醫療、教育、電力、製造業等各個領域。

 

 

 

 

 

 

 

相關文章