如何降低 SQL Server 中的 HEAP 碎片？

May 8, 2018

我最近發現一個堆表有超過 70% 的碎片。所以我決定做一個
ALTER TABLE dbo.myTable REBUILD
有趣的是，之後我有 20% 的碎片化。從那以後，那張桌子上就再也沒有寫過字了。所以我決定再做一次重建。
在第二次之後，表帽 50% 的碎片變得如此之多**！** 我真的不明白這怎麼會發生……

堆中的碎片是什麼意思
avg_fragmentation_in_percent通過查詢DMV從列中獲得的堆中的碎片值sys.dm_db_index_physical_stats表明
索引的邏輯碎片，或IN_ROW_DATA 分配單元中堆的擴展碎片。
此外，相同的 BOL 說
這是堆的葉頁中無序擴展區的百分比。無序擴展區是指包含堆的目前頁的擴展區在物理上不是包含前一頁的擴展區之後的下一個擴展區。
所以你可以看到不是分配給堆的頁面中存在的可用空間，而是創建碎片的不同頁面序列。
這可以通過小測試來證明。讓我們創建一個堆表並在其中插入一些記錄，然後檢查碎片。
create table dbo.HeapTest
(
Id INT not NULL Default (1),
Col1   char(5000) Not null Default ('Heaps Are Cool')
)

SET NOCOUNT ON

Insert into dbo.Heaptest default values
go 50

select index_type_desc,avg_fragmentation_in_percent,fragment_count,
avg_page_space_used_in_percent,record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.HeapTest','U'),0,default,'detailed')
因此，創建了堆表，其中包含 50 條記錄。以下是查詢 DMV sys.dm_db_index_physical stats 後碎片的樣子
您可以看到avg_fragmentation_in_percent列值為 33 %。現在讓我們看看頁面是如何排列的。這可以通過使用未記錄的查詢來完成%%lockres%%。查詢將是
SELECT  %%lockres%%, * FROM dbo.HeapTest;
下面是輸出的樣子。只附上它的相關部分。由於我們在 dbo.HeapTest 表中插入了 50 行，因此查詢產生了 50 行。
它說的是第一頁有 ID197下一頁有 ID242後續頁面有連續的 ID 直到我們達到 page ID264因為之後我們得到 page ID 280。因此，頁面 ID 號的這種跳躍實際上是導致碎片的原因。
現在免得重建堆並再次執行該命令以查看碎片以及頁面是如何排列的。我們得到像
現在可以看到碎片化了14%。
讓我們看看分配的頁碼
我們只有一個跳轉休息區，所有頁面都按順序分配頁面 ID。由於僅僅一跳，碎片就大大減少了。
我再次重建堆，現在當我檢查碎片時它完全消失了。而頁面ID分配就像
為什麼碎片化增加
現在關於可能導致碎片上升的原因，我們可以證實當頁面被分配到堆時它們不會是連續的，正如您在上面看到的那樣，導致碎片值增加的原因是分配給頁面的 PAGE ID 跳轉。
在腦後你還應該記住，HEAP 的碎片這個詞沒有任何意義，你將如何定義一堆無序頁面的碎片。
真的很擔心碎片化
如果您真的遇到堆表碎片化並且查詢速度變慢的情況，那麼在表上創建聚集索引會比重建它更好。原因是當你重建堆時，所有底層的非聚集索引也被重建，導致重建過程花費更長的時間，利用大量資源和膨脹的事務日誌。在生產系統上，人們總是會盡量避免這種情況。Paul 在他關於堆的神話部分中對此進行了介紹。
**PS：**請不要在生產系統上使用未記錄的命令。這只是為了展示。