September 27, 2011 at 2:23 pm
opc.three (9/27/2011)
Heap fragmentation (which is extent fragmentation) is solely from inserts, allocating new extents that are not contiguous in the file.
Inserts and the creation of forwarded records, right? Physically speaking, would a new page allocation due to the creation of a forwarded record not be analogous to an insert and create extent frag?
Only if every single page in the heap is full and none of the extents in the heap have unallocated pages. It's different to an index where a row with a key value of X must go on page J. With a heap a row can go anywhere, so any page with free space is acceptable.
Updates to a heap cause forwarding pointers, which is far more a problem than extent fragmentation and would probably require heap rebuilds to fix before the extent fragmentation becomes a problem (depending of course on your insert, update and select patterns)
I see a lot of forwarded records in this particular system.[/quote]
Honestly, if that's the case I'd be looking at adding a cluster, not focusing on rebuilding the heap. Indexes can get a fill factor to allow for rows growing in-place, heaps can't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2011 at 3:15 pm
You are superb Gail, thank you for your help here 🙂
GilaMonster (9/27/2011)
Updates to a heap cause forwarding pointers, which is far more a problem than extent fragmentation and would probably require heap rebuilds to fix before the extent fragmentation becomes a problem (depending of course on your insert, update and select patterns)
I see a lot of forwarded records in this particular system.
Honestly, if that's the case I'd be looking at adding a cluster, not focusing on rebuilding the heap. Indexes can get a fill factor to allow for rows growing in-place, heaps can't.
That's exactly what I had proposed to my client, and he took it back to the vendor. It was finally blessed by the vendor because I explained to them how it would not introduce any logical behavior changes. Their developers were consistent in that they created an identity column on every one of the heaps, and already had a unique non-clustered index in place on the identity column. It is clear they ported the schema from their previous platform to SQL Server and did not know of the value in making those unique-clustered indexes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply