Find Long running queries in SQL2005

The following query shows you the long running queries in the database by combination of few dynamic management views. It’s quite useful to get the “bottleneck” on-the-fly when your system is super busy.

Select dmText.text as 'Executed Query',dmStats.last_execution_time as 'Last Executed Time', dmstats.*
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
total_elapsed_time desc,
dmStats.last_execution_time desc