4

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?

Torben Warberg Rohde
  • 471
  • 1
  • 6
  • 15
  • Have a look at [SQL Server profiler](http://msdn.microsoft.com/en-us/library/ms187929.aspx) – Andomar Jun 16 '11 at 11:14
  • Thanks for all the suggestions. Unfortunately I don't have time to try it out at the moment. But I will get back and post results when I do. I'll probably end up using the suggestion of running the profiler with the "log to table" setting and matching the timestamps of the CPU-spikes from the perfmon logging. – Torben Warberg Rohde Jun 21 '11 at 10:58

4 Answers4

7

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);
Jimbo
  • 2,529
  • 19
  • 22
2

Run a profiler trace logging the database name and cpu during a spike, load the data up into a table, count and group on db.

select DatabaseName, sum(CPU) from Trace 
group by DatabaseName
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
1

Have a look at sys.dm_exec_query_stats. The total_worker_time column is a measure of CPU. You may be able to accomplish what you're trying to do in one look at the view. You may, however, need to come up with a process to take "snapshots" of the view and compare successive snapshots. That is, look at the data in the view and compare it to five minutes later and compare the differences. The differences will the the amount of resources consumed between the two snapshots. Good luck!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
0

Have you tried relating SQL Server Profiler to Performance Monitor? When you correlate the data, you can see spikes in performance related to DB activity. http://www.sqlservernation.com/home/relating-sql-server-profiler-with-performance-monitor.html

kg.
  • 31
  • 3