InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 00:53:27 GMT20http://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxJust 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.HTHPaulFri, 09 Nov 2012 08:34:21 GMTpaul.phillipshttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspx[quote][b]john pan (10/31/2012)[/b][hr]I have set 3605,1204 and 1222 - deadlocking - seeing a lot of these and the scans - working on them also.[/quote]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.Fri, 02 Nov 2012 02:28:20 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxjust 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 latelySo 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 cluster2) HBA set to default - have asked for this to be increased3) have VOIP on the same subnet as data4) 1GB iSCSI SAN - don't know how the luns are made up likely to be RAID6 with high IOPSQuestion: 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?thanksThu, 01 Nov 2012 16:48:57 GMTjohn panhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxHi Craig - thanks for your reply - I will have a look at this alsoi'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 problemI will start looking at other issues alsoWe'll see what tomorrow bringsthanksWed, 31 Oct 2012 20:03:41 GMTjohn panhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxInitial 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: [url]http://blog.sqlauthority.com/2011/02/11/sql-server-async_io_completion-wait-type-day-11-of-28/[/url]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?Wed, 31 Oct 2012 15:38:58 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxps - sorry - too many things going on hereI have set 3605,1204 and 1222 - deadlocking - seeing a lot of these and the scans - working on them also.Wed, 31 Oct 2012 15:25:30 GMTjohn panhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxthanks for your reply Georgeselect 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 hurtI'll have a look at the cpu app and see what this turns upthanksjohnWed, 31 Oct 2012 15:22:51 GMTjohn panhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxOne 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.tracesWed, 31 Oct 2012 14:59:25 GMTGeorge M Parkerhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspx[quote][b]john pan (10/31/2012)[/b][hr]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 factsSQL Server 2005 9.0.4035 - yes I am 1 SP behind - plans underway to patchmax_server_memory set to 26624 MB (26GB)Windows Server 2008 R2 Datacenter x644 cpusphysical RAM is 29GBI have identified a number of high cpu queries and dealing with those presently - change control :)I have regular maintenance underway twice weekly optimizes being runI 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 receivedthanksjohn[/quote]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.Wed, 31 Oct 2012 14:51:52 GMTGeorge M Parkerhttp://www.sqlservercentral.com/Forums/Topic1379522-149-1.aspxHello - 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 factsSQL Server 2005 9.0.4035 - yes I am 1 SP behind - plans underway to patchmax_server_memory set to 26624 MB (26GB)Windows Server 2008 R2 Datacenter x644 cpusphysical RAM is 29GBI have identified a number of high cpu queries and dealing with those presently - change control :)I have regular maintenance underway twice weekly optimizes being runI 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 receivedthanksjohnWed, 31 Oct 2012 14:14:07 GMTjohn pan