Reorganize a SQL Server index

How to reorganize a SQL Server index? This operation defragments the bottom level of the indexes by physically reordering the pages, from left to right.

The reordering operation also compacts the pages of the index. To better understand this, imagine a recipe book whose pages are no longer in order. You must then reorganise the pages to quickly reach the desired page.

Reorganize a fragmented SQL Server index and optimize performance

First, let’s look at the index in this tutorial with sample code to create a non-clustered SQL Server index on a sales table.

The index uses the main filters on the sales table from the time dimension, i.e. the year and month columns.

After many insertions and deletions, the data is no longer in a logical ascending order on the disks. This means that the indexes are fragmented.

To reorganise the data in an index and improve performance, the indexes must be reorganised regularly.

Or when the fragmentation exceeds a certain percentage.

Check the fragmentation average percentage on all SQL Server tables

Secondly, this query displays all statistics for all tables and indexes in an MSSQL database.

The query not only displays the data but also stores it in a hardcopy table to allow the data to be filtered and sorted with the second query.

Considering the column avg_fragmentation_in_percent, i.e., the average fragmentation in percent, displays the information for each index.

SELECT   * 
INTO   dbo.dm_db_index_physical_stats_TEMP
FROM   sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

SELECT      * 
FROM      dm_db_index_physical_stats_TEMP
ORDER BY   avg_fragmentation_in_percent DESC;

Reorganise a SQL Server index with this query

At what percentage should the indexes be rebuilt or reorganized?

It is a good SQL practice to reorganize indexes if the fragmentation percentage is between 15 and 30%.

However, above 30%, rebuild the index with the REBUILD command.

ALTER INDEX indexYearMonth
ON [dbo][SALES] REORGANIZE;

This article shows how to reorganise a fragmented index with a simple example.

To go further, see Microsoft’s documentation on why to check for index fragmentation and how to rebuild and reorganise indexes.

This is the first tutorial in the series on views with a script to create a SQL Server view.

Script to create a view in SQL Server