October 31, 2012 at 2:14 pm
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 🙁
Any suggestions would be gratefully received
thanks
john
October 31, 2012 at 2:51 pm
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 🙁
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.
October 31, 2012 at 2:59 pm
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
October 31, 2012 at 3:22 pm
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
October 31, 2012 at 3:25 pm
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.
October 31, 2012 at 3:38 pm
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2012 at 8:03 pm
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
November 1, 2012 at 4:48 pm
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
November 2, 2012 at 2:28 am
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
November 9, 2012 at 8:34 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply