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.

1
  • 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
    Commented Aug 4, 2011 at 9:30

3 Answers 3

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.

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.

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .