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.

  • 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 4 '11 at 9:30
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.