I prefer to use information from system view sys.dm_exec_query_stats. Activity monitor is inconvenient for me.
To get information from
sys.dm_exec_query_stats I use following script which I found here (unfortunatly it is in Russian). Works for SQL Server 2005+.
Using this script you can filter or group queries by the fields you want and, what is more important, get information about last execution time and object (SP, trigger) to which the query belong (Activity Monitor does not provide this information).
The only disadvantage of the script is that it returns aggregate performance statistics for cached query plans only. If a plan is removed from cache by any reason (shortage of memory, recompilation) you will not see statistics for that query.
set transaction isolation level read uncommitted
total_worker_time/1000 as CPU,
convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
qs.total_elapsed_time/1000 as TotDuration,
convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
total_logical_reads as [Reads],
total_logical_writes as [Writes],
total_logical_reads+total_logical_writes as [AggIO],
convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
when sql_handle IS NULL then ' '
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
end - qs.statement_start_offset)/2 ))
end as query_text,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AvgDur desc