| Author |
Topic |
|
ckleong
Starting Member
17 Posts |
 Posted - 2009-04-09 : 22:10:41
|
| Hi,I have a 2005 instance running on four intel xeon 3.4Ghz quad core processors.This instance is mainly for sharepoint db's. Recently the CPU utilization hit up to 75 - 90% and causing some performance slowness problem. How do we justify it is a CPU bottleneck? Do i need to upgrade or add more processors to reduce the utilization? Please adviseRick |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-09 : 22:23:15
|
| First get all queries wit CPU consumption from Standard reports. See Execution plans if indexes are missing . |
 |
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-09 : 22:28:14
|
| Hi,How to get all queries from Standard report? Do i need to run perfmon? if yes, please advise which counters to use and what is the recommended value? |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-04-10 : 04:36:33
|
| The Standard Reports are available in Management Studio.Open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”. There are some additional Performance reports (SP2) that may also help. |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-04-10 : 09:43:54
|
| >> all queries wit CPU consumption from Standard reportsSodeep, when I right click on the standard reports I do not see anything like CPU utilization in the menu...there are reports for disk usage etc.I am missing something? |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-04-10 : 10:42:38
|
| Recently, you noticed an increase in CPU utilization. A few questions before jumping on conclusion:1) What CPU utilization you observed before?2) Any changes/updates to Sharepoint?3) Any updates to Windows since you started noticing this behavior? 4) What is the size of Sharepoint database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-12 : 02:11:41
|
| Hi,just wondering why SQLserv.exe consumed so much of CPU ulitilization. Is there anyway to check? To answer saurabhsrivastava 1) What CPU utilization you observed before? It's stall between 20-30% during peak hour and 5-10% during off-peak2) Any changes/updates to Sharepoint? No, but recently add few more DB's into the same instance. 3) Any updates to Windows since you started noticing this behavior? No4) What is the size of Sharepoint database? 30GB++ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-12 : 12:53:01
|
quote:
Originally posted by ckleong
Hi,just wondering why SQLserv.exe consumed so much of CPU ulitilization. Is there anyway to check? To answer saurabhsrivastava 1) What CPU utilization you observed before? It's stall between 20-30% during peak hour and 5-10% during off-peak2) Any changes/updates to Sharepoint? No, but recently add few more DB's into the same instance. 3) Any updates to Windows since you started noticing this behavior? No4) What is the size of Sharepoint database? 30GB++
Did you read Tara's Suggestion? Most of times you can solve with that . |
|
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-13 : 11:51:54
|
| Hi Sodeep,Yes, i have confirmed there is no unused index and missing index in my server.What is the optimum value to set for MaxDOP if my server is running on 4 quad core processors?I also found out Context Switches/Sec giving very high figures (between 15000 - 42000)should i turn on the Lightweight Pooling? Is there any impact to existing sharepoint db's? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-13 : 12:37:48
|
quote:
Originally posted by ckleong
Hi Sodeep,Yes, i have confirmed there is no unused index and missing index in my server.What is the optimum value to set for MaxDOP if my server is running on 4 quad core processors?I also found out Context Switches/Sec giving very high figures (between 15000 - 42000)should i turn on the Lightweight Pooling? Is there any impact to existing sharepoint db's?
But do you get CPU intensive queries with standard reports?See Execution plans. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-04-13 : 13:30:47
|
| High context switching. OK. How is you tempdb look like? DO you see high tempdb activity?If yes, have you created multiple tempdb files of equal size or it's running on default. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-04-13 : 13:33:29
|
| see if this is applicable on your system: http://support.microsoft.com/kb/931821 |
|
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-13 : 21:43:00
|
| Hi Saurabhsrivastava,tempdb look fine and no high usage. For the link that you are mentioned, that is not applicable cause the server already run on SP2. hi Tara,I have mod the Maxdop to 8 and it's help to reduce the utilization. Now, it will not stall at 75-90% for more than a minutes, but achocly it will still hit for 10 - 20 seconds. Can i safely say the high cpu utilization problem is cleared?Hi Sodeep,I have extract the reports and i do see few query running at very high cpu time. I will forwarded to application team to verified again. Thanks for highlight |
|
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-13 : 23:08:43
|
| Hi Team,can you please clarify why high context switching is ok? i thought this will directly impact the CPU utilization. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-13 : 23:41:57
|
quote:
Originally posted by ckleong
hi Tara,I have mod the Maxdop to 8 and it's help to reduce the utilization. Now, it will not stall at 75-90% for more than a minutes, but achocly it will still hit for 10 - 20 seconds. Can i safely say the high cpu utilization problem is cleared?
You'll need to watch the system. Remember that spikes in the graph are normal. It's when it flatlines that it's bad.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
ckleong
Starting Member
17 Posts |
Posted - 2009-04-14 : 08:34:49
|
| Thanks mate.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
CPU utilization remain at 75-90%
Tara Kizer