Determining which SQL Server database is spiking the CPU

Question

We are running SQL Server 2008 with currently around 50 databases of varying size and workload. Occasionally SQL Server spikes the CPU completely for about a minute, after which it drops to normal baseline load.

My problem is that I can't determine which database or connection is causing it (I'm fairly sure it is one specific query that is missing an index - or something like that). I have found T-SQL queries that gives you a frozen image of current processes. There are also the "recent expensive queries" view and of course the profiler, but it is hard to map to a "this is the database that is causing it" answer. What makes it even harder is that the problem disappears before I have even fired up the profiler or activity monitor, and it only happens about once or twice a day.

Ideally I would like to use a performance counter so I could simply run it for a day or two and then take a look at what caused the spikes. I can however not find any relevant counter.

Any suggestions?

Answer 1

This will help, courtesy of Glenn Berry adapted from Robert Pearl:

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);