How To Defragment Indexes with ALTER INDEX ... REORGANIZE in SQL Server?

When an index is defragmented to a small percentage, like < 30%, you can use the "ALTER INDEX ... REORGANIZE" statement to defragment the index. Here is a tutorial exercise on defragmenting indexes:

USE FyiCenterData;
GO

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

ALTER INDEX fyi_links_url ON fyi_links_indexed REORGANIZE;
GO

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    1.87590187590188
3        fyi_links_counts 0.448430493273543

The fragmentation level has been reduced from 84.05% to 1.88%.

⇒ "ALTER INDEX ... REBUILD" - Defragmenting Indexes in SQL Server

⇐ Defragmenting Table Indexes in SQL Server

⇑ Understanding and Managing Indexes in SQL Server

⇑⇑ SQL Server Database Tutorials