Some nice code to be used when we investigate performance problems on sql server 20005.

-- Gives info regarding how the indexes have been used, seeks, scan, updates etc.
select * from sys.dm_db_index_usage_stats

-- Gives info about e.g. fragmentation, type, depth etc
SELECT * FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

-- gives wait times info for locks, latches, io etc.
select * from sys.dm_os_wait_stats

-- Clear the info so we can test again after changes
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

-- Gives info about how queries was executed, min and max writes, read elapsed time, clr time etc..
select * from sys.dm_exec_query_stats

There are several other interesting system views on sql server 2005 for many other situations.