I’m running into index issues on SQL Server and wanted to figure out index fragmentation to see if we could improve it. Here’s a way to find index fragmentation:

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id(),null,null,null,'DETAILED')
    WHERE index_id  0
) AS dt 
INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

But, I was running into an issue because my database is running in SQL 80 mode (SQL Server 2000). The following fixes this issue.

declare @db_id smallint
set @db_id=db_id()

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (@db_id,null,null,null,'DETAILED')
    WHERE index_id  0
) AS dt 
INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

It seems that in SQL 80 mode, you can’t use the output of functions directly, so storing it in a variable first does the trick.

The output of this method shows internal and external fragmentation levels:

Exernal Fragmentation
External fragmentation occurs when on disk, the physical storage of contents is non-contiguous. This result represents the average amount of fragmentation on an index. The lower this number is, the better. Any result over 10% should be dealt with.

Internal Fragmentation
Internal fragmentation occurs when records are stored non-contiguously inside the page. In other words, there is space between records in the index. This can happen as a table is modified (INSERT, UPDATE, DELETE) without rebuilding the index. This result represents the average amount of page storage consumed in a given index. The higher the result, the better.