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。
