Post reply

why parallelism in OLTP sometimes is bad ?

  • April 18, 2009 at 7:55 pm

    #203723

    Hi all,

    In my little experience with sql server i found some situations where parallelism hurt performance.

    I think if sql server uses parallelism it doing the best it can with the information that it has (statistics, indexes..etc).

    So in some cases, restructuring the query and the creating correct indices parellelism is gone.

    But i have many cases were everthing its correct (statistics, indexes) and parallelism is in there. Then, i use option maxdop in querie and it was much more fast without parallelism.

    Why this happend ?

    Perhaps the cost of UMS to manage the threads in all processor is more than just resolve it in a one processor?

    I reaaly want know this....

    Thanks a lot !!!

    $hell your Experience !!![/url]

  • April 20, 2009 at 6:26 am

    #980651

    Parallelism is an expensive process because of all the management that SQL Server has to do. So, you only want it to run on queries that are running long already where parallelism will reduce their time. The default values when you install SQL Server, Parallelism Threshold = 5 and Max Degree of Parallelism = 0, are not necessarily suited to most OLTP systems. I usually bump the threshold up to 25. This way, only queries that get an estimated run time of 25 seconds will start to use paralelllism. It's not a hard and fast number. I've set it lower or higher on systems depending on the types of queries, but that's my general rule of thumb. I'd also suggest limiting the number of CPU's so that one is always available to the OS.

    "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

  • April 20, 2009 at 6:57 am

    #980671

    Thanks Grant,

    but one question you said :

    "Parallelism is an expensive process because of all the management that SQL Server has to do"

    Its UMS Scheduler or SQL SERVER has to do this ?

    $hell your Experience !!![/url]

  • April 20, 2009 at 7:03 am

    #980674

    SQL Server is heavily involved on managing parallelism. It has to to merge the data from the various streams so, it does quite a lot of work.

    "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

  • April 20, 2009 at 7:05 am

    #980677

    Thanks a lot Grant !!!

    $hell your Experience !!![/url]

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

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