What Causes Index Fragmentation in SQL Server?

Index fragmentation is usually caused by deleting of existing rows or updating existing values of the indexed column. Inserting new rows should not cause any index fragmentation.

This tutorial exercise shows you how update statements of 50000 rows on the table "fyi_link_indexed" with 100000 rows caused the index fragmented 84%:

USE FyiCenterData;
GO

SELECT COUNT(*) FROM fyi_links_indexed;
GO
100000

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
   FROM sys.dm_db_index_physical_stats (
      DB_ID(N'FyiCenterData'), 
      OBJECT_ID(N'fyi_links_indexed'),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
   WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;
GO
0        NULL             0.574712643678161
2        fyi_links_url    0
3        fyi_links_counts 0

UPDATE fyi_links_indexed SET url = REVERSE(url)
   WHERE id <=50000;
GO
(50000 row(s) affected)

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
   FROM sys.dm_db_index_physical_stats (
      DB_ID(N'FyiCenterData'), 
      OBJECT_ID(N'fyi_links_indexed'),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
   WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;
GO
0        NULL             0.574712643678161
2        fyi_links_url    84.053862508859
3        fyi_links_counts 0.448430493273543

Note that only the index on "url" fragmented. Index on "counts" did not affected, because the update statement only touched the "url" column.

 

⇒Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials