在不添加/删除聚集索引的情况下减少SQL Server表碎片?

问题

让我们弄清楚一点,因为这是一个常见的问题,对于每个使用SQLServer的公司来说都是一个严重的问题。

这个问题以及创建聚集索引的必要性被误解了。

一致认为有一个永久聚集索引比没有更好。但这不是重点,它将导致一个漫长的讨论无论如何,所以让我们把它放在一边,并集中在张贴的问题。

堆 . 您一直称它为“表”,但在物理数据存储或数据结构级别上没有这样的东西。表是一个逻辑概念,而不是物理概念。它是物理数据结构的集合。该系列是两种可能性之一:

• 堆
  
  加上文本/图像链

• 或者 聚集索引
  (消除堆和 一
  加上所有非聚集索引
  加上文本/图像链。

堆变得非常零碎;插入/删除/更新的间隔(随机)越多,碎片就越多。

没有办法清理堆,因为是这样。MS不提供设施(其他供应商提供)。

只是为了消除堆的碎片 ,然后放下。您需要表1.25大小的db中的可用空间。

当你这样做的时候,一定要用FILLFACTOR,来减少 未来 碎片。然后,堆将占用更多分配的空间,允许将来由于更新而进行插入、删除和行扩展。

注意

1. 注意有三个 水平 碎片化;这只处理第三级,堆内的碎片,这是由 缺少聚集索引

2. 作为一项单独的任务,在其他时间,您可能希望考虑实现一个永久聚集索引,它可以完全消除碎片。。。但这与问题无关。

SqlRyan公司:

1. 我给出的消除堆中碎片的方法是创建一个聚集索引, 暂时的,唯一的目的是纠正碎片。

2. 总体的 碎片(数据结构仍然可以碎片化,请参阅下面链接中的详细信息),这远远小于堆中发生的碎片。

   • 关系数据库中的每一个表(除了“管道”或“队列”表)都应该有一个聚集索引,以便利用它的各种好处。

   • 聚集索引应该在分布数据的列上(避免插入冲突),而不能在单调递增的列上建立索引,例如记录ID ¹ ,保证在最后一页插入热点。

^{1每个文件上的记录id都会使“数据库”成为一个非关系型的记录归档系统,使用SQL只是为了方便。这样的文件没有关系数据库的完整性、功能或速度。}

安德鲁·希尔:
你是否可以进一步评论“注意,有三个层次的碎片;这只涉及第三级“其他两级的分裂是什么?

在mssql和sybasease中,有三种 在每一个层面上,有几个不同的 . 请记住,在处理碎片时,我们必须关注数据结构,而不是表(表是数据结构的集合,如上所述)。级别为:

• 
  在相关数据结构之外,跨数据库或在数据库内。

• 二级数据结构
  在相关的数据结构中,上述页面(跨所有页面)
  

• 三级页面
  

这些链接提供了完整的详细信息。它们特定于SybaseASE,但是,在结构级别上,这些信息适用于MS SQL。

• Fragmentation Definition

• Fragmentation Impact

• Fragmentation Type

您声明添加聚集索引以减轻表碎片,然后立即删除它。

聚集索引通过对集群键进行排序来删除碎片,但是您说这个键不可能在将来使用。这就引出了一个问题:为什么要使用这个密钥进行碎片整理?

FILLFACTOR 是否超过默认值?根据数据更改模式,您可以从低至80%的数据中获益。然后,每页有20%的“未使用”空间,但是当集群键值更改时,较低的页分割会带来好处。

这对你有帮助吗?

你可以或许 通过运行 DBCC SHRINKFILE with NOTRUNCATE.

根据评论,我看你还没有测试过永久聚集索引。

从这个角度来看,我们有一个每天有1000万新行的数据库,所有表上都有聚集索引。已删除的“空白”将通过预定的ALTER索引(以及前向指针/页拆分)删除。

索引后,12GB表可能是2GB:它只分配了12GB,但也有大量碎片。

我理解你被传统设计束缚的痛苦。

在我的一个遗留应用程序中,所有数据都是通过视图访问的。我可以通过添加标识列和聚集索引来修改基础表的模式,而不会影响应用程序。

拥有堆的另一个缺点是与任何向前的行关联的额外IO。

This article is by Microsoft

问题是,没有人谈论的是碎片的数据或日志设备文件在硬盘驱动器本身!!每个人都在谈论索引的碎片化以及如何避免/限制这种碎片化。

仅供参考:当您创建一个数据库时,您需要指定.MDF的初始大小以及它需要增长时的增长量。对.LDF文件执行相同的操作。无法保证当这两个文件增长时,为所需的额外磁盘空间分配的磁盘空间将在物理上与分配的现有磁盘空间相邻!!

每当这两个设备文件中的一个需要扩展时,硬盘空间就有可能出现碎片。这意味着硬盘上的磁头需要更加努力(并且需要更多的时间)才能从硬盘的一个部分移动到另一个部分以访问数据库中的必要数据。这就好比买了一小块地,盖了一栋正好适合那块地的房子。当你需要扩建房子的时候,你就没有更多的土地了,除非你买了隔壁的空地——除了——如果与此同时,其他人已经买了那块地并在上面盖了房子呢?那你就不能扩大你的房子了。唯一的可能就是在“小区”再买一块地,在上面再盖一栋房子。问题变成了——你和你的两个孩子将住在房子A,你的妻子和第三个孩子将住在房子B。那将是一种痛苦(只要你还结婚)。