Currently executing slow queries on SQL 2005 or greater

longestrunningqueries.sql

select  
    t1.session_id,  
    CASE WHEN t2.total_elapsed_time > 86400e3 THEN CAST(CAST(t2.total_elapsed_time/86400e3 AS DEC(5,1)) AS VARCHAR(7))+' days' ELSE SUBSTRING(CONVERT(VARCHAR(27),CONVERT(DATETIME,t2.total_elapsed_time/864e5),121),12,15) END AS elapsed,  
 --   t1.request_id,  
    t1.task_alloc, 
    t1.task_dealloc, 
 --   t2.sql_handle,  
 --   t2.statement_start_offset,  
 --   t2.statement_end_offset,  
 --   t2.plan_handle,
	substring(sql.text, statement_start_offset/2, 
		CASE WHEN statement_end_offset<1 THEN 8000 
		  ELSE (statement_end_offset-statement_start_offset)/2 
		END) AS runningSqlText,
	sql.text as FullSqlText,
	p.query_plan 
from (Select session_id,  
             request_id, 
             sum(internal_objects_alloc_page_count) as task_alloc, 
             sum (internal_objects_dealloc_page_count) as task_dealloc  
      from sys.dm_db_task_space_usage  
      group by session_id, request_id) as t1,  
      sys.dm_exec_requests as t2 
cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql 
cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p 
where t1.session_id = t2.session_id and  
     (t1.request_id = t2.request_id) 
 AND total_elapsed_time > 1000
order by t1.task_alloc DESC