Menu Close

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.

With T-SQL, will do the reorganize using below queries.
#To reorganize 
alter index AK_Employee_LoginID on HumanResources.Employee reorganize;
#To rebuild
alter index AK_Employee_LoginID on HumanResources.Employee rebuild;

 

From SSMS
will go the database –> Tables –> HumanResources.Employee –> Indexes –> AK_Employee_NationalIDNumer.  Right click on index name and select reorganize, from the same menu you can choose rebuild option to rebuild index.

 Click okay on the reorganize window and done.

Use the first query again to check fragmentation status.

~Thank you for reading

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!