Once indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. These automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index over time.

The result – indexes now have pages where logical ordering (based on the key-value) differs from the physical ordering inside the data file. This means that there is a high percentage of free space on the index pages and that SQL Server has to read a higher number of pages when scanning each index. Also, ordering of pages that belong to the same index gets scrambled and this adds more work to the SQL Server when reading an index – especially in IO terms.

The solution to fragmented indexes is to rebuild or reorganize indexes.

Index reorganization - Index reorganization is a process where the SQL Server goes through the existing index and cleans it up. While index reorganization is a pure clean-up operation that leaves the system state as it is without locking-out affected tables and views.

Index rebuild - index is deleted and then recreated from scratch with an entirely new structure, free from all piled up fragments and empty-space pages. the rebuild process locks the affected table for the whole rebuild period, which may result in long down-times that could not be acceptable in some environments

To decide which one to do, it is important to answer two main questions:

1. What is the degree of fragmentation?

2. What is the appropriate action? Reorganize or rebuild?

Detecting fragmentation information for an index or table or database

sys.dm_db_index_physical_stats will return size and fragmentation information for the data and indexes of the specified table or view in SQL Server. Read this link for full details.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2017');

SET @object_id = OBJECT_ID(N'AdventureWorks2017.Person.Address');

IF @db_id IS NULL

BEGIN;

PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

PRINT N'Invalid object';

END;

ELSE

BEGIN;

SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

END;

Generally accepted solution based on the percent of fragmentation - avg_fragmentation_in_percent column from the previously described sys.dm_db_index_physical_stats function.

Fragmentation is less than 10% – no de-fragmentation is required.

Fragmentation is between 10-30% – it is suggested to perform index reorganization

Fragmentation is higher than 30% – it is suggested to perform index rebuild

Using SQL Server Management Studio: