When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status, r.wait_time,wait_type,r.wait_resource, substring(qt.text,(r.statement_start_offset/2) +1, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as stmt_executing,r.blocking_session_id,...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2007/08/29/1780.aspx