Post reply

MAX DOP and Threshold

  • February 17, 2005 at 7:00 am

    #110100

    Hello all:

    I am considering a change to my configuration due to 'inherited' poorly written code.  (We've all got this, right???)...At any rate, I'm considering setting the max degree of parallelism to 4 (on a 4 physical, 4 hyperthreaded) CPU system to help with 'poorly written procs'.  My questions are these:

    1.  How badly might this affect peformance?

    2.  How might a determine a good value for cost threshold for parallelism as an alternative to setting MAXDOP?

    3.  Lastly, for poorly written queries, would turning OFF hyperthreading help in any way...seems to be split on hyperthreading out there...more say its good, but some say its bad.

     

    Obviously, my best solution is to correct the underlying code...  but I want to find some type of stopgap to allow me time to do this.  My server gets 100% CPU usage.  The only way so far I've found to correct is to issue DROPCLEANBUFFERS AND FREEPROCCACHE.  That seems to sort of 'correct' it, for the time being. 

     

    Additional thoughts are always welcome.

  • February 18, 2005 at 7:04 am

    #543066

    1) If you are using windows 2000 advanced server, based on my test on a heavy transactional sql server, turning hyperthreading off will greatly increase perfromance

    2) If you are using windows 2003 family server, it's suggested that leave hyperthreading on.

    As for MAXDOP and threshold, it's up to the query type, concurrent user etc on your sql server. Some users may benifit from lower MAXDOP, some may be not e.g. large table scan, sorting, indexing, and some dbcc commands.

    The only way is to test it and find a balance configuration. Each time change one setting and test on typical load.

     

     

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply