How to lower HEAP Fragmentation in SQL Server?

Question

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a

ALTER TABLE dbo.myTable REBUILD

Funny enough, afterwards i had 20% fragmentation. There was no write on that table ever since. So i decided to do the rebuild one more time.

After the 2nd time the table hat 50% fragmentation so even more! I really don't understand how this can happen...

Answer 1

What does Fragmentation Means in a Heap

The fragmentation value in Heap which you get from column avg_fragmentation_in_percent by querying sys.dm_db_index_physical_stats DMV states that

Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

Further the same BOL says that

This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

So you can see it is not the free space present in pages allocated to Heap but the varying sequence of pages that creates the fragmentation.

This can be demonstrated by small test. Let us create a Heap Table and insert some records in it and then check the fragmentation.

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')

So Heap table is created with 50 records in it. Below is what fragmentation looks like after query DMV sys.dm_db_index_physical stats

You can see avg_fragmentation_in_percent column value is 33 %. Now let us see how are pages arranged. This can be done by using undocumented query %%lockres%%. The query would be

SELECT  %%lockres%%, * FROM dbo.HeapTest;

And below is what output looks like. Attaching only relevant portion of it. The query produced 50 rows since we inserted 50 rows in our dbo.HeapTest table.

What it says is the first page has ID 197 the next page has ID 242 subsequent pages has continuous ID till we reach page ID 264 because after that we get page ID 280. So this jump in page ID numbers is what actually causing fragmentation.

Now lest rebuild the heap and run The command again to see the fragmentation and how pages are arranged. We get fragmentation like

You can see fragmentation is now 14%.

Let us see page numbers allocated

We only have one jump rest all pages are allocated page ID serially. Since just one jump fragmentation decreased considerably.

I rebuild the Heap again and now when I checked fragmentation it was completely gone. And page ID allocation is like

Why Fragmentation Increased

Now regarding what could have caused fragmentation to rise we can corroborate it to fact that when pages were getting allocated to the heap they would not be continuous, as you saw above what caused fragmentation value to increase was jump in the PAGE ID's allocated to pages.

At the back of head you should also keep in mind that the word fragmentation for HEAP does not have any meaning, how would you define fragmentation for bunch of un-ordered pages.

Really Worried about Fragmentation

If you really face a scenario where heap table is fragmented and slowing queries it would be better creating a clustered index on table than rebuilding it. The reason is when you rebuild heap all underlying Non Clustered indexes are also rebuilt causing the rebuild process to take much longer time, utilizing lot of resources and bloating transaction log. On a production system one would always try to avoid this. Paul covered this in his Myth Section about heap.

PS: Please don't use undocumented command on production system. This was just for demonstration.