locked
Is it possible to force the query to use parallelism? RRS feed

  • Question

  • I have a query which is not using  parallel processing.

    Is it possible to force the query to use parallelism?


    Mr Shaw
    Thursday, October 7, 2010 1:58 PM

Answers

All replies

  • Yes, you can use MAXDOP option in your query to force parallelism.

    Check out this article

    http://msdn.microsoft.com/en-us/library/ms181714.aspx


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, October 7, 2010 2:01 PM
  • I thought this limits the maximum degree of parallelism.

    Surely i need MINDOP? if it exists?


    Mr Shaw
    Thursday, October 7, 2010 2:03 PM
  • You can use the MAXDOP query hint, but if the Query Optimizer estimates that not using parallelism is better, you have to manually create a plan guide and introduce this (using sp_create_plan_guide)

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    • Proposed as answer by Naomi N Thursday, October 7, 2010 2:11 PM
    • Marked as answer by Ai-hua Qiu Friday, October 15, 2010 9:09 AM
    Thursday, October 7, 2010 2:04 PM
  • No, it does not exist. If you can post the query, table(s) structures and indexes, may be someone can suggest anything.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, October 7, 2010 2:05 PM
  • You can't force it to. You can overwrite the degree of parallelism configuration by using maxdop, but the optimizer might not necessarily uses the processors.

     

     

    Thursday, October 7, 2010 2:06 PM
  • Well the query hint MAXDOP sets the number of CPU's to be used for the batch.

    If you set MAXDOP to 0 then SQL SERVER chooses maximum degree of parallelism and in the same way if you use MAXDOP 1 then only one CPU is allocated to the batch.

    If you look at the execution plan you will get a better idea of what is happening. It is not always that specifiying this ooption will increase performance. It may or may not improve perfomance based on your query.

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, October 7, 2010 2:09 PM
  • The question then becomes, how do I create a plan guide that forces parallelism when the issue is that I am unable to generate a parallel plan in the first place?  A secondary question is, why does the optimizer choose a non-parallel plan when the parallel version would execute so much faster

    Answers to both these questions can be found here:

    http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

    In addition, you can vote for a MINDOP or PARALLEL_PLAN query hint here:

    http://connect.microsoft.com/SQLServer/feedback/details/714968/provide-a-hint-to-force-generation-of-a-parallel-plan

    Cheers,

    Paul

    • Marked as answer by Naomi N Sunday, December 25, 2011 1:06 AM
    Saturday, December 24, 2011 3:17 AM
  • Hi Shaw,

    Please refer : http://msdn.microsoft.com/en-us/library/ms178065.aspx

    - Johni Sposeto

     

    Sunday, December 25, 2011 1:52 PM