# 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…

- Author: Jérôme Giacomini
- Language: en
- Canonical URL: [SQL Server: when should you rebuild your indexes?](https://jeromegiacomini.net/articles/2019/07/01/sql-server-when-should-you-rebuild-your-indexes)
- Published: 2019-07-01
- Last modified: 2026-09-05
- Topics: .NET, DevOps, SQL

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.

```sql

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:

![](https://jeromegiacomini.net/Blog/wp-content/uploads/2019/06/indexResult.png)

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:**

```sql

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


**Reorganizing the’index d’a table:**

```sql

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

GO
```


## Reconstruct the indexes of a table

**Reconstruction of the index d’a table:**

```sql

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


**Rebuilding the’index d’a table:**

```sql

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

GO
```


Happy coding.

To go further:
- [Link to the official documentation](https://docs.microsoft.com/fr-fr/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017)