Reduce Index Fragmentation

In my earlier article How To Find Index Fragmentation, you can get the T-SQL query to find the level of index fragmentation in a database. After finding the fragmentation level, you may have to either do INDEX REORGANIZE or INDEX REBUILD to reduce the fragmentation. Here are the details on, when to reorganize and when to rebuild an index. Steps and examples for performing these operations are also provided.

When To Reorganize And Rebuild

• Use Reorganize if the fragmentation percentage is between 5% to 30%.
• Use Rebuild if the fragmentation percentage is above 30%.

Reduce Index Fragmentation By Reorganizing

(1) Reorganizing Index Using Management Studio

1. In SSMS object explorer, expand the database and then the table which has the index.
2. Under the table, expand the index folder and select the Index you want to reorganize.
3. Right click the index and select Reorganize.
4. In the Reorganize Indexes pop-up window, you can see the Total fragment level. Press OK. This will reorganize the index.
   

(2) Reorganizing Index Using T-SQL

In T-SQL, you have to use the ALTER INDEX script to reorganize the index. You can either reorganize a single index or all the indexes in a table.

To reorganize an index

Syntax:

Use <DATABASE_NAME>
GO
ALTER INDEX <INDEX_NAME> ON <TABLE_NAME> REORGANIZE;
GO

Example:

USE WideWorldImporters;   
GO  
ALTER INDEX 
	FK_Sales_InvoiceLines_PackageTypeID ON Sales.InvoiceLines
	REORGANIZE;   
GO 

To reorganize ALL the indexes in a table

Syntax:

Use <DATABASE_NAME>
GO
ALTER INDEX ALL ON <TABLE_NAME> REORGANIZE;
GO

Example:

USE WideWorldImporters;   
GO  
ALTER INDEX ALL ON Sales.InvoiceLines REORGANIZE;   
GO 

Reduce Index Fragmentation By Rebuilding

(1) Rebuild Index Using Management Studio

1. In SSMS object explorer, expand the database and then the table which has the index.
2. Under the table, expand the index folder and select the Index you want to rebuild.
3. Right click the selected index and select Rebuild.
4. In the Rebuild Indexes pop-up window, you can see the Total fragment level. Press OK. This will rebuild the index.
   

(2) Rebuild Index Using T-SQL

In T-SQL, you have to use the ALTER INDEX script to rebuild the index.You can either rebuild a single index or all the indexes in a table. While rebuilding an index, you can include the fillfactor. It is optional though. In the below example I have included a fill factor of 90%. You can change the value of fillfactor or ignore the fill factor altogether.

To rebuild an index

Syntax:

Use <DATABASE_NAME>
GO
ALTER INDEX <INDEX_NAME> ON <TABLE_NAME> REBUILD WITH (FILLFACTOR=90);
GO

Example:

USE WideWorldImporters;   
GO  
ALTER INDEX 
	FK_Sales_InvoiceLines_PackageTypeID ON Sales.InvoiceLines
	REBUILD WITH (FILLFACTOR=90);  
GO 

To rebuild ALL the indexes in a table

Syntax:

Use <DATABASE_NAME>
GO
ALTER INDEX ALL ON <TABLE_NAME> REBUILD;
GO

Example:

USE WideWorldImporters;   
GO  
ALTER INDEX ALL ON Sales.InvoiceLines REBUILD;  
GO 

Reference

• About altering index at Microsoft Docs.