减少SQL Server表碎片而无需添加/删除聚簇索引? [英] Reduce SQL Server table fragmentation without adding/dropping a clustered index?

问题描述

我有一个大型数据库(90GB数据，70GB索引)，过去一年来一直在缓慢增长，而且这种增长/变化不仅导致索引内部大量分散，而且导致表本身也大量碎片化./p>

解决(大量)非常分散的索引很容易-根据它们的碎片化程度，REORGANIZE或REBUILD会解决这个问题-但是我发现有关清除实际表碎片的唯一建议是添加表的聚集索引.我之后会立即删除它，因为我不想继续在表上使用聚簇索引，但是还有另一种方法可以在没有聚簇索引的情况下执行此操作?会执行此操作的"DBCC"命令吗?

感谢您的帮助.

解决方案

问题

我们要弄清楚一点，因为这是一个常见问题，对于每个使用SQL Server的公司来说都是一个严重的问题.

这个问题以及对CREATE CLUSTERED INDEX的需求都被误解了.

同意拥有一个永久的聚集索引比没有索引更好.但这不是重点，无论如何，这将导致冗长的讨论，因此我们将其搁置一旁，集中讨论已发布的问题.

重点是，您在堆上有很多零碎的内容.您一直称其为表"，但是在物理数据存储或DataStructure级别上没有这样的东西.表是一个逻辑概念，而不是物理概念.它是物理DataStructures的集合.收集是以下两种可能性之一:

• 堆
  加上所有非聚集索引
  加上文字/图片链

• 或聚集索引
  (消除了堆和 one 非聚集索引)
  加上所有非聚集索引
  加上文字/图片链.

堆变得非常分散；插入/删除/更新越散在(随机)，碎片越多.

无法按原样清理堆. MS不提供工具(其他供应商提供).

解决方案

但是，我们知道创建聚簇索引会完全重写并重新排列堆.因此，该方法(绝非技巧)是创建聚簇索引 ，仅用于将堆碎片化 ，然后将其删除.您需要在table_size x 1.25的数据库中有可用空间.

在使用时，一定要使用FILLFACTOR来减少未来的碎片.然后，堆将占用更多的已分配空间，以允许将来由于更新而进行的插入，删除和行扩展.

注意

1. 请注意，有三个 Levels 级的碎片；这仅处理III级，堆中的碎片，这是由缺乏聚集索引

引起的

2. 作为一项单独的任务，在其他时候，您可能希望考虑实施永久性聚簇索引，该聚簇索引可以完全消除碎片……但这与发布的问题是分开的.

回复评论

SqlRyan:
虽然这并不能给我解决问题的灵丹妙药，但很显然我的问题是SQL Server的限制所致，并且添加聚簇索引是对堆进行碎片整理"的唯一方法.

不完全是.我不会称其为局限性".

1. 我给出的消除堆中碎片的方法是创建一个聚簇索引，然后将其删除.即.暂时，唯一的目的就是纠正碎片.

2. (永久地)在表上实现聚簇索引是一种更好的解决方案，因为它减少了 overall 碎片(DataStructure仍然可以碎片化，请参见下面链接中的详细信息)，这远小于堆中发生的碎片.

   • 关系数据库中的每个表(管道"或队列"表除外)都应具有聚集索引，以便利用其各种好处.

   • 聚集索引应该位于分布数据的列上(避免INSERT冲突)，决不能在单调递增的列上建立索引，例如Record ID ¹ ，这样可以保证INSERT Hot在最后一页找到.

^{1.每个文件上的记录ID都会使您的数据库"成为非关系记录归档系统，仅出于方便起见，使用SQL.此类文件没有关系数据库的完整性，功能或速度.}

安德鲁·希尔(Andrew Hill):
您能否进一步评论请注意，存在三个级别的碎片；这仅涉及级别III" –其他两个级别的碎片是什么?

在MS SQL和Sybase ASE中，有三个 Levels 碎片，在每个Level中，有几个不同的 Types .请记住，在处理碎片时，我们必须专注于DataStructures，而不是表(表是DataStructures的集合，如上所述).级别是:

• I级•Extra-DataStructure
  在相关的DataStructure外部，跨数据库或数据库内部.

• II级•数据结构
  在有关的DataStructure中，页面上方(所有页面)
  这是DBA最常处理的级别.

• III级•页面
  在相关的数据结构中，在页面中

这些链接提供了有关碎片的完整详细信息.它们特定于Sybase ASE，但是在结构级别上，该信息适用于MS SQL.

• 碎片定义

• 碎片影响

• 碎片类型

请注意，我给出的方法是II级，它可以纠正II级和III级碎片.

I have a large database (90GB data, 70GB indexes) that's been slowly growing for the past year, and the growth/changes has caused a large amount of internal fragmentation not only of the indexes, but of the tables themselves.

It's easy to resolve the (large number of) very fragmented indexes - a REORGANIZE or REBUILD will take care of that, depending on how fragmented they are - but the only advice I can find on cleaning up actual table fragmentation is to add a clustered index to the table. I'd immediately drop it afterwards, as I don't want a clustered index on the table going forward, but is there another method of doing this without the clustered index? A "DBCC" command that will do this?

Thanks for your help.

解决方案

Problem

Let's get some clarity, because this is a common problem, a serious issue for every company using SQL Server.

This problem, and the need for CREATE CLUSTERED INDEX, is misunderstood.

Agreed that having a permanent Clustered Index is better than not having one. But that is not the point, and it will lead into a long discussion anyway, so let's set that aside and focus on the posted question.

The point is, you have substantial fragmentation on the Heap. You keep calling it a "table", but there is no such thing at the physical data storage or DataStructure level. A table is a logical concept, not a physical one. It is a collection of physical DataStructures. The collection is one of two possibilities:

• Heap
  plus all Non-clustered Indices
  plus Text/Image chains

• or a Clustered Index
  (eliminates the Heap and one Non-clustered Index)
  plus all Non-clustered Indices
  plus Text/Image chains.

Heaps get badly fragmented; the more interspersed (random)Insert/Deletes/Updates there are, the more fragmentation.

There is no way to clean up the Heap, as is. MS does not provide a facility (other vendors do).

Solution

However, we know that Create Clustered Index rewrites and re-orders the Heap, completely. The method (not a trick), therefore, is to Create Clustered Index only for the purpose of de-fragmenting the Heap, and drop it afterward. You need free space in the db of table_size x 1.25.

While you are at it, by all means, use FILLFACTOR, to reduce future fragmentation. The Heap will then take more allocated space, allowing for future Inserts, Deletes and row expansions due to Updates.

Note

1. Note that there are three Levels of Fragmentation; this deals with Level III only, fragmentation within the Heap, which is caused by Lack of a Clustered Index

2. As a separate task, at some other time, you may wish to contemplate the implementation of a permanent Clustered Index, which eliminates fragmentation altogether ... but that is separate to the posted problem.

Response to Comment

SqlRyan:
While this doesn't give me a magic solution to my problem, it makes pretty clear that my problem is a result of a SQL Server limitation and adding a clustered index is the only way to "defragment" the heap.

Not quite. I wouldn't call it a "limitation".

1. The method I have given to eliminate the Fragmentation in the Heap is to create a Clustered Index, and then drop it. Ie. temporarily, the only purpose of which is correct the Fragmentation.

2. Implementing a Clustered Index on the table (permanently) is a much better solution, because it reduces overall Fragmentation (the DataStructure can still get Fragmented, refer detailed info in links below), which is far less than the Fragmentation that occurs in a Heap.

   • Every table in a Relational database (except "pipe" or "queue" tables) should have a Clustered Index, in order to take advantage of its various benefits.

   • The Clustered Index should be on columns that distribute the data (avoiding INSERT conflicts), never be indexed on a monotonically increasing column, such as Record ID ¹, which guarantees an INSERT Hot Spot in the last Page.

^{1. Record IDs on every File renders your "database" a non-relational Record Filing System, using SQL merely for convenience. Such Files have none of the Integrity, Power, or Speed of Relational databases.}

Andrew Hill:
would you be able to comment further on "Note that there are three Levels of Fragmentation; this deals with Level III only" -- what are the other two levels of fragmentation?

In MS SQL and Sybase ASE, there are three Levels of Fragmentation, and within each Level, several different Types. Keep in mind that when dealing with Fragmentation, we must focus on DataStructures, not on tables (a table is a collection of DataStructures, as explained above). The Levels are:

• Level I • Extra-DataStructure
  Outside the DataStructure concerned, across or within the database.

• Level II • DataStructure
  Within the DataStructure concerned, above Pages (across all Pages)
  This is the Level most frequently addressed by DBAs.

• Level III • Page
  Within the DataStructure concerned, within the Pages

These links provide full detail re Fragmentation. They are specific to Sybase ASE, however, at the structural level, the information applies to MS SQL.

• Fragmentation Definition

• Fragmentation Impact

• Fragmentation Type

Note that the method I have given is Level II, it corrects the Level II and III Fragmentation.

这篇关于减少SQL Server表碎片而无需添加/删除聚簇索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！