December 31, 2007 at 11:59 pm
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
January 1, 2008 at 1:13 am
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"
January 1, 2008 at 8:48 am
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
Change is inevitable... Change for the better is not.
January 1, 2008 at 10:39 am
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/
January 1, 2008 at 11:52 am
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.
January 1, 2008 at 12:13 pm
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
Change is inevitable... Change for the better is not.
January 1, 2008 at 12:48 pm
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply