Post reply

Max degree of parallelism for OLTP?

  • February 1, 2012 at 10:20 pm

    #256171

    Hi,

    Max degree of parallelism,

    This is is an instance level configuration setting that is meant to control whether and how much the Query Optimizer will attempt to spread a complex or expensive query across multiple processor cores to run in parallel.

    1. For the OLAP DW should leave it as defualt setting is 0.

    2. For OLTP database at inital setup can leave it defualt setting is 0, in cause wait type CXPACKET how much precentage considerable in OLTP database?

    CXPACKET wait type reaching more than considerable precentage, how much should be set in maxdop if server have dual core and 8 logical processor?

    thanks

    ananda

  • February 2, 2012 at 12:17 am

    #1441558

    maybe this article can shed some light on the topic for you ...

    http://www.brentozar.com/archive/2011/12/consulting-lines-pilot-dog/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 2, 2012 at 7:29 am

    #1441818

    I wouldn't sweat the Max Degree of parallelism nearly as much as I would the cost threshold for parallelism. The default value there is 5, which is EXTREMELY low, especially for an OLTP system. I'd suggest bumping it up somewhere between 35 & 50. See what that does for you.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 3, 2012 at 12:39 am

    #1442223

    Thanks for your reply..

    As you suggested to increase cost threshold for parallelism between 35 & 50? and default value 5 is not Sufficient in OLTP Database.

    I would leave it MAXDOP option, please tell me how to measure cost threshold for parallelism should be increase in database setting?

    Thanks

    ananda

  • February 3, 2012 at 4:46 am

    #1442319

    Change it, then keep an eye on your processor to see if you're still seeing bottlenecks there. Monitor processor queue length and the wait states to see if those numbers change. Make sure you capture them before you make the change too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

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