[SQL SERVER][Memo]邏輯碎片這檔事(2)

2013-01-03

2577
0
SQL_SERVER

上一篇測試Heap資料表碎片及相關注意事項，

這一篇來測試NonHeap資料表幾個常見操作所產生的碎片程度。

1.先建索引後增資料和先增資料後建索引那種方式產生的碎片程度小

先增資料後建索引較小。

先建索引後增資料不僅會影響資料新增效能，而且也會產生較多碎片，

而當資料表都沒有任何索引時，先處理完新增資料部份後，

在執行建立索引作業時，這時會整理相關碎片。

--先建索引後增資料--


create clustered index cix_1 
on nonheap(c1)

create index ix_2
on nonheap(c2)

create index ix_3
on nonheap(c3)
include(c4)


declare @step int
set @step=1
while(@step<=30000)
begin
insert into nonheap(c1,c2,c3,c4) 
select @step,'rico'+cast(@step as varchar(5)),getdate(),1
set @step=@step+1 
end

查看碎片程度

--先增資料後建索引--

2.重建資料表會不會連動重建 Clustered Index & Nonclustered Index

不一定。

SQL2008中執行 Alter Table Rebuild 只會重建 Clustered Index，

但如果為Heap資料表時，就會重建相關所有Nonclustered Index。

原本碎片程度


alter table nonheap rebuild

NonHeap資料表只重建cix_1(Clustered Index)。

3.異動 Clustered Key 會不會連動影響 Nonclustered Index 碎片

是。

因為所有Nonclustered Index都包含Nonclustered key和資料定位器，

如果該資料表有Clustered Index，那麼資料定位器就會指向Clustered key，

如果沒有的話，則指向資料列指標。

原本碎片程度


--異動Clustered Key產生碎片
delete nonheap where c1>21000 and c1<25000
delete nonheap where c1>1000 and c1<15941

4.異動 Nonclustered Key 會不會連動影響 Clustered Index 碎片

是。

主要原因大致上同第3點。


--異動NnoClustered Key產生碎片



delete nonheap
where c2 in(select top(5000) c2 from nonheap) 
delete top(1000) from nonheap
where c3 >='20130103 00:00:00' and c3<'20130103 23:59:59'
and c4=1