Cacher is the code snippet organizer for pro developers

select top 100
qs.max_elapsed_time/1000000.00,
db.name db,
ob.name object,
substring(st.text,1,100) preview,
total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text

, qs.*
 from sys.dm_Exec_query_stats qs
cross apply sys.dm_Exec_sql_text(qs.sql_handle) as st
left join sys.databases db on db.database_id=st.dbid
left join sys.objects ob on ob.object_id = st.objectid
where st.text not like '%select top 100%'
order by qs.max_elapsed_time desc