Most usableDMV in SQL SERVER 2008/2005

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

sys.dm_exec_query_stats

sys.dm_os_wait_stats

sys.dm_db_index_usage_stats

sys.dm_db_index_operational_stats

sys.dm_db_missing_index_details

Please take a close look on all above dmv's