• 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.