High CPU usage - Who?!
-
What is the best way to find who (particular User or Appplication) is the reason for current high CPU usage? Read about sp_who2 and sysprocesses table but frankly didn't get how to answer my question. Thanks
-
That's kind of a tough one... sp_who2 which just pulls the info from sysprocess reports on cpu usage, but it is cumulative, so it can't really tell you which process is currently using CPU...
You might have better luck by specifying the Active parameter, like this:
EXEC sp_who2 'active'
Which returns only thos processes that are currently doing something. Looking at the CPU and IO utilization does give you somewhat of a hint, and then you can use the DBCC INPUTBUFFER(spid) to see that last command that was executed.
You can get a lot more information by running a trace through SQL Profiler. You can even get execution plans, but if your server is already stressed, running Profiler could bring it to its knees...
I would suggest running profiler for a short period to gather the sql statements, spid, and cpu time. Look for the most expensive and most frequently run statements and see what you can do to optimize them. As you optimize these statements you should start to see improvement. This is an iterative and ongoing process for most systems.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
If you want to know which applications are consuming the processor time, You can use Task Manager or Performance monitor monitor it.
If you are sure the high CPU usage is caused bu SQL Server process, Follow the Real DBA's suggestions.
-
Thanks, guys. But, CPU will give you an idea how long this thread (process id) is running and can't confirm that it is a real current CPU usage issue (probably I need something like CPU speed increase?!). Or I am wrong? For example, I see 3 processes with biggest CPU and IO. And what now? Login time was 5 days ago, last batch start was recently. How can I be sure that these 3 particular processes create CPU problem? Thanks
-
See my post above... Run Profiler to capture each statement run with its associated CPU and IO cost.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks, hope I got you.
-
You could also use sysprocesses for a quick ref.
This is a system-table, NOT to be modified/updated by anyone but the system !!
But you can use it for consumption-detection :
-- In this case I'm searching for waittypes
if object_id('tempdb..#ALZ_sysprocesses') is null
begin
set nocount on
select *
into #ALZ_sysprocesses
from master.dbo.sysprocesses
print 'Werktabel aangemaakt. (' + cast(@@rowcount as varchar(10)) + ' rijen)'
set nocount off
end
select a.spid, a.lastwaittype, p.lastwaittype, a.waittime, p.waittime
, db_name(p.dbid) as P_DbName
, p.physical_io
, p.memusage
, p.cpu
, p.last_batch
, p.status
, p.hostname
, p.cmd
, rtrim(p.nt_domain) + '\' + rtrim(p.nt_username)
, p.loginame
from #ALZ_sysprocesses A
inner join master.dbo.sysprocesses P
on A.spid = P.spid
and a.lastwaittype = P.lastwaittype
--where A.spid = 65
order by p.physical_io desc
-- drop table #ALZ_sysprocesses
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Regarding sysprocesses table- could you, please, explain what status "runnable" means (not "running")? For example, I just came to my office and found that: 1)CPU usage is almost 100% (checked- it's Sql server) but 2)we don't have any single process with status "running" and 3) whole bunch with status "runnable". What does it mean? Thanks
-
As you might have found out, there is litle info regarding sysprocesses available on the web. (many using it, but none describing it)
So I fallback to sybase :
This is what it says :
Status
Meaning
alarm sleep
Waiting for alarm to wake process up (user executed a waitfor delay command)
background
A process, such as a threshold procedure, run by Adaptive Server rather than by a user process
infected
Server has detected a serious error condition; extremely rare
latch sleep
Waiting on a latch acquisition
lock sleep
Waiting on a lock acquisition
PLC sleep
Waiting to access a user log cache
recv sleep
Waiting on a network read
runnable
In the queue of runnable processes
running
Actively running on one of the server engines
send sleep
Waiting on a network send
sleeping
Waiting on a disk I/O, or some other resource (often indicates a process that is running, but doing extensive disk I/O)
stopped
Stopped process
sync sleep
Waiting on a synchronization message from another process in the family
I think this may be a good start for directions
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks
-
Guess what : I found this litle article ...
The waittype and lastwaittype columns in the sysprocesses table in SQL Server 2000
available at http://support.microsoft.com/default.aspx?scid=kb;en-us;822101
This did ring a bell
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply