May 22, 2014 at 11:27 am
great question Igor.
May 24, 2014 at 7:16 am
Good question & explanation, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 23, 2014 at 4:17 am
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
I've tried your script in SQL Server 2014 (with Solid State Drives) multiple times and the fragmentation is always 0%. I'm not sure it has to do with SQL 2014 or the SSD:s, but the fragmentation is gone.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
September 23, 2014 at 12:05 pm
hakan.winther (9/23/2014)
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
I've tried your script in SQL Server 2014 (with Solid State Drives) multiple times and the fragmentation is always 0%. I'm not sure it has to do with SQL 2014 or the SSD:s, but the fragmentation is gone.
That's good.
I immediately went to some articles about fragmentation on SSDs:
1. http://technet.microsoft.com/en-us/magazine/hh750395.aspx
2. https://www.sqlskills.com/blogs/jonathan/does-index-fragmentation-matter-with-ssds/
Both say that fragmentation still does matter, but the works were done before SQL 2014.
I immediately tried it on my sql 2014 instance (without SSDs) and the figures are like 10 times less fragmentation, but as above, it still varies.
However, on sql server 2014 instance I saw better results, and that's good. I'm starting to no doubt your claims about the 0% on SSDs.
It would be really good if someone else could do the same test (at least) and share results regarding this question.
Thanks!
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply