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
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
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.