May 2, 2016 at 2:18 pm
I was trying to prove to developers that they should convert the heaps to clustered indexes, with the numbers to back it up.
For comparison, I selected into a new table with the same data, and put a clustered index on it.
Using sys.dm_db_index_physical_stats, I thought the results were surprising. The heap results were from index_id = 0.
Heap fragment count = 5,800
Clustered index fragment count = 11,000
Heap page count = 191,800
Clustered index page count = 191,800
Heap avg fragmentation in percent = 24.1%
Clustered index fragmentation in percent = 0.01%
I was expecting more pages and more fragments for the heap, but that didn't happen. Also it seems impossible that they should have the exact same number of pages. Any ideas?
May 2, 2016 at 4:52 pm
Run this:
ALTER INDEX IndexName ON SchemaName.TableName REBUILD
Check the fragmentation stats.
Now let them defragment the HEAP to match the outcome.
_____________
Code for TallyGenerator
May 3, 2016 at 1:04 pm
The clustered index was already brand new with the pages shown, so no there shouldn't be a difference with a rebuild.
May 3, 2016 at 3:14 pm
Can you post the full sets returned by the view for both tables?
The numbers look a bit odd.
Was there any DB shrinking done after the index was created?
And I would suggest at least reorganising the index pages.
But fragmentation should not be the thing to evaluate when comparing HEAPs with clustered tables.
Heap does not have a logical structure, so it cannot be fragmented, unless it's divided into several heaps.
Like a heap of rubble on a demolition site - it's a single continuos fragment, until you're trying to "cluster" it into chunks of bricks, concrete block, timber, etc. Then it becomes heavily fragmented, even though not a single piece of rubble has been moved.
What you should compare is the speed of selection.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply