Asger Blond
ab-mi at post3.tele.dk
Wed Nov 14 14:46:09 CST 2012
Hi all,
Testing fragmentation and index rebuild, can anyone explain the following:
/* Create test table: */
CREATE TABLE Test(ID int IDENTITY(1,1) CONSTRAINT PK_ID
PRIMARY KEY CLUSTERED, SomeColumn varchar(50));
GO
/*Insert 300 rows with incrementing values for the clustered column:*/
INSERT Test(SomeColumn) VALUES('some value');
GO 300
/* Check the fragmentation:*/
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(),OBJECT_ID('Test'),NULL,NULL,'DETAILED');
GO --> 50
/* Question: Why does these inserts cause fragmentation of the clustered index? */
/* Try index rebuild and check fragmentation again: */
ALTER INDEX PK_ID ON Test REBUILD;
GO
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('test'),NULL,NULL,'DETAILED');
GO --> still 50
/* Question: Why does the index rebuild not remove the fragmentation? */
/* Now cause a deliberate fragmentation by adding a new 800 char column and filling with 800 x's for all existing rows: */
ALTER TABLE Test ADD SomeNewColumn char(800) NOT NULL DEFAULT 'x';
GO
/* Check fragmentation: */
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('test'),NULL,NULL,'DETAILED');
GO --> still 50
/* Retry index rebuild and check fragmentation again: */
ALTER INDEX PK_ID ON Test REBUILD;
GO
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('test'),NULL,NULL,'DETAILED');
GO --> now 0
/* Question: Why does an index rebuild remove the fragmentation in this case as opposed to the previous case? */
Asger