Post reply

Execution Plan Icons

  • April 8, 2014 at 10:30 am

    #306077

    Hi,

    I want to find out more information about the execution plan. I saw Parallelism (Gather Streams) in the excution plan. In what situation we can see this icon? Please let me know Is it good or bad in terms of performance. Do we need to avoid this? If in case if we need to avoid how can we avoid this?

    Thanks,

  • April 8, 2014 at 10:36 am

    #1704204

    This operator gathers all of the other streams to produce a single output. This is not good or bad, it depends :-D. This is necessary for the parallel plan, so the question is should this query go parallel? You need to determine if the query is having performance issues due to parallelism or not and then determine why. If you want please read the first article in my signature and then post your query (along with sample scripts) and we could see if we can help with the performance of it.

    http://technet.microsoft.com/en-us/library/ms187002(v=sql.105).aspx


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • April 8, 2014 at 10:57 am

    #1704209

    As was stated, it's not about avoiding it or not. It's about whether it's appropriate or not. But, one thing you should change is the default value for Cost Threshold for Parallelism. The default value is 5, and that's too low. I'd suggest changing it to 35 for an OLTP system and then see how that affects queries.

    For more details on execution plans, see the book in my link.

    "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 8, 2014 at 12:39 pm

    #1704270

    Hi,

    Thanks for the response.

    I am sorry, I didn't understand the point. Please let me know, which default value and how to change that?

    Thanks,

    Ramana

  • April 8, 2014 at 12:44 pm

    #1704274

    ramana3327 (4/8/2014)

    Hi,

    Thanks for the response.

    I am sorry, I didn't understand the point. Please let me know, which default value and how to change that?

    Thanks,

    Ramana

    Cost Threshold for Parallelism

    You change it as described here.

    "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 13, 2014 at 9:39 pm

    #1705626

    Yes. Got your point. Thanks

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

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