Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Find and Fix Fragmented Indexes

4.93/5 (5 votes)
6 Feb 2014CPOL 12K  
Quick tip to allow you to identify indexes that could do with defragmentation

Introduction

Over time, SQL server indexes become fragmented as data are inserted and deleted from the tables they refer to. Identifying these and defragmenting them can improve your database performance.

Using the Code

Replace [databasename] with your database name and then run the following SQL:

SQL
select object_name(a.object_id), b.name , a.avg_fragmentation_in_percent,  
'ALTER INDEX ' + B.NAME + ' ON ' + OBJECT_NAME(a.object_id) + ' REORGANIZE ' AS FIX_SQL
from 
sys.dm_db_index_physical_stats(db_id(N'[databasename]'), DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a
inner join
sysindexes as b
on a.object_id = b.id 
and a.index_id = b.indid
order by a.avg_fragmentation_in_percent desc

Then, from the results set if any indexes are highly fragmented, run the FIX_SQL to repair them.

Points of Interest

This runs a lot faster on the database itself than from master in my experience.

History

  • 6th February, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)