SQL Server 2005 cpu at 100%

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 General Discussion » SQL Server 2005 cpu at 100%

Add to briefcase

SQL Server 2005 cpu at 100%

Expand / Collapse

Author

Message

Posted Wednesday, October 31, 2012 2:14 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:36 PM
Points: 66, Visits: 527

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

Sad

Any suggestions would be gratefully received

thanks
john

Post Attachments

cxpack.JPG (12 views, 50.39 KB)

para.JPG (12 views, 19.73 KB)

Post #1379522

George M Parker

Posted Wednesday, October 31, 2012 2:51 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

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

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.

Post #1379537

George M Parker

Posted Wednesday, October 31, 2012 2:59 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

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

Post #1379543

Posted Wednesday, October 31, 2012 3:22 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:36 PM
Points: 66, Visits: 527

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

Post #1379548

Posted Wednesday, October 31, 2012 3:25 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:36 PM
Points: 66, Visits: 527

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.

Post #1379549

Posted Wednesday, October 31, 2012 3:38 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 5,672, Visits: 6,115

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

Post #1379552

Posted Wednesday, October 31, 2012 8:03 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:36 PM
Points: 66, Visits: 527

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

Post #1379586

Posted Thursday, November 01, 2012 4:48 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:36 PM
Points: 66, Visits: 527

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

Post #1380084

Posted Friday, November 02, 2012 2:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 37,692, Visits: 29,951

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 2008, MVP
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

Post #1380190

Posted Friday, November 09, 2012 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:05 AM
Points: 5, Visits: 41

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

Post #1383053

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse