Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 394

Monitoring Index Fragmentation
In SQL Server 2000, you used DBCC showcontig to monitor index fragmentation. With SQL Server
2005, you now have a new function, sys.dm_db_index_physical_stats . The syntax for this function
is detailed in full in BOL, so here's a look at running it the People sample database:

use People
go

SELECT *
FROM sys.dm_db_index_physical_stats
(
DB_ID('People'),
OBJECT_ID('People'),
NULL,
NULL ,
'DETAILED'
)

go

The results provide a lot of information, but there are just a few things you really want to focus on. In fact,
to get the information you need on the level of fragmentation, you can just use look at these columns:

SELECT index_id, index_level, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID('People'),
OBJECT_ID('People'),
NULL,
NULL ,
'DETAILED'
)

go

What you are looking for is any level of index where the avg_fragmentation_in_percent is higher than
0, although low single digits are also acceptable. In the case of the People table, I dropped and recreated
the table, then created the indexes, and then loaded 200,000 rows. After executing these steps the results
don't look so good and show a high level of index fragmentation:

index_id index_level avg_fragmentation_in_percent
----------- ----------- ----------------------------
1 0 98.6089375760737

1 1 96.4285714285714

1 2 0

6 0 1.38666666666667

6 1 57.1428571428571

6 2 0

7 0 98.3564458140729

7 1 100

7 2 0

8 0 99.1496598639456

8 1 98.4375

8 2 0

9 0 98.4857309260338