SQL Server Performance Forum – Threads Archive

Cpu usage 100 % in sqlserver 2005,windows 2000 ser

hi, I am new to this issue, and i am not sure how to track down , which is occupying and making the use of CPu 100%. Can any of you tell be the basic things i have to go thru’ to resolve this issue..

USe the command sp_who2 active and identify the spids sucking up cpu. Or you can also query top 10 sysprocesses and order by cpu desc. once you have the spid, use DBCC INPUBUFFER(spid) to identify what the spid is doing. After that you need to look into the T-SQl or the proc and tune it. ***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/

Use sql profiler to see what procedures using high cpu and recompilations…
High recompiles also cause high CPU…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU Use sql profiler to see what procedures using high cpu and recompilations… High recompiles also cause high CPU… MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy. <hr height="1" noshade id="quote"></blockquote id="quote"> Mr M. If CPU is already chugging along 100%, using profiler on production server will kill the server.. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] *********************** Dinakar Nethi *********************** http://weblogs.sqlteam.com/dinakar/</a>

I know [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Not always… it depends how much load your putting on the server… You can try to put the filter and use procedures to grab only high cpu queries and don’t run for long time… run for couple of seconds/minutes… And also I believe you can use a report from SSMS to get high cpu queries…. MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I agree with DInaker in this case and do not and never allow running PROFILER when the server is really stressed out on resources [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], in this cse running DMVs or even server side trace will help a lot to get such information. Priya For the discussion on High cpu refer to<a target="_blank" href=http://sqlserver-qa.net/blogs/perftune/archive/2007/06/18/sql-server-2005-high-cpu-occurrence-why-it-is.aspx>http://sqlserver-qa.net/blogs/perftune/archive/2007/06/18/sql-server-2005-high-cpu-occurrence-why-it-is.aspx</a> Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> @<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>