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


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

  • What do you mean when saying logical fragmentation. It is the fragmentation in terms of usage of data pages. I know there is no order but unordered data is not fragmented per se. Fragmentation in this case means efficient usage of data pages. – tuxmania May 18 '16 at 12:40
  • 2
    I guess we should ask, how big is the table? In rows and pages. – Cody Konior May 18 '16 at 13:17

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


Insert into dbo.Heaptest default values
go 50

select index_type_desc,avg_fragmentation_in_percent,fragment_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

enter image description here

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.

enter image description here

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

enter image description here

You can see fragmentation is now 14%.

Let us see page numbers allocated

enter image description here

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

enter image description here

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.

  • Thanks for your detailed analysis. I am facing big heap tables because some data vault enthusiasts think it is way better than using Clustered Indexes but then they use plenty of check constraints and non clustered indexes on those heaps so i don't really see the benefit of heaps in this situation. However since i am only the stupid developer i have to deal with this. Thanks again for the insight :) – tuxmania May 21 '16 at 11:53
  • 1
    How do you run 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') to return the result from only one table ? it returns on all indexes on all tables for me even if I correcty specify my table name in 'object_id' – Mickael May 15 '17 at 14:12
  • @Mickael I have used function db_id() which would take current database and I have specifically given object name so this will always look into current database and look for Heaptest and give the result. I am sure you might have missed something. Just make sure compatibility level is not 80 in that case db_id function ill not work – Shanky May 16 '17 at 5:37
  • @Shanky why don't you recommend to use undocumented query %%lockres%% in production? Could you explain it in detail? – Ralph Jun 24 '19 at 10:38
  • 1
    @user1624552 Simply because it is undocumented, means MS also does not keeps the documentation about it updated. What are its after effects how does it work is not documented anywhere that is why it is asked so. Ex there is command fn_dump_dblog() which creates hidden scheduler and that is not good. This command is also unsupported. You can use it but risk lies on you. – Shanky Jun 24 '19 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.