Heaps rebuild

Question

Heaps rebuild

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Sean Pearce (5/21/2014)
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.

In the question it's stated that there are no any non-clustered indexes in the table.

I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".

Igor Micev,My blog: www.igormicev.com

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 2

Thanks for the easy and informative question. The way to chose answers caused me problems but still a good QOTD.

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 3

ssimmons 2102

SSCommitted

Points: 1634

May 21, 2014 at 7:58 am

#1715365

I got 3 out of 4, do I get 3/4 of a point?

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Megistal (5/21/2014)
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...

If you have fragmentation of over 30% at a clustered index for e.g. and you rebuild it, then the fragmentation is going down to about 0%. But with the Heaps it's not the case, i.e. with every rebuild you'll have different fragmentation, and it's varying.

Rebuilding of a Heap removes the forwarding pointers.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

Pei Zhu-415513 SSCarpal Tunnel Points: 4040 More actions · Answer 5

Igor pls read Paul's blog carefully yourself. rebuild heap table will remove fragmentation even it might not be a good idea since it is expensive.

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
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.
In the question it's stated that there are no any non-clustered indexes in the table.
I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".

Read it carefully.

your answer of "Fragmentation had not removed" is not relevant to a HEAP rebuild. Fragmentation can and will change during a HEAP rebuild.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
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.
In the question it's stated that there are no any non-clustered indexes in the table.
I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".
Read it carefully.
your answer of "Fragmentation had not removed" is not relevant to a HEAP rebuild. Fragmentation can and will change during a HEAP rebuild.

Well, this could be a correction to the question. I agree. However the fragmentation changes with every rebuild.

I'm posting this from pmadhavapeddi22, and I got similar.

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

Igor Micev,My blog: www.igormicev.com

Sean Pearce SSCoach Points: 15999 More actions · Answer 8

Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
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.
In the question it's stated that there are no any non-clustered indexes in the table.
I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".
Read it carefully.
your answer of "Fragmentation had not removed" is not relevant to a HEAP rebuild. Fragmentation can and will change during a HEAP rebuild.
Well, this could be a correction to the question. I agree. However the fragmentation changes with every rebuild.
I'm posting this from pmadhavapeddi22, and I got similar.
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

CREATE TABLE TestFrags (myGUID UNIQUEIDENTIFIER, MyValue CHAR(1));

GO

INSERT INTO TestFrags VALUES (NEWID(), '');

GO 50000

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');

GO

ALTER TABLE TestFrags REBUILD WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

GO

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');

GO

DROP TABLE TestFrags;

GO

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Megistal SSCrazy Eights Points: 8787 More actions · Answer 9

You beat me to it Sean Pearce!

I was also in the process of writing a script to end the discussion.

In case of doubt, let SQL do the talking!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
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.
In the question it's stated that there are no any non-clustered indexes in the table.
I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".
Read it carefully.
your answer of "Fragmentation had not removed" is not relevant to a HEAP rebuild. Fragmentation can and will change during a HEAP rebuild.
Well, this could be a correction to the question. I agree. However the fragmentation changes with every rebuild.
I'm posting this from pmadhavapeddi22, and I got similar.
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
CREATE TABLE TestFrags (myGUID UNIQUEIDENTIFIER, MyValue CHAR(1));
GO
INSERT INTO TestFrags VALUES (NEWID(), '');
GO 50000
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
ALTER TABLE TestFrags REBUILD WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
DROP TABLE TestFrags;
GO

Hi Sean

I got your test and spotted at first glance that it's not relevant enough. However, I extended your example a bit like this one and made some tests.

The conclusion is: you cannot control the fragmentation on Heaps.

CREATE TABLE TestFrags (myGUID UNIQUEIDENTIFIER, MyValue CHAR(1), MyDate datetime, MyVarchar varchar(30));

GO

INSERT INTO TestFrags VALUES (NEWID(), '',CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)), CONVERT(varchar(30),CONVERT(DATETIME, ROUND(60000*RAND()-30000,9))) );

GO 50000

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');

GO

--11.6666666666667

ALTER TABLE TestFrags REBUILD WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

GO

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');

GO

--3.7037037037037 after first rebuild

--11.1111111111111 after second rebuild

--3.7037037037037 after third rebuild

--11.1111111111111 after forth rebuild

DROP TABLE TestFrags;

GO

Igor Micev,My blog: www.igormicev.com

Ken SSCoach Points: 17191 More actions · Answer 11

Interesting question. Thanks Igor. I guessed wrong and then did some testing on my SQL 2008 R2 box and got the same results as the correct answer choices.

Pei Zhu-415513 SSCarpal Tunnel Points: 4040 More actions · Answer 12

Guys, pls read book on line about avg_fragmentation_in_percent and other columns for heaps. in general for heap fragmentation you do not look at avg_fragmentation_in_percent.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Pei Zhu-415513 (5/21/2014)
Guys, pls read book on line about avg_fragmentation_in_percent and other columns for heaps. in general for heap fragmentation you do not look at avg_fragmentation_in_percent.

Interesting, the definition in BOL for that column says nothing about not using it for heaps. In fact it is quite the opposite in that it tells you what that column measures for the heap as far as fragmentation goes. avg_fragmentation_in_percent is the extent fragmentation of a heap.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
Igor Micev (5/21/2014)
Sean Pearce (5/21/2014)
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.
In the question it's stated that there are no any non-clustered indexes in the table.
I nowhere say that you can't remove fragmentation. I say "If you expected the fragmentation had removed... then read Paul's blog".
Read it carefully.
your answer of "Fragmentation had not removed" is not relevant to a HEAP rebuild. Fragmentation can and will change during a HEAP rebuild.
Well, this could be a correction to the question. I agree. However the fragmentation changes with every rebuild.
I'm posting this from pmadhavapeddi22, and I got similar.
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
CREATE TABLE TestFrags (myGUID UNIQUEIDENTIFIER, MyValue CHAR(1));
GO
INSERT INTO TestFrags VALUES (NEWID(), '');
GO 50000
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
ALTER TABLE TestFrags REBUILD WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
DROP TABLE TestFrags;
GO
Hi Sean
I got your test and spotted at first glance that it's not relevant enough. However, I extended your example a bit like this one and made some tests.
The conclusion is: you cannot control the fragmentation on Heaps.
CREATE TABLE TestFrags (myGUID UNIQUEIDENTIFIER, MyValue CHAR(1), MyDate datetime, MyVarchar varchar(30));
GO
INSERT INTO TestFrags VALUES (NEWID(), '',CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)), CONVERT(varchar(30),CONVERT(DATETIME, ROUND(60000*RAND()-30000,9))) );
GO 50000
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
--11.6666666666667
ALTER TABLE TestFrags REBUILD WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestFrags'), 0, NULL, 'DETAILED');
GO
--3.7037037037037 after first rebuild
--11.1111111111111 after second rebuild
--3.7037037037037 after third rebuild
--11.1111111111111 after forth rebuild
DROP TABLE TestFrags;
GO

The beauty of this argument is that I have seen the exact same behavior, that Igor is demonstrating, when dealing with Clustered Indexes.

We are looking at fragmentation in the realm of 0-12% which is really inconsequential. Depending on the number of pages in these examples, the results are to be expected.

In the end, defragging a heap using the Alter Table Rebuild syntax does defrag the heap. It also defrags a CI. The effects seen here can be seen on either heaps or CIs.

Too bad the BOL documentation recommends creating a CI and dropping it to fix fragmentation. tsk tsk tsk. Hopefully MS will fix that document because we know that to be a less than ideal practice.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 15

Great question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP