January 14, 2008 at 9:39 am
if you have table fragmentation ( a table with no indexes that is) how do you defrag it? I would usually use alter index...reorganize/rebuild , but that doesnt work for heap objects.
January 14, 2008 at 10:29 am
One way to defragment a heap is to create a clustered index on the table and then drop it.
January 14, 2008 at 12:35 pm
You pretty much need a clustered index.
any reason why you dont want a ci on the table ?
Greg Jackson
Gregory A Jackson MBA, CSM
January 15, 2008 at 12:29 am
A heap can't really fragment. Since there's no defined order for the rows, pages are just added at the end of the chain.
What you can get with heaps is forward pointers. Happens when a row is updated and no longer fits on the page it was on. On cluster, that would cause a page split and hence fragmentation. With a heap, the row is moved to a new page and a reference is left pointing at the new page
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
January 15, 2008 at 4:15 pm
sorry but a heap will fragment if it gets deletes and suffer page splits the same as any other table if conditions are right. You're thinking of a queue with FIFO which would unlikely fragment. Queues aren't always the same as heaps.
It's actually considered bad practice to add a clustered index and then drop it to remove fragmentation, but Paul Randel never said why! guess you should export the data, truncate the table and import it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 15, 2008 at 4:34 pm
If the Database does not allow downtime, you're hosed with the Create CI and Drop trick. (can you imagine if the table has millions of records?)
the BEST option is to Create a clustered index and Leave it in place.
then schedule routine maintenance to keep the table defragged....
GAJ
Gregory A Jackson MBA, CSM
January 15, 2008 at 6:23 pm
If you have to defrag a heap (with downtime allowed), and you don't want to add a clustered index, you can:
1. Rename the table to prevent access to it.
2. Create a new table with the correct structure, but a tempororary name.
3. Insert the data into the new table.
4. Rename the new table to the permanant name.
5. Drop the old table.
January 16, 2008 at 12:00 am
colin Leversuch-Roberts (1/15/2008)
sorry but a heap will fragment if it gets deletes and suffer page splits the same as any other table if conditions are right.
From my understanding of a heap (the way SQL implements it), new rows go into the whatever page has space. If no page has free space, then a new page/extent is allocated and added at the end of the chain. The new row then goes in that.
Page splits on clusters happen because the key defines the page that the row must go on and if there's no space on that page the full page gets split into 2 half full pages and the newly allocated page gets added into the chain.
What would cause a page split on a heap?
I can see fragmentation of a heap happening if the database is shrunk (because pages are just put anywhere, resulting in a page further down the IAM chain been located earlier in the file)
If you're got a reference that expains more about heaps, I would appreciate it. I don't deal with them often.
It's actually considered bad practice to add a clustered index and then drop it to remove fragmentation, but Paul Randel never said why!
Wasted IOs. Unnecessary expansion of the data file. Downtime (unless you're creatng the index online in SQL 2005)
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply