Big Queries

Have you ever wanted to know which queries are consuming the most resource on your server? There is a quick and easy way to do this without starting up SQL Profiler. Just execute the following SQL query.
select top 50
execution_count,
total_physical_reads,
total_logical_reads,
total_elapsed_time/1000 total_elapsed_time,
total_elapsed_time/(1000*execution_count) Avg_Execution_Time,
text
--,*
From Sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)
order by total_elapsed_time desc

This query will list out the top 50 queries from you plan cache and show how much resource they have consumed since they have been in the cache. Note, this is not an accurate way of measuring total resource consumption, since it depends on what and how long plans have been in the cache. However, for a first cut tuning exercise, it is very helpful. If you are looking at overall SQL Server (or machine) activity, they it doesn't matter if a small query is running inefficiently, if it executes infrequently. The query above, provides total_elapsed_time, which is a good indication of which queries (in total) are consuming the most resource.

What I would do in the first instance is copy the text from the query and run an explain, possibly with an execution, and look for tuning opportunities. One of my mottos is that anything can have its performance improved, it's only a matter of how much effort is required.