[dba-SQLServer] Fragmentation

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



More information about the dba-SQLServer mailing list