SQL Server Performance Forum – Threads Archive

database server 100% cpu usage

Gents: I just encountered one problem with our db server, which has been with 100% cpu usage all day for a few days. Before It was busy with 80% cpu usage avarage, but not 100% all the time. After I carefully checked this server, it shouldn’t be caused by virus problem.
some proc or application t-sql code wasn’t written properly but definitely they hadn’t been changed before the cpu usage greatly increased and stayed at 100%. Later, i found the sqlagent.exe process consumes quite high cpu usage as well, the fact is when sqlserver.exe release cpu time, the sqlagent.exe will take most of the cpu time, average about 15-20% all the time. I checked some other servers, but found sqlagent shouldn’t take cpu usage all the time. Pls give me some advice if you could, I have really no idea what can cause sqlagent.exe consumes high cpu time all the time, appreciate your help. thank you very much!

---

Just some more extra information: When I noticed the 100% cpu usage, I restarted the sqlagent service and the cpu used by sql agent will be down to 1% or 0%, but it will increase slowly and take all cpu resource besides sqlserver.exe. thanks,

---

Hi,
have you patched your sql server with SP3 and lattest fixs, another issue is if your t-sql statement / batch contain highly calculated values , too much data and without proper locking hint / re – organize index, would cause this issue.or any transaction that takes more time to review dbcc opentran please check if you patched your server with latest fixz and sql server sp3a, and run the following dbcc perfmon
dbcc performance
pssdiag utility
DBCC DBREINDEX Please read following thread
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1955&SearchTerms=cpu,usage,100% http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1962&SearchTerms=cpu,usage,100% http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1153&SearchTerms=cpu,usage,100% http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=242&SearchTerms=cpu,usage,100%
Regards. hsGoswami
[email protected]

---

In addition to what said above, as you say when you restart SQLAgent the CPU resources are back to their levels, then I suggest to look under network side for any issues as one of jobs might have problem while executing process. The latest service pack on SQL 2000 is SP4, so test it before deploying on production server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

thanks for your information. sp3 was appatched three years ago, sp4 is on hold. this morning I got alerts again ( cpu 100%), after I restart sql agent, it’sgone. Normally the cpu usage will be kept 90% for one day until the sql agent cpu usage goes up again.
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1153&SearchTerms=cpu,usage,100% http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=242&SearchTerms=cpu,usage,100%
Regards. hsGoswami
[email protected]

---

you can set the profiler to trace the statements / batch that occupy more time to write for optimizion of it , their must be some batch runnig that consumps very heavy calculation and hence it takes that much of time , in past one of my reports having same issue and we optimize the query batch to achive performance ratio of cpu utilization. or run and check DBCC OPENTRAN — to review transaction which takes more time to execute / lazy writer. Regards. hsGoswami
[email protected]

---

Check the processes running during the SQLAGent peakup time. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

satya: how to check the processes? via sql trace? via profiler? via sp_who2? throught the windows task manager, can’t see any thing else use cpu except sql server, sql server agent. What I am wondering is why the sql agent consumes more and more cpu? Anywhere can I check what is using it?

---

Thanks for u r information but i am unable to resolve my Issue
Could you please help me out on this

---

[quote user="ramesh_vasam"]
Thanks for u r information but i am unable to resolve my Issue
Could you please help me out on this
[/quote]
i found the first post by danan_xu in 27 june, 2005 , i.e since more than three years.
is it not resolved all that time ?

---

Exclude this location from virus scanning ( rogram FilesMicrosoft SQL ServerMSSQLData). SQL Server data files that have the .mdf extension, the .ldf extension, and the .ndf extension.
http://support.microsoft.com/kb/309422

---

]]>