Heaps rebuild
-
Comments posted to this topic are about the item Heaps rebuild
Igor Micev,My blog: www.igormicev.com
-
nice question: I've never used SORT_IN_TEMPDB and DATA_COMPRESSION and I've never received that warning message, but with a little bit of intuition I got the two points 🙂
-
It took me two cups of tea and a little brain power but I got it right!
Nice question by the way, thank you.
-
Two points missed today, but not surprised.
-
The question is not quite right. Fragmentation can be removed by running this because the table is rebuilt.
Your claim that Paul says you can't remove fragmentation is wrong. Paul doesn't say you can't do it, he says don't do it.
If you think you can use ALTER TABLE … REBUILD in SQL Server 2008 to fix heap fragmentation, you can, but it causes all the nonclustered indexes to be rebuilt as the heap record locations obviously change.
-
This was removed by the editor as SPAM
-
A rarely use heaps, but did some research before answering and found my answer on this page http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c6783244-c558-4a23-8ce4-520ce4a87033/meaning-of-index-fragmentation-return-for-heap-tables?forum=sqldatabaseengine and got the answer "wrong"..
-
yahoo.... i got it right..
I have created a heap table and inserted data 50,00,000 records and then deleted
Observed the below points
1. page count is reduced before and after the rebuild statement , that implies tables is compressed and rebuilt
2. but found fragmentation is increased.
It took some time to select 4 :w00t:
finally, I did it right
-
Thank for the post, very interesting one.
(Out of 4, I got 2 correct, but learnt a lot today) 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Good question, Igor. Enjoyed diging the inromation and basics.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't." -
I got it wrong.
I'm still not sold on why the fragmentation would still be there.
---------------
Mel. 😎 -
I totally agree with Sean Pearce
The question is not quite right. Fragmentation can be removed by running this because the table is rebuilt.
Your claim that Paul says you can't remove fragmentation is wrong. Paul doesn't say you can't do it, he says don't do it.
If you think you can use ALTER TABLE … REBUILD in SQL Server 2008 to fix heap fragmentation, you can, but it causes all the nonclustered indexes to be rebuilt as the heap record locations obviously change.
Let me ask you then, if no fragmentation is removed when rebuilding an heap, explain me how forwarding pointers gets removed...
-
I initially had trouble parsing the suggested answers since "The Heap had rebuilt." is missing a participle. I suppose what was meant is "The Heap was rebuilt."
Perhaps the editors should proofread the submissions a bit more carefully 😉
-
I thought it was a good question and it made me think about it before I got it wrong. Thanks.
-
I disagree that fragmentation would not be removed. As long as the table is big, the fragmentation will be reduced. I have a bunch of crappy third party software tables that I have to do this to on a regular basis to remove fragmentation. I'd rather put on some clustered indexes, but I'm not supposed to change them.
Be still, and know that I am God - Psalm 46:10
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply