I put together this query on SQL Server 2008 to help me organize the fragmented indexes and thus get me pointed in the right direction for troubleshooting.
SELECT dbps.database_id, dbps.OBJECT_ID, dbps.index_id, sysi.name,
dbps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbps
INNER JOIN sys.indexes AS sysi ON dbps.OBJECT_ID = sysi.OBJECT_ID
AND dbps.index_id = sysi.index_id
WHERE dbps.database_id = DB_ID()
AND dbps.index_id > 0
ORDER BY dbps.avg_fragmentation_in_percent DESC
GO
Many of our systems automatically defrag in overnight tasks, but when things stop working, this helps me get systems back up and running.