Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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