Answers

Question and Answer:

  Home  MS SQL Server

⟩ How To Defragment Indexes with ALTER INDEX ... REORGANIZE?

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:

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_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 ggl_links_url 84.053862508859

3 ggl_links_counts 0.448430493273543

ALTER INDEX ggl_links_url ON ggl_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'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_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 ggl_links_url 1.87590187590188

3 ggl_links_counts 0.448430493273543

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

 126 views

More Questions for you: