主页 / dba / 问题 / 77935
Accepted
Daniel Björk
Asked: 2014-09-30 05:06:33 +0800 CST

在聚集索引上重建，为什么数据大小会缩小？

  • 772

当我们对包含大约 15gb 数据的表的聚集索引进行重建并且数据大小缩小到 5gb 时，怎么会这样？删除了什么样的“数据”？

数据大小我的意思是 DBCC sp_spaceused 的“数据”列

在聚集索引上重建之前：

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    39169656 KB 15857960 KB 22916496 KB 395200 KB

在聚集索引上重建后： 

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    29076736 KB 5867048 KB  22880144 KB 329544 KB

用于重建的 TSQL： 

USE [DAX5TEST]
GO
ALTER INDEX [I_212RECID] ON [dbo].[LEDGERJOURNALTRANS] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE, FILLFACTOR = 85 )
GO
sql-server clustered-index

3 个回答

  1. Best Answer

    当表有聚集索引时，索引就是表数据（否则你有一个堆类型的表）。聚集索引的重建（实际上是任何索引，但空间不会被视为非聚集索引的“数据”）将导致部分使用的页面合并为更完整的形式。

    当您以索引顺序将数据插入索引（集群或其他方式）时，叶页会根据需要创建，并且您将永远只有一个部分页面：最后的那个。当您输入不符合索引顺序的数据时，需要拆分页面以将数据放置在正确的位置：您最终会得到两个大约半满的页面，新行进入其中之一。随着时间的推移，这可能会发生很多，消耗相当多的额外空间，尽管在某种程度上未来的插入将填补一些空白。非叶子页面也会看到类似的效果，但实际数据页面的大小要比它们重要得多。

    删除也可能导致部分页面。如果您删除页面中的所有行，则将其计为“未使用”，但如果还剩下一行或多行数据，则仍将其计为正在使用。即使一页中只有一行使用 10 个字节，该页在已用空间计数中也算作 8192 个字节。同样，未来的插入可能会填补一些空白。

    对于可变长度的行，更新也可以产生相同的效果：随着行变小，它可能会在其页面中留下空间，以后不容易重用，如果几乎整页中的行变长，它可能会强制页面拆分.

    SQL Server 不会花时间尝试通过重新排列页面的使用方式来规范数据，除非明确告知您的索引重建顺序，因为此类垃圾收集练习可能是性能噩梦。

    我怀疑这就是您所看到的，尽管我会说为绝对需要的数据量的约 2.7 倍分配足够的空间是一个特别糟糕的情况。这可能意味着您有一些随机的东西作为索引中的重要键之一（可能是 UUID 列），这意味着新行不太可能按索引顺序添加，和/或最近发生了大量删除。

    页面拆分示例

    以索引顺序插入固定长度的行，其中四个适合页面：

    Start with one empty page: 
        [__|__|__|__]
Add the first item in index order:
        [00|__|__|__]
Add the next three
        [00|02|04|06]
Adding the next will result in a new page:
        [00|02|04|06] [08|__|__|__]
And so on...
        [00|02|04|06] [08|10|12|14] [16|18|__|__]

    现在要按索引顺序添加行（这就是我在上面只使用偶数的原因）：添加11意味着要么扩展第二页（不可能，因为它们是固定大小的），将 11 以上的所有内容上移一页（在一个大索引）或像这样拆分页面：

    [00|02|04|06] [08|10|11|__] [12|14|__|__] [16|18|__|__]

    从这里开始，添加13并且17不会导致拆分，因为相关页面中当前有空间：

    [00|02|04|06] [08|10|11|__] [12|13|14|__] [16|17|18|__]

    但添加 03 将：

    [00|02|03|__] [04|06|__|__] [08|10|11|__] [12|13|14|__] [16|17|18|__]

    如您所见，在这些插入操作之后，我们目前分配了 5 个数据页，总共可以容纳 20 行，但我们只有 14 行（“浪费”了 30% 的空间）。

    使用默认选项重建（见下文“填充因子”）将导致：

    [00|02|03|04] [06|08|10|11] [12|13|14|16] [17|18|__|__]

    在这个简单的例子中保存一页。很容易看出删除如何具有与索引乱序插入类似的效果。

    减轻

    如果您希望数据以相对于索引顺序相当随机的顺序出现，则可以FILLFACTOR在创建或重建索引时使用该选项来告诉 SQL Server 人为地留下空白以供以后填充 - 从长远来看，减少页面拆分但最初占用更多空间。当然，弄错这个值会使事情变得更糟，而不是让情况变得更好，所以要小心处理。

    页面拆分，特别是在聚集索引上，可能会对插入/更新产生性能影响，因此FILLFACTOR有时会出于这个原因进行调整，而不是看到大量写入活动的数据库中的空间使用问题（但对于大多数应用程序而言，读取超过写入几个数量级，通常最好将填充因子保持在 100%，除非在特定情况下，例如在具有有效随机内容的列上具有索引）。

    我假设其他大牌数据库也有类似的选项，如果你也需要这种级别的控制的话。

    更新

    关于ALTER INDEX在我开始输入上述内容后添加到问题中的语句：我假设选项与索引首次构建（或上次重建）时相同，但如果不是，那么压缩选项可能非常重要，如果它被添加这个时间。同样在该语句中，填充因子设置为 85% 而不是 100%，因此每个叶页在重建后将立即为约 15% 的空。

    • 16

  2. 当您重建索引时，它实际上会将所有数据放到新页面上。我怀疑发生的事情是您在重建之前删除了很多数据，例如删除了一个列，更新了一个可变宽度的列以减少数据，更改了一个固定宽度的列大小，或者删除了很多行。这些操作中的任何一个都可能在页面上留下大量空白空间，这些空间在重建之前不会被回收。中的“数据”列sp_spaceused不是衡量实际数据，而是用于存储数据的 8K 页数。由于重建，这些页面现在更满了，因此相同数量的数据适合较少数量的页面。

    • 6

  3. sp_spaceused存储过程不检查数据库中行的总累积大小。它在为数据分配的范围的累积大小中报告为保存该数据而分配的空间大小。

    如果有大量可用空间，例如来自许多已删除的行，则出于性能原因，重新构建聚集索引将压缩页面和范围中的空间以提高效率（即更小）。

    因此，不应该丢弃任何数据，但是重建过程使嵌入数据页中的空闲空间再次可用。

    • 5

相关问题