How to get Long running queries from sql database

How to get Long running queries from sql database

Just open query analyzer and then use current database then run the following query to get desired result.

 SELECT  
 creation_time  
 ,last_execution_time  
 ,total_physical_reads  
 ,total_logical_reads  
 ,total_logical_writes  
 , execution_count  
 , total_worker_time  
 , total_elapsed_time  
 , total_elapsed_time / execution_count avg_elapsed_time  
 ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
 ((CASE statement_end_offset  
 WHEN -1 THEN DATALENGTH(st.text)  
 ELSE qs.statement_end_offset END  
 - qs.statement_start_offset)/2) + 1) AS statement_text  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
 where total_elapsed_time >= 300000000 --5 min  
 ORDER BY total_elapsed_time / execution_count DESC;