Long Running Queries

A colleague sent me this to show what is currently running.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.

SELECT
    r.session_id
  , p.kpid
  , r.start_time
  , DATEDIFF(SECOND, r.start_time, GETDATE()) as elapsed_time
  , st.text
  , r.status
  , r.command
  , r.cpu_time
  , r.wait_type
  , DB_NAME(r.database_id)
  , p.hostname
  , qp.query_plan
FROM
    sys.dm_exec_requests AS r
    INNER JOIN sys.sysprocesses AS p on r.session_id = p.spid
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE session_id > 50
AND DATEDIFF(SECOND, r.start_time, GETDATE())  > 10 -- duration in seconds
ORDER BY r.start_time

From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.

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
ORDER BY total_elapsed_time / execution_count DESC;