[SQL SERVER][Memo]邏輯碎片這檔事(1)
大家都知道，在硬碟上執行刪除、移動檔案..等操作，會為硬碟帶來空間碎片，
因為檔案容易破碎，造成檔案寫入空間沒有連續，所以時間一久，
就會造成檔案破碎程度越來越大，進而影響資料存取效能(硬碟搜尋時間拉長)，
如果要改善資料存取效能，這時就需使用硬碟重組軟體整理這些不連續的碎片(盡量減少碎片)，
重新排列分散的資料使其連續。如下圖使用windows內建硬碟重組工具分析硬碟碎片程度。
而資料庫世界中也是一樣的，對一個資料表執行資料刪除、移動作業，也會帶來相關邏輯碎片，
我想大家都知道應該怎麼去重整索引或資料表碎片，
重整索引碎片雖可以改善查詢效能(減少邏輯I/O讀取量和硬碟空間用量)，
但索引維護作業可是會讓交易記錄檔案迅速暴增( 管理交易記錄檔(5) )，
所以一定要仔細評估何時該執行重建索引作業。
下面我主要針對兩種資料表類型(Heap & NonHeap)進行簡單碎片測試，
同時和一些需注意的事項。
Heap 資料表
create table myheap
(
c1 int identity(1,1),
c2 char(200)
)
insert myheap(c2) select 'rico'
go 30000
查看碎片程度
SELECT ps.database_id, ps.OBJECT_ID,object_name(ps.OBJECT_ID) as 'TableName',
ps.index_id, b.name as 'IndexName',
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
--AND b.name is not null
and object_name(ps.OBJECT_ID)='myheap'
2.88%邏輯碎片。
DBCC SHOWCONTIG ('myheap')
平均頁面密度高達97.32%，但30000筆資料卻使用高達 811 pages。
我大概計算一下每筆資料約使用4bytes+200bytes=204bytes，
30000筆資料約使用30000*204bytes=6120000 bytes。
使用空間
實際看一下該資料表使用6536KB。資料使用6488kb(811*8)。
執行刪除資料操作產生碎片
delete myheap where c1>21000 and c1<25000
delete myheap where c1>1000 and c1<15941
delete top(1000) from myheap
--查看碎片空間
平均碎片程度變為5.33。
頁面密度降低、碎片程度增加、掃描頁數減少。
使用空間
資料使用4576kb(572*8)。
重整Heap資料表碎片
SQL2005以前要整理Heap資料表碎片，可以使用重建資料表或重建Clustered Index來處理，
SQL2008之後，Alter Table 多了Rebuild選項可供使用，
但該選項基本上也是使用類似重建方法來處理(如下圖使用profiler擷取)，
所以該選項也是要仔細評估使用。
可以看到我執行 alter tablemyheaprebuild 後，
SQL Server會啟動單一交易並執行 insert … select …，
當然這中間SQL SERVER會把相關資料操作都記錄在交易記錄檔案中，
最後等待3002筆資料新增完畢並commit交易，
這時整各alter table rebuild作業才算完成。
Rebuild後查看資料表碎片程度
可以看到頁面密度以高達99.51%，而且相同資料筆數卻只使用266 pages。
使用空間
資料空間比之前少了2448kb。
注意:
上面我們知道，重整碎片可以帶來降低邏輯I/O讀取量和硬碟空間用量的好處，
可是也帶來負面的一些影響(交易記錄檔案成長快速..等)。
但如果你執行變更資料表結構的話，
我建議你也一併執行重建資料表作業(如果大型資料行順序不在最後)，
如果沒執行Rebuild的話，你將遇到資料列大小超過8060上限錯誤訊息，
下面我簡單測試一下。
drop table myheap
create table myheap
(
c1 int identity(1,1),
c2 nchar(4000), --大型資料行
c3 char(10)
)
insert myheap(c2,c3) select REPLICATE(N'R', 4000),'rico'
這時每一筆資料約使用(4000*2)+10+4 bytes=8014(未超過8060)。
刪除C2欄位並新增C4
alter table myheap drop column c2
alter table myheap add c4 char(50)
可以看到SQL SERVER拋出超過8060 bytes資料列大小上限錯誤。
如何避免
1.使用Rebuild
刪除C2欄位後，先執行Rebuild在新增C4欄位
alter table myheap rebuild
alter table myheap add c4 char(50)
select * from myheap
2.將大型資料行放置最後
如果不想使用Rebuild是否還有其他方法呢?
有的，但規劃資料表時就需考慮將大型資料行放在最後。
drop table myheap
create table myheap
(
c1 int identity(1,1),
c3 char(10),
c2 nchar(4000),
)
這次我將C2欄位順序移到該資料表最後
insert myheap(c2,c3) select REPLICATE(N'R', 4000),'rico'
再次執行刪除C2欄位並新增C4
alter table myheap drop column c2
alter table myheap add c4 char(50)
select * from myheap
順利執行且沒有任何錯誤。
為什麼將大型資料行移到最後就不會發生超過資料列上限錯誤?
我個人理解是這樣的，刪除欄位時，SQL Server並不會整理資料表所使用頁面配置，
而只有更新資料表metadata資訊並將該欄位所佔用資料長度刪除(可用空間)，
在還沒有移動大型資料行順序時，原本每一資料列配置示意如下
c1 int | c2( nchar(4000)) | c3(char 10)
刪除c2後，每一資料列配置示意如下
c1 int |( 8000byte 可用空間 ) | c3(char 10)
新增c4時，因為SQL Server不會先尋找該頁面可用空間，
而是採取繼續寫入的作法(這樣新增作業速度較快)，
所以就會產生資料列超過8060限制(每一資料列配置示意如下)
c1 int | ( 8000byte 可用空間 ) | c3(char 10) | c4 (char 50)--超過限制
大概知道SQL SERVER寫入的作法後，所以把大型資料行移到最後，
因此可以避開資料列超過8060限制。
原本每一資料列配置示意如下:
c1 int | c3(char(10)) | c2(nchar(4000))--大型資料行移到最後
刪除c2後，每一資料配置示意如下
c1 int | c3(char(10)) | ( 8000byte 可用空間)
新增c4時因為已經有8000byte可用空間，
所以可以繼續寫入而不超過8060byte限制，
每一資料列配置示意如下:
c1 int | c3(char(10)) | c4(char(50))
參考