How to check which job is running cpu time up to 90% 1

[ptp491]

Hi,
We have a website that had 8 web server and 3 db servers, data is distributed on 3 server unevenly, DB1 is bigger and better server and DB2 and 3 are smaller so they handle less data and jobs.

We upgraded to SQL 2005 so we get better performance, so performance got better but cpu on only db1 is going very too high(mostly 80%), it was not like this when we upgraded them.

I was wondering if anyone knows a solution to find out what is driving cpu utilization so high.

Thanks.

 

[Catadmin]

Run PerfMon and Profiler. This should help you figure out when this is happening and which job / query is doing it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

run sp_who2 in query analyzer and check the CPUTime for each SPID. You can then take the SPID number in this code to get the statement using the CPU.

declare @handle binary(20)
select @handle = sql_handle 
from master.dbo.sysprocesses where spid = [COLOR=red]your SPID here[/color]
select * from ::fn_get_sql(@handle)

- Paul
- Database performance looks fine, it must be the Network!