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:


--SQL Script begin
SELECT databasename.sys.indexes.name as "Index Name", index_type_desc as "Index Type", avg_fragmentation_in_percent as "Percent Fragmented", fragment_count as "Fragment Count", page_count as "Page Count"
FROM master.sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED')
JOIN databasename.sys.indexes
on master.sys.dm_db_index_physical_stats.object_id=databasename.sys.indexes.object_id
where database_id = (select database_id from sys.databases where name = 'databasename')
and page_count > 10
order by avg_fragmentation_in_percent desc
--SQL Script end



An example for JDE_PRODUCTION:

--SQL Script begin
SELECT jde_production.sys.indexes.name as "Index Name", index_type_desc as "Index Type", avg_fragmentation_in_percent as "Percent Fragmented", fragment_count as "Fragment Count", page_count as "Page Count"
FROM master.sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
JOIN jde_production.sys.indexes
on master.sys.dm_db_index_physical_stats.object_id=jde_production.sys.indexes.object_id
--where database_id = (select database_id from sys.databases where name = 'jde_production')
and page_count > 10
order by avg_fragmentation_in_percent desc
--SQL Script end


Script file for queries used in this article is here:
http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/Fragmentationinallindexesinalltables.sql?attredirects=0

All SQL script files are here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts