Inserts, updates, and deletes spoil indexes which start out good after create index or index rebuild. SQL Server 2005 has introduced a new way to check index fragmentation:

-- SQL Server 2005 and SQL Server 2008

USE AdventureWorks2008;

GO

-- DETAILED fragmentation info on table

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , 'DETAILED');

GO

-- Fragmentation info on table

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , NULL);

GO

-- All fragmentation info

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        NULL, NULL, NULL , NULL);

GO

------------

-- SQL Server 2000 index fragmentation for all indexes of a table

use AdventureWorks2008;

dbcc showcontig('Sales.SalesOrderDetail') with all_indexes

/*

....

DBCC SHOWCONTIG scanning 'SalesOrderDetail' table...

Table: 'SalesOrderDetail' (898102240); index ID: 3, database ID: 7

LEAF level scan performed.

- Pages Scanned................................: 227

- Extents Scanned..............................: 31

- Extent Switches..............................: 30

- Avg. Pages per Extent........................: 7.3

- Scan Density [Best Count:Actual Count].......: 93.55% [29:31]

- Logical Scan Fragmentation ..................: 3.08%

- Extent Scan Fragmentation ...................: 25.81%

- Avg. Bytes Free per Page.....................: 79.5

- Avg. Page Density (full).....................: 99.02%

...

*/

------------

Use the following pre-2005 DBCC command to check the state of an index:

dbcc showcontig(TableX) with all_indexes

Results:

LEAF level scan performed.
- Pages Scanned................................: 22329
- Extents Scanned..............................: 2804
- Extent Switches..............................: 2804
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [2792:2805]
- Logical Scan Fragmentation ..................: 12.56%
- Extent Scan Fragmentation ...................: 3.28%
- Avg. Bytes Free per Page.....................: 2146.7
- Avg. Page Density (full).....................: 89.77%

The fragmentation is 12.56%, in the tolerable range.