From SQL Server 2008 R2 Diagnostic Information Queries by Glenn Berry[/url] you can grab a quick view of your highest CPU procs:

Find highest CPU use by DB:

-- Get CPU utilization by database (adapted from Robert Pearl) (Query 17)

WITH DB_CPU_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS F_DB

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],

DatabaseName, [CPU_Time_Ms],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]

FROM DB_CPU_Stats

WHERE DatabaseID > 4 -- system databases

AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

-- Helps determine which database is using the most CPU resources on the instance

PS Forgot the one that shows the highest procs 🙂

USE TheDBFromLastCheck;

GO

-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost (Query 38)

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective

-- You should look at this if you see signs of CPU pressure