Delete heap table後的空間重用及釋放問題

Scenario

SQL PASS的好友Alexis有天談到：

delete 一個table的資料，這個table是一個heap table，但資料刪完後，table所佔用的閒置空間並沒有釋放出來還給database。
無巧不巧，另一位好友JasonChen也遇到相同的問題，如下圖，table筆數已經為0，但卻仍佔用10GB的空間

要說明這個問題，首先要瞭解一下何謂閒置空間

(1)    空的Page，意即page中的資料已經完全被標示為刪除，沒有資料了。

(2)    半空的page，page中的資料並沒填滿，尚有空間可以容納新的資料進來。

首先確立一個觀念，SQL SERVER空間管理是以page為單位，要將table的閒置空間還給DB，必需是該table所屬的「空page」。

所以在delete過程中，必需是該頁面的資料被刪除到一筆不剩，這個page才能釋出還給DB。

空的page

做個測試，重現第1種「空的page」的情況

--建立一個table，讓每筆資料會佔用一個page，方便評估

create table testSpace(c1 int identity,c2 char(8000)) 

go

--新增1000筆資料，估計會佔用1000個page

insert into testSpace values(N'打破沙鍋問到底.....') 

go 1000

檢查table的page count，

SELECT object_name(object_id) as obj_name, index_depth AS Depth,index_type_desc

, index_level AS [level]

, record_count AS 'RowCount'

, page_count AS 'PageCount'

FROM sys.dm_db_index_physical_stats

(DB_ID ()

, OBJECT_ID ('testSpace')

, 0, NULL, 'DETAILED');

從上圖可見，確認有1000筆資料佔用了1000個page

接著使用一般的delete指令刪除table 500筆

begin tran 

--刪除500筆資料，測試空間是否會歸還給DB

delete top(500) from testSpace

--查詢lock統計

select resource_type,request_mode,request_status,count(*) as LockCount

 from sys.dm_tran_locks

 where request_session_id=72

group by resource_type,request_mode,request_status

commit

可以看到，SQL SERVER使用row-level lock在被刪除資料行獲取x-lock。

再次檢查table page count，可以發現筆數已經減少，但所佔用的page，仍然不變，由於是一筆資料會佔用一個page，這也就是說有500個空的page，而這些空page其實沒有還給database，仍然由該table佔用。

我們再刪500筆，把table整個清空

delete top(500) from testSpace

再檢查page count，可以看到table已經沒有任何資料，但空間仍然佔用1000 page

從DB的報表來看，也是一樣

以上測試，我們重現了第1種的閒置空間情境，table已沒有資料，但所佔用的空間仍然不變。

How

~~那麼，有什麼方法可以在刪除後將空page釋出還給DB呢？~~

根據微軟的說法，有下列3種方式，可以在刪除heap table資料後所剩餘的空間釋放出來。

節錄微軟官網描述

當資料列從堆積中刪除時， Database Engine 可能會在作業時使用資料列或頁面鎖定。 如此一來，由刪除作業清空的頁面仍然會配置給堆積。 如果未取消空白頁面的配置，資料庫中的其他物件就無法重複使用相關聯的空間。

若要刪除堆積中的資料列及取消配置頁面，請使用下列其中一個方法。

·         在 DELETE 陳述式中指定 TABLOCK 提示。 使用 TABLOCK 提示會造成刪除作業對資料表進行獨佔鎖定，而非資料列或頁面鎖定。如此可允許取消配置頁面。 如需 TABLOCK 的詳細資訊，請參閱資料表提示 (Transact-SQL)。

·         如果要從資料表刪除所有資料列，請使用 TRUNCATE TABLE。

·         請先在堆積上建立叢集索引之後，再刪除資料列。 您可以在刪除資料列之後卸除叢集索引。 這個方法會比之前的方法耗用更多的時間，而且會使用更多的暫存資源。

微軟所說的第二點沒什麼好討論的。至於第三點或者用alter table .. rebuild主要是將table重建然後刪除舊有的page，這也沒什麼問題。

有趣的是第一點，delete .. with(tablock)就會在刪除後立即把空間還給database。

我們再來測試一下

drop table testSpace

go

--建立一個table，讓每筆資料會佔用一個page，方便評估

create table testSpace(c1 int identity,c2 char(8000)) 

go

--新增1000筆資料，估計會佔用1000個page

insert into testSpace values(N'打破沙鍋問到底.....') 

go 1000

接著我們使用with(tablock)來刪除資料

begin tran 

--刪除500筆資料，使用tablock，測試空間是否會歸還給DB

delete top(500) from testSpace with(tablock)

--查詢lock統計

select resource_type,request_mode,request_status,count(*) as LockCount

 from sys.dm_tran_locks where request_session_id=72

group by resource_type,request_mode,request_status

commit

table上有加x-lock

檢查空間，可以看到空間立即被釋放，原本1000 pages只剩下500 pages

果然加上with(tablock)後，刪除後空間(空的page)確實立即被釋放出來。

~~~~~但，為什麼？~~~~

~~~~~with(tablock)難道有什麼特殊的作用？~~~~

Why

page都已經清空了，系統為什麼不預設自己將空間釋出，還要使用者自己加with(tablock)呢？

我們知道每個配置單元（allocation_unit）會有一個IAM page，IAM中紀錄了這個配置單元所使用到的頁面。

heap table中的資料頁是由IAM page所控制。例如, 當SQL Server 讀取第xxx頁時, 它不知道下一頁是什麼或在何處。因此, heap table的DML執行前，首先要讀取該物件的 IAM頁(會在IAM頁要求share lock),以確定它必須讀取哪些頁面才能進行完整資料表掃描。有了這些資訊, 才可以啟動查詢, 並且可以讀取每個資料頁。這個過程稱為「Allocation Scan」。

假設有一個heap table，在IAM紀錄中有5個頁面

(1)交易T1正在delete資料(非 tablock)，它首先讀取IAM頁成功，接著準備往下執行，我們再假設這交易會delete清空第3頁

(2)交易T2在交易T1讀取完IAM尚未開始delete時啟動，它是select 所有資料

上圖所示的這情況，如果T1在delete後立即將閒置的頁面還給DB, 那麼T2在查詢到第3頁的時候，就會遇到「找不到資料頁面」的情況。這是一個嚴重的錯誤。所以 SQL SERVER為了資料的一致性，必需保留該頁面的配置。

因此，一般非tablock的delete在執行完後，即使有頁面被清空，它也不會釋放給DB以供重新配置。

這些閒置空間，仍然屬於該table，如果後續有對這個table的insert，那麼這些閒置空間仍然會被新增的資料所使用。

那麼為什麼加上with(tablock)就會將空間釋放呢？

其實很簡單，那是因為delete..with(tablock), 它會在目標table上要求x-lock，而這x-lock會延伸到該table的IAM頁。

所以這時IAM頁是被鎖定隔離的，其它程序無法讀取IAM，也就可以避開上面所講的「找不到資料頁面」的情況。

因此在tablock的情況下，delete後如果有產生閒置的頁面，系統就會將這些頁面還給DB。

鎖定升級

在同一SQL語句單一instance的單一物件上獲取超過5000個row/page lock，將達到鎖定升級的閥值（5000只是概略數字）。

或許有人有疑問，有時候delete並沒有加with(tablock)，也會將閒置頁面釋出啊！

那是因為「鎖定升級」的關係，系統將原本的row/page lock自動升級到tablock，所以閒置空間會被釋放。

不過delete的鎖定升級，並不是一開始就評估好要升級，而是隨著delete的過程，row/page lock的用量越來越多，超過閥值後，會觸發鎖定升級。

測試

drop table testSpace

go

--建立一個table，讓每筆資料會佔用一個page，方便評估

create table testSpace(c1 int identity,c2 char(8000)) 

go

--新增20000筆資料，估計會佔用20000個page

insert into testSpace values(N'打破沙鍋問到底.....') 

go 20000

刪除資料，並檢視lock統計

begin tran 

--刪除10000筆資料

delete from testSpace where c1<10001

--查詢lock統計

select resource_type,request_mode,request_status,count(*) as LockCount

 from sys.dm_tran_locks

 where request_session_id=54

group by resource_type,request_mode,request_status

commit

檢查table空間

筆數已經被刪成10000筆，page數應該也會是10000但卻有13124

。這是因為剛delete開始時系統是使用row-lock，直到系統判斷lock數太多，而啟用鎖定升級為table x-lock。而自獲得table x-lock後所刪除的資料空間(空page)才會被釋出還給DB。

delete..with(tablock)所能釋放的空間，僅限於它獲得x-lock的期間所清出的空間。如果你在之前有用一般的delete去刪資料，後面才用delete..with(tablock)，那麼這些之前所釋出的空間是不會被放掉的。

Conclusion

Heap table delete時，閒置的空間可以分成下列兩類

(1)    空的page

(2)    半空的page

一個heap table如果你想要在delete時一併釋放空間，那麼你必需加上with(tablock)。注意delete指令只會將這次delete過程中所清空的page釋出還給DB。之前的空page不會被這次delete所釋放。

半空的page，由於page中仍有資料，雖然不能釋出給DB，但是可以被本身的資料重用。如果你想把這些半空的page或者上述全空而沒有釋放的page空間釋放出來，可以使用alter table..rebuild空間重整後剩餘空的page會釋出還給DB。或者對此table建立clustered index再刪除clustered index也行。

至於DB的可用空間，則是要經由shrink才能釋出給OS。

註：

Clustered table由於它的搜尋並不是走IAM，而是依靠page header中的nextpage及prevpage，所以即使不加with(tablock), 也會將刪除過程中所清空的page釋出還給DB。