i use a variant of this to find procedures that take longer than 15 seconds, tune them one by one, and then lower my threshold until it's a diminishing return scenario(takes an hour of scripting and testing to save a few seconds);
start with the slowest and work your way through them.
this is getting the plans from the DMV's, for the CURRENT database, so get out of master, and in the db in question:
DECLARE @seconds INT = 15;
SELECT 'Purpose: Show Any queries taking longer than '
+ CONVERT(VARCHAR, @seconds) + ' seconds.' AS notes;
DECLARE @ServerRestartedDate VARCHAR(30);
SELECT @ServerRestartedDate = CONVERT(VARCHAR(30),dbz.create_date,120) FROM sys.databases dbz WHERE name='tempdb';
--DECLARE @seconds INT = 15
SELECT @ServerRestartedDate AS ServerRestartedDate,
st.last_execution_time AS LastExecutionTime,
st.execution_count AS TotalExecutions,
(st.last_elapsed_time / 1000000 ) AS LastElapsedSeconds,
(st.max_elapsed_time / 1000000 ) AS MaxElapsedSeconds,
DB_NAME(fn.dbid) AS DBName,
Object_schema_name(fn.objectid, fn.dbid) AS SchemaName,
OBJECT_NAME(fn.objectid, fn.dbid) AS ObjectName,
fn.*,
st.*
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.[sql_handle]) fn
WHERE 1=1
AND st.last_elapsed_time >= ( 1000000 * @seconds ) --15 seconds default
AND (DB_NAME(fn.dbid) =DB_NAME() )
AND OBJECT_NAME(fn.objectid, fn.dbid) IS NOT NULL -- object name = proc/function name
ORDER BY st.execution_count DESC;
Lowell