Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 cpu at 100%


SQL Server 2005 cpu at 100%

Author
Message
StillaLearner
StillaLearner
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 550
Hello - I would like some help please on an issue I am having on a site. I have found a previous thread (http://www.sqlservercentral.com/Forums/Topic1165211-145-2.aspx).
I have managed to run the queries request here and have confirmed that I am also experiencing the same symptoms

First some brief facts

SQL Server 2005 9.0.4035 - yes I am 1 SP behind - plans underway to patch
max_server_memory set to 26624 MB (26GB)

Windows Server 2008 R2 Datacenter x64
4 cpus
physical RAM is 29GB

I have identified a number of high cpu queries and dealing with those presently - change control Smile
I have regular maintenance underway twice weekly optimizes being run

I have run Ninja's queries and the results are in the attached jpg - sorry can't figure out how to roll txt into the post.

Could someone please confirm that upping the cost threshold higher than 5 as suggested by Grant would help me - along with the query optimization being planned. Right at the moment my task manager is showing me the cpu utilisation and it looks like someone is lying on one of those poly graph tests Sad

Any suggestions would be gratefully received

thanks
john
Attachments
cxpack.JPG (13 views, 50.00 KB)
para.JPG (12 views, 19.00 KB)
George M Parker
George M Parker
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1469
john pan (10/31/2012)
Hello - I would like some help please on an issue I am having on a site. I have found a previous thread (http://www.sqlservercentral.com/Forums/Topic1165211-145-2.aspx).
I have managed to run the queries request here and have confirmed that I am also experiencing the same symptoms

First some brief facts

SQL Server 2005 9.0.4035 - yes I am 1 SP behind - plans underway to patch
max_server_memory set to 26624 MB (26GB)

Windows Server 2008 R2 Datacenter x64
4 cpus
physical RAM is 29GB

I have identified a number of high cpu queries and dealing with those presently - change control Smile
I have regular maintenance underway twice weekly optimizes being run

I have run Ninja's queries and the results are in the attached jpg - sorry can't figure out how to roll txt into the post.

Could someone please confirm that upping the cost threshold higher than 5 as suggested by Grant would help me - along with the query optimization being planned. Right at the moment my task manager is showing me the cpu utilisation and it looks like someone is lying on one of those poly graph tests Sad

Any suggestions would be gratefully received

thanks
john





Increasing the value for cost threshold for parallelism "may" help but I wouldn't do that without testing it in another environment first as it can also make overall performance worse. Increasing it will reduce the number of queries that execute in parallel because only those that the query optimizer costs out above the threshold will run in parallel. But, if you have enough of those queries running now, increasing the value to 5 may cause many of them to no longer run in parallel and that can actually make performance worse. Trust me on that one, I speak from experience having changed it in a knee jerk reaction mode to large CXPACKET waits only to find that it actually did make overall performance worse.

I would look for possible table scans occurring because of missing supporting indexes, out of date distribution statistics causing poor query plans and changes to query plans in general. If you aren't already, consider automating a process that periodically captures the query plans from the DMV's to a table. You can then review it on occasion to find out which query plans have changed.

A few other other things: make sure that Windows is running in High Performance mode and not balanaced or power-saving. Consider installing CPU-Z http://www.cpuid.com/softwares/cpu-z.html, we actually found a misconfiguration setting for MAX POWER MANAGEMENT in the BIOS using this tool that was starving one of our high end servers for CPU. Review "Troubleshooting SQL Server: A Guide for the Accidental DBA" currently showing on the home page for SQLServerCentral.com. It is a great resource and one that I turn to frequently even if I'm not an Accidental DBA.
George M Parker
George M Parker
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 1469
One more thing to check, look for running profiler traces. I have seen multiple occasions where runaway profiler traces have caused high CPU usage.

select * from sys.traces
StillaLearner
StillaLearner
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 550
thanks for your reply George

select from sys.traces returns nothing - I did make sure of that earlier - i even turned of the default sql trace.
OK - i wont do anything in terms of the cost threshold.

i know there are table scans and there are lots - working on these. Stats are fresh - twice weekly - however a nightly update wouldn't hurt
I'll have a look at the cpu app and see what this turns up

thanks
john
StillaLearner
StillaLearner
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 550
ps - sorry - too many things going on here

I have set 3605,1204 and 1222 - deadlocking - seeing a lot of these and the scans - working on them also.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 7660
Initial reaction to any CXPacket problem on a global scale is that it's not the CXPacket's fault. Something is delaying one of the parallelism threads and that's the real culprit. However, I do recommend upping the baseline cost to 10-15 on any server by default, but that's simply my preference.

You've got a ton of scheduler yields but they're all short. I wouldn't sweat those. Backupthread can take forever, no biggie.

Async is off the charts. Your app or network appears to jam up on occassion. More information on that one: http://blog.sqlauthority.com/2011/02/11/sql-server-async_io_completion-wait-type-day-11-of-28/

After that you don't have any particularly significant waits.

So, I wouldn't tackle this directly from the Server. What else runs on this box? IIS? Reporting Services? SSIS?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
StillaLearner
StillaLearner
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 550
Hi Craig - thanks for your reply - I will have a look at this also

i'm going to fail this sql instance back to it's home node which has more memory and I will be increasing the max_server_memory tomorrow if I see this as having a problem
I will start looking at other issues also

We'll see what tomorrow brings

thanks
StillaLearner
StillaLearner
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 550
just an update for those interested:

failed over SQL instance to passive node and up the SQL Max_server_memory to 36GB - showing less deadlocking and the WEB UI is responding quicker than I have seen it lately

So While i am trying to sort out the queries (table scans and deadlocking) we have some breathing space. Have more information.

1) this is a virtual cluster
2) HBA set to default - have asked for this to be increased
3) have VOIP on the same subnet as data
4) 1GB iSCSI SAN - don't know how the luns are made up likely to be RAID6 with high IOPS

Question: regardless of the RAID level if the IOPS are high would this be better than having a mixture of RAID 1 and RAID 5 on fast SAS disks?

thanks
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
john pan (10/31/2012)
I have set 3605,1204 and 1222 - deadlocking - seeing a lot of these and the scans - working on them also.


Turn 3605 off, it has nothing to do with deadlocks. Turn 1204 off, it produces the old-style SQL 2000 deadlock graph that's a pain to read.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


paul.phillips
paul.phillips
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 50
Just a thought - and it may not apply in your case - but could the issues be caused by parallelism and/or your isolation level?

Our OLTP ran into CX Packet issues and we resolved most of them by restricting tasks to one processor (max degree of parallelism : 1). We also enabled Read Committed Snapshot.

As always - I heavily recommend testing before applying them to any production system.

HTH

Paul
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search