主页 / dba / 问题 / 138878
Accepted
tuxmania
Asked: 2016-05-19 04:25:24 +0800 CST

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

  • 772

我最近发现一个堆表有超过 70% 的碎片。所以我决定做一个

ALTER TABLE dbo.myTable REBUILD

有趣的是，之后我有 20% 的碎片化。从那以后，那张桌子上就再也没有写过字了。所以我决定再做一次重建。

在第二次之后，表帽 50% 的碎片变得如此之多！ 我真的不明白这怎么会发生......

sql-server fragmentation

1 个回答

  1. Best Answer

    堆中的碎片是什么意思

    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：请不要在生产系统上使用未记录的命令。这只是为了演示。

    • 21

相关问题