Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.

Example:

ALTER TABLE [dbo].[ClaimLineInstitutional] ADD  CONSTRAINT [PK_ClaimLineInsitutional]  
PRIMARY KEY NONCLUSTERED 
(
    [ClaimLineInstitutionalID] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,    
FILLFACTOR = 100) ON [PRIMARY]

Update:

I am querying dm_db_index_physical_stats.avg_fragmentation_in_percent, so I believe it is physical fragmentation I am seeing.

share|improve this question
1  
Strictly, you mean "clustered index" that is an identity column. A PK can be non-clustered. –  gbn Nov 9 '11 at 15:51

2 Answers 2

up vote 6 down vote accepted

Updates on data already there causes rows to be moved and forward pointers added

In this test, we can get 65% fragmentation on 115k densely packed rows

CREATE TABLE #FragTest (
    FragTestID int NOT NULL IDENTITY PRIMARY KEY,
    SomeString varchar(4100) NULL
    );

INSERT #FragTest (SomeString) VALUES ('a');
GO
INSERT #FragTest (SomeString)
    SELECT F1.SomeString FROM #FragTest F1 CROSS JOIN #FragTest F2;
GO 4
INSERT #FragTest (SomeString) SELECT F1.SomeString FROM #FragTest F1    
GO 6

SELECT COUNT(*) FROM #FragTest

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('b', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 0

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('c', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 1

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('d', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 2

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

Edit, sorry, browser trouble

If you change the middle replicate to 2000 you get <5% at the end. This happens because there is free space from moved rows in the first update

share|improve this answer
    
The script is incomplete and I can't save it. –  gbn Nov 9 '11 at 16:18
    
is it ok now, gbn? Edited last line as I supposed you would :-). –  Marian Nov 9 '11 at 16:20
1  
Yeah..I saw too late that the last select is missing. –  Marian Nov 9 '11 at 16:22
    
@Marian: thanks, I did what I wanted in baby steps –  gbn Nov 9 '11 at 16:24
    
Awesome example script! –  Ben Brocka Nov 9 '11 at 17:15

Logical fragmentation occurs when the logically next page is different from the physically next page.

In the case of the leaf level of an index with a monotonically increasing key this can happen if extent allocations for the index become interleaved with the extent allocations for other objects.

Even without this a small amount of fragmentation will ensue due to the first page allocations coming from mixed extents and pages for different index levels being interleaved in the same extents.

(Edit: Plus of course effect of updates and page splits as per @gbn's answer)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.