How To Decide Whether to Rebuild or Reorganize Table Indexes [SQL Server]

Database Administrators sometimes get doubt whether to rebuild the indexes or reorganize the indexes. Before deciding the right option lets try to understand the difference between rebuilding and reorganizing.

When an Index is rebuilt, the existing index is dropped and a new one is created. This operation takes long time and utilizes lot of SQL Server resources like CPU cycles and disk space.

When an index is reorganized, the index is not dropped and recreated but the leaf level pages of the index are defragmented by physically reordering to match the logical order.

As rebuilding indexes is an expensive operation, DBA should carefully choose when to rebuild over reorganising the indexes by analysing the degree of fragmentation in the indexes. The general guidelines given in MSDN says

• Reorganise an index when the degree of fragmentation is between 5 and 30%
• Rebuild an index when the degree of fragmentation is over 30%

These are guideline values and the actual degree of fragmentation varies case to case depending on many parameters of the database server environment. It is always advised to perform multiple tests to identify the exact degree of fragmentation which decides whether to rebuild or reorganize.

Example Script to Rebuild and Reorganize an Index

-- SQL Server Script to rebuild the index

-- 'PK_Employee_EmployeeID' available on

-- 'Employee' table

ALTER INDEX PK_Employee_EmployeeID

ON Employee

REBUILD;

-- SQL Server Script to reorganize the index

-- 'PK_Employee_EmployeeID' available on

-- 'Employee' table

ALTER INDEX PK_Employee_EmployeeID

ON Employee

REORGANIZE ;