SQL Server 2005 Index Fragmentation

There are about a billion articles about this already I am sure. I created this script using several different articles and samples. Just a straight forward way of quickly listing all the details about index fragmentation for the database of your chosing sorted by avg_fragmentation_in_percent.

DECLARE @theDatabase int
set @theDatabase = (select DB_ID(‘Your Database here’))
SELECT OBJECT_NAME(i.object_id) AS TableName,i.name AS TableIndexName,p.*
FROM sys.dm_db_index_physical_stats (@theDatabase, NULL, NULL, NULL, ‘DETAILED’) p, sys.indexes i
where i.object_id = p.object_id
and p.index_id = i.index_id
order by p.avg_fragmentation_in_percent DESC

Again there are several articles out there on how to interpret the results as well I like to target the most used indexes first. You can use SQL Profiler to find out what is used the most. An article on SQL Profiler should be my next post. Sometimes the most used index is also the largest, requiring the most resources to rebuild. Once you determine what indexes need attention you have some options for repairing the fragmentation.

Books Online Search Topic: Reorganizing and Rebuilding Indexes.

Option 1:  Reorganizing the Index – uses minimal system resources. Reorders leaf-level pages to match the logical order of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized withing the existing pages allocated to it, no new pages are allocated. Reogranizing also compacts the index page. Reorganizing is good for indexes that are not heavily fragmented.

Option 2: Rebuilding the Index – Rebuilding an index drops the index and creates a new one. All fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages. This can reduce the number of page reads required to obtain the requested data and can imporve disk performance. You can either use the ALTER INDEX REBUILD or the CREATE INDEX DROP_EXISTING SQL to rebuild an index.