Determine Index Fragmentation in a SQL Server Database

Index fragmentation causes significant performance problems in EnterpriseOne systems. If access to the underlying data is slow, end users will not have the 'snappy' experience they expect, particularly on the web-client interface.

Determining index fragmentation on SQL 2000 was simple, using the DBCC SHOWCONTIG command with a few extra arguments:


--SQL Script begin
USE databasename
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
--SQL Script end


Determining index fragmentation in SQL 2005 is a little more difficult as Microsoft has switched to the use of Dynamic Management Views to grant access to important information.


The code below is fast but requires two steps - a one-time per server creation of a function and the SELECT statement that uses the function.  The function was created based on knowledge gleaned from Paul Randal's In Recovery blog posting on combining CROSS APPLY and Dynamic Management Views.


Configuration

Create the function by executing the following code:

--Script Begin
use master
GO
IF exists (select * from sys.objects where name = N'fn_PhysicalIndexStatistics_Wrapper')
DROP FUNCTION fn_PhysicalIndexStatistics_Wrapper;
GO
CREATE FUNCTION fn_PhysicalIndexStatistics_Wrapper
(@DatabaseID INT,
@ObjectID INT,
@IndexID INT,
@PartitionNumber INT,
@Mode VARCHAR)

RETURNS @IndexStats TABLE
([Object ID] INT,
[Index ID] INT,
[Index Description] VARCHAR(30),
[Avg. Fragmentation %] FLOAT,
[Fragment Count] BIGINT,
[Page Count] BIGINT,
[Alloc. Unit Type] nvarchar(60))

BEGIN
INSERT INTO @IndexStats
([Object ID], [Index ID], [Index Description], [Avg. Fragmentation %], [Fragment Count], [Page Count], [Alloc. Unit Type])

SELECT
object_id, index_id, index_type_desc, round(avg_fragmentation_in_percent, 0), fragment_count, page_count, alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats
(@DatabaseID,
@ObjectID,
@IndexID,
@PartitionNumber,
@Mode)

RETURN
END
--Script End


Execution

Run the following code. Replace databasename with your database name:


--Script Begin
USE databasename
GO
SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(30)) as 'Object Name',
cast(sys.indexes.name as char(30)) as 'Index Name',
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Description],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Avg. Fragmentation %],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Fragment Count],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Page Count]
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
CROSS APPLY master.dbo.fn_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL)
JOIN sys.indexes
on master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Object Id]=sys.indexes.object_id
and master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Id]=sys.indexes.index_id
WHERE [Index Description] ! = 'HEAP'
--and [Page Count] > 100
order by [Object Name], [Index Name]
--or
--order by [Avg. Fragmentation %] desc