snippetsqlMinor
How can an identity primary key index become fragmented?
Viewed 0 times
canidentityprimaryfragmentedhowindexbecomekey
Problem
From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.
Example:
Update:
I am querying dm_db_index_physical_stats.avg_fragmentation_in_percent, so I believe it is physical fragmentation I am seeing.
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.
Solution
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
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
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
Code Snippets
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);Context
StackExchange Database Administrators Q#7689, answer score: 8
Revisions (0)
No revisions yet.