1

I have a fairly powerful database server having SQL Server 2008 R2 installed. There is only one database on it which is being accessed from 2 servers (around 5/6 applications). The problem is as soon as applications start pointing to the database, system's CPU usage goes upto 100% with sqlserver itself using 95+%.

I have checked profiler, there aren't any heavy queries running there. I have checked active connections, they are hardly 150. Still CPU usage is around 100% and applications are experiencing slow response/connection to database server are getting refused.

Database grus, I really need some ideas here.

MDMarra
  • 100,734
  • 32
  • 197
  • 329
Ali
  • 111
  • 3
  • How could you tell that they aren't "heavy"? Which is the one with the biggest cost? Which is the one with the biggest CPU usage? – mailq Aug 04 '11 at 09:30

3 Answers3

1

I'd also take a look at sys.dm_os_wait_stats to see what the server is waiting on.

If a few queries are really pegging the CPU like that, I'd suspect lots of parallel execution. It's something to look for.

Grant Fritchey
  • 121
  • 3
0

When you look at profiler there should be a CPU column. The queries which have the highest value in the column are the ones which are using a lot of CPU time. Also go find sp_whoisactive and put it on the machine. When you run this it'll show you the queries which are taking a lot of time and CPU to run while they are running.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
0

Are you doing regular maintenance on the database like backups, update statistics, index rebuilds? I have seen a heavily fragmented database cause performance issues like you are describing.

Jason Cumberland
  • 1,579
  • 10
  • 13