In oracle there is a built in report which identifies the most costly queries for that "tuning" can take place for those queries and therefore speed up the database.
I'm looking for something similiar in SQL Server 2005/2008 but have only been able to find "instant" based answers (only tell which are the most costly at that moment in time as opposed to most oft used/costly).
This will give you the average running time for the top 25 slowest processes
SELECT TOP 25 DB_NAME(st.dbid) AS 'Database name',
execution_count AS 'Execution count',
(total_worker_time) / qs.execution_count * 2 AS 'Average running time (Microseconds)'
, (total_worker_time/1000000) / qs.execution_count AS 'Average running time (Seconds)'
, [text] AS 'Command' FROM master.sys.dm_exec_query_stats as qs
CROSS APPLY master.sys.dm_exec_sql_text(qs.sql_handle)
st ORDER BY (total_worker_time) / qs.execution_count desc
Free SQL server monitoring for DBA's SQL DBA manager by BlueThames.com