Identifying and Repairing Index Fragmentation in SQL Server

When the order of pages in a index does not match the physical order in a datafile, we consider it as fragmentation. This happens in databases due to data insertion, updating, and deletion over the time, which can degrade performance.

Today will share about Index fragmentation in SQL server, how can we identify fragmentation and how to repair it using both T-SQL and SSMS simultaneously.

I am using below query to find the fragmentation in AdventureWorks2019 database’s Employee table in HumanResources schema. I am using sys.dm_db_index_physical_stats dynamic management view and joining it with sys.indexes system table using object_id column on both.

select a.index_id, name, avg_fragmentation_in_percent from
sys.dm_db_index_physical_stats (db_id(N'AdventureWorks2019'),
object_id(N'HumanResources.Employee'), NULL, NULL, NULL) as a JOIN
sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id;

As it shows two indexes AK_Employee_LoginID and AK_Employee_NationalIDNumer having fragmentation. Will repair one with T-SQL and another one with SSMS now.

#To reorganize 
alter index AK_Employee_LoginID on HumanResources.Employee reorganize;
#To rebuild
alter index AK_Employee_LoginID on HumanResources.Employee rebuild;

Use the first query again to check fragmentation status.

~Thank you for reading