Reduce SQL Server table fragmentation without adding/dropping a clustered index?

Question

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.

Answer 1

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.