CPU utilization

Question

Post reply

CPU utilization

pshrestha4

SSC Enthusiast

Points: 193
More actions
December 31, 2007 at 11:59 pm

#121524

In order to reduce the high CPU utilization, what are some of the key things that we can do other than reducing the no of users.
Thanks
Newbie

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

There are many things that can be done here.

First and foremost find out the reason why the CPU utilization is high.

Can you give us more details like your machine configuration, type of applications being run etc ..

"Keep Trying"

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

I've found that the single best improvement you can make in this area is to find and remove all hidden RBAR... "touch" each row only once, if possible, and as few times as possible if not. A good example of what I'm talking about can be found in today's headlines... the following URL is a shortcut to the article I'm referring to...

http://www.sqlservercentral.com/articles/Test+Data/61572/

You can't really reduce the percentage of CPU utilization during a run... only the amount of time that it's utilized 😉

--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)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

I usually find high cpu is down to poor queries and usually high i/o go hand in hand with high cpu. profile for high i/o queries and clock the cpu ticks, start reducing the high io queries and hopefully cpu will drop too. Don't forget to look for smaller queries which run very often - too often the tuning is applied to big queries which hit say 100k(s) io - but reducing a query which runs serveral times a second from say 100 to 12 io can also bring massive gains. You might actually need a more powerful server of course.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

What Colin and Jeff are getting at is that the CPU isn't related to the number of users. It's related to the queries and other processes. One user can run the CPU to 100% with a poorly written query. More users make this worse, but it's a sign of poorly written queries as much as load.

Of course if you have great queries, load can be a factor, but often you have the people running similar queries, so the cache (memory) and the disk systems can overload (usually) before the CPU.

Tune the queries as suggested above, read articles on how to do that, and also trace the activity and try to determine what is causing the CPU to peg.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Heh... if it's a very well written query, it will always run the CPU to 100%... just for a very, very short time... and that's the goal... get everything done as quickly as possible (find and repair the queries Colin was talking about) and get out... The math is simple... the longer a query runs, the more CPU time (and resources) it's going to take (unless it's all WAITFOR command like a "delete crawler") 😉

--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)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

I have queries which will extract highest cpu queries from the dmvs I'll perhaps see if I can dig them out.

There is an answer of course - the sql 2005 performance dashboard.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/