find current CPU usage

Question

Post reply

find current CPU usage

Mad-Dog

SSCertifiable

Points: 6117
More actions
August 6, 2009 at 7:17 am

#209056

Hi,
my goal here is to capture all the queries that run now that cause the CPU to become over 90%.
my CPU get to 90-100 a lot of time.
so i thought to run an alert that check if the CPU is grater then 80% do something.
the problem is that i don't know how to check for the current CPU performance status from within the sql server.
can i do that from an Alert?
can i do that from an sql agent job?
can i do that from an xp_cmdshell?
or any thing else?
THX

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

CPU usage script can be found by reading ssc thread: http://www.sqlservercentral.com/Forums/Topic611107-146-1.aspx

Have also attached some scripts that may help your cause. 😉

Enjoy.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

thanks.

this script is nice but it is not give the the current (live) CPU utilization, and i can't rely on it.

anyone have something else that give me the current CPU utilization i live?

THX

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

Schadenfreude-Mei (8/6/2009)
CPU usage script can be found by reading ssc thread: http://www.sqlservercentral.com/Forums/Topic611107-146-1.aspx
Have also attached some scripts that may help your cause. 😉
Enjoy.

Good scripts Adam. Thanks.

SQL DBA.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

SCC article on performance monitor[/url] by our very own Steve Jones.

Can also check out this for setting up Performance tool in Windows: http://support.microsoft.com/kb/324752

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 5

SanjayAttray (8/6/2009)
Schadenfreude-Mei (8/6/2009)
CPU usage script can be found by reading ssc thread: http://www.sqlservercentral.com/Forums/Topic611107-146-1.aspx
Have also attached some scripts that may help your cause. 😉
Enjoy.
Good scripts Adam. Thanks.

Your completely welcome. :blush:

Adam Zacks-------------------------------------------Be Nice, Or Leave

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

Mad-Dog (8/6/2009)
Hi,
my goal here is to capture all the queries that run now that cause the CPU to become over 90%.
my CPU get to 90-100 a lot of time.
so i thought to run an alert that check if the CPU is grater then 80% do something.
the problem is that i don't know how to check for the current CPU performance status from within the sql server.
can i do that from an Alert?
can i do that from an sql agent job?
can i do that from an xp_cmdshell?
or any thing else?
THX

The alert isn't really going to do anything for you. You already know it hits the wall. Do a search for how to find long running queries. Even that may not do it for you, though. It might not be one or two queries... it could be dozens of small queries during busy times.

My recommendation would be to setup profiler for rpc and batch completes with an initial filter so nothing under one second (1000 ms) is reported to start with. That will find the long running queries if there are any. If none show up, then the search for which short queries are run the most becomes a tiny bit more difficult but is certainly possible. Neither situation should cause much of a load on the server if done properly.

In 2k5, there's a canned report that shows you the longest running queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

praneethpalla Valued Member Points: 55 More actions · Answer 7

Hi

I am facing a similar issue. Any Concrete solution.

Shiva

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

No... there is no concrete solution because there is no concrete source to the problem. There are many things (usually bad code either from the GUI's or in stored procs) than can cause such wild CPU usage. Step 1 would be to setup the Profiler as I suggested and capture the output either to a trace file or a table for further analysis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)