Click here to Skip to main content
11,642,216 members (61,763 online)
Click here to Skip to main content

Find and fix fragmented indexes

, 5 Feb 2014 CPOL 4.3K 10
Rate this:
Please Sign up or sign in to vote.
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:-

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.

License

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

Share

About the Author

Duncan Edwards Jones
Software Developer (Senior)
Ireland Ireland
C# / SQL Server developer
Microsoft MVP 2006, 2007
Visual Basic .NET

You may also be interested in...

Comments and Discussions

 
SuggestionJust a few notes Pin
franop10-Feb-14 22:02
memberfranop10-Feb-14 22:02 
SQL documentation recommends to use REORGANIZE clause for fragmentation beetween 5 and 30%, REBUILD for fragmentation over 30%. You may need to change online=off for text/image fields. Also including schema owner would be nice, if that's not you.

use [<databasename>]
 
select object_name(a.object_id), b.name , a.avg_fragmentation_in_percent,
'ALTER INDEX ' + b.name + ' ON ' + USER_NAME(o.schema_id) + '.' + 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
inner join sys.objects o on a.object_id=o.object_id
where b.name is not null and a.fragment_count > 2
 and a.avg_fragmentation_in_percent between 5 and 30
order by a.avg_fragmentation_in_percent desc
 

select object_name(a.object_id), b.name , a.avg_fragmentation_in_percent,
'ALTER INDEX ' + b.name + ' ON ' + USER_NAME(o.schema_id) + '.' + OBJECT_NAME(a.object_id) + ' REBUILD WITH (ONLINE=ON)' 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
inner join sys.objects o on a.object_id=o.object_id
where b.name is not null and a.fragment_count > 2
 and a.avg_fragmentation_in_percent>30
order by a.avg_fragmentation_in_percent desc

GeneralRe: Just a few notes Pin
Duncan Edwards Jones10-Feb-14 22:11
memberDuncan Edwards Jones10-Feb-14 22:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150731.1 | Last Updated 6 Feb 2014
Article Copyright 2014 by Duncan Edwards Jones
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid