程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 關於SQL數據庫表碎片問題處理

關於SQL數據庫表碎片問題處理

編輯:關於SqlServer
 

一般情況下,SQL語句執行過慢時,我們第一時間考慮的是碎片存在碎片,導致業務在執行時效率低,需要我們人為進行索引碎片的重建或者重整進行解決,但是我們很少注意到表同樣存在碎片的情況,而且同樣導致SQL語句執行效率,本文將從數據表碎片的問題進行研究。

一、 問題現象描述

在我們合作多年的一家基金公司中,有個CRM系統,該系統是微軟的產品,經過該公司的開發部門進行二次開發。之前為了實現功能,忽略了表的最佳結構設計,存在大量的堆表,今年出現了下面一些問題:

在CRM系統中的用戶數據庫中,存在兩張特大的數據庫表table1和table2;
在兩張表在從今年開始空間大小暴漲,目前總大小已接近770GB,並有410GB未使用空間,目前需要通過不斷擴容磁盤空間已解決日益增長的難題;
通過檢查表的結構,並未發現大字段(例如:text/max/xml等),與實際占空空間情況不符;另外通過數據庫à右鍵à任務à收縮,查看可用空間僅為2GB,並未看到上面所示的410GB的可用空間。

目前此問題已造成嚴重隱患,一旦需要數據庫恢復,則需要大量的備份還原時間,並且對性能同樣造成一定的影響。

二、 原因分析

首先,數據庫可用空間並未等同表的可用空間,由數據庫à右鍵à任務à收縮中所查看的是數據庫未分配的空間,而表中存在的可用空間是已分配的,所以數據庫的可用空間為2GB,而表中的可用空間則有410GB,故無法通過收縮的方式將表中的可用空間釋放。詳細信息請參考: https://msdn.microsoft.com/zh-cn/library/ms188776.aspx
針對表的空間異常暴漲問題,則是由於對表進行數據修改(INSERT、UPDATE 和 DELETE 語句)的過程中會出現表碎片現象,如果修改特別頻繁,則造成的碎片情況越嚴重;
而這兩張大表無聚集索引,在物理存儲上並未按照順序存放,這種類型的表稱為堆表,從堆中刪除行時,數據庫引擎可以使用行鎖定或頁鎖定進行操作。結果,刪除操作導致的空頁將繼續分配給堆。未釋放空頁時,數據庫中的其他對象將無法重用關聯的空間(根據與管理員的溝通中發現這兩張表的刪除和插入操作特別頻繁);
利用DBCC SHOWCONTIG進行表的碎片情況分析時,得到信息如下:
(table1表)
- 掃描頁數................................: 19524745
- 掃描區數..............................: 5657978
- 區切換次數..............................: 5657977
- 每個區的平均頁數........................: 3.5
- 掃描密度 [最佳計數:實際計數].......: 43.14% [2440594:5657978]
- 區掃描碎片 ..................: 56.89%
- 每頁的平均可用字節數.....................: 8022.9
- 平均頁密度(滿).....................: 0.88%

(table2表)
- 掃描頁數................................: 23863879
- 掃描區數..............................: 6024951
- 區切換次數..............................: 6024950
- 每個區的平均頁數........................: 4.0
- 掃描密度 [最佳計數:實際計數].......: 49.51% [2982985:6024951]
- 區掃描碎片 ..................: 50.76%
- 每頁的平均可用字節數.....................: 8013.7
- 平均頁密度(滿).....................: 0.99%
根據以上信息可得知:SQL Server的每個頁面的可用空間為8060字節,而這兩張表的可用剩余空間為8022和8013字節,造成大量的空間碎片,而導致表的空間異常的變大。

三、 解決方法

一般處理這種表碎片問題,可通過重建聚集索引已重新將數據排序,整理釋放空間,如果無聚集索引,可通過新建聚集索引後再刪除聚集索引(建議創建聚集索引後不再刪除,定期進行索引的重建即可);
由於這兩張表的空間已達到780GB,通過創建聚集索引的方式需要耗費大量的時間進行,現可通過新建同樣的新表,將原表的數據導入到新表中,這種方式效果最佳,問題處理得最徹底(通過測試,整個過程持續10分鐘左右,並且將780GB空間可收縮為2GB左右空間);
導入數據庫步驟執行完成後(確定將所有索引都已創建完成),確認新表數據正常,則可利用truncate的方式將舊表清空;
以上步驟執行完成後,可利用數據庫的收縮方式將可用空間釋放,但由於是收縮數據文件的空間,此步驟需要耗費的時間較長。

四、 類似情況解決

由於堆表的大量更改操作均會存在表碎片的情況發生,可創建相應的作業定期進行聚集索引的重建已解決碎片的情況,確保不會再次出現此類問題。

 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved