SQL Server: when should you rebuild your indexes?

The more your database is going to be used, the more likely your indexes are to be fragmented. decrease sharply the performance of your requests and slow down The entire application.

In this article we will consider how:

• to diagnose fragmentation of our index tables
• reorganize / reconstruct an index

Identify the level of fragmentation

Before reconstructing indexes we have to identify which ones are fragmented, to do so we will examine our database so that it can give us the level of fragmentation of each index.

SELECT avg_fragmentation_in_percent AS FragmentationInPercent,
OBJECT_SCHEMA_NAME  (Stats.object_id) AS TableName,
OBJECT_NAME (Stats.object_id) AS TableName,
Indexes.name
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.T_CLIENT_CLI') , NULL, NULL , 'LIMITED') AS Stats
INNER JOIN sys.indexes AS Indexes ON Stats.object_id = Indexes.object_id AND Stats.index_id = Indexes.index_id
ORDER BY avg_fragmentation_in_percent DESC

After executing the request you should get a result in this form:

Original illustration unavailable

The official documentation estimates that an index fragmented by more than 30% must be reconstructed while an index fragmented by between 5% and 30% can only be reorganized.

Be careful . It 's counterproductive to rebuild indexes

Reorganize the indexes of a table

Reorganization of indices’a table:

ALTER INDEX ALL ON [NomDuSchema].[NomDeLaTable]
REORGANIZE ;
GO

Reorganizing the’index d’a table:

ALTER INDEX IX_MonIndex
ON [NomDuSchema].[NomDeLaTable]
REORGANIZE ;

GO

Reconstruct the indexes of a table

Reconstruction of the index d’a table:

ALTER INDEX ALL ON [NomDuSchema].[NomDeLaTable]
REBUILD ;
GO

Rebuilding the’index d’a table:

ALTER INDEX IX_MonIndex
ON [NomDuSchema].[NomDeLaTable]
REBUILD ;

GO

Happy coding.

To go further:

• Link to the official documentation