SQL Server Performance

Max degree of parallelism - not understood!

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by sqlguy, Oct 13, 2011.

  1. sqlguy New Member

    Hi,

    We have a Win 2008 R2 32 CPU SQL Server 2008 machine we use for SP2010.

    I'm trying to find a straight answer to a couple of questions about queries and MDOP.

    (1) Max degree of parallelism - as I understand it :

    0 = use all CPUs on server with no exceptions
    1 = suppress parallel plan generation
    2 or greater = this is the number of CPUs to use for a single query's execution

    OK - so under what conditions would you change this setting?

    (2) We run Sp2010 - MS recommends in many docs that you set MDOP to 1 - but why?
    Surely with something hitting the db as hard as SP2010 you would want parallel plans being executed because you'd want as many CPUs being utilized at same time for similtaneous execution of code - or have I missed the general concept here?

    (3) Cost - could someone *please* define what "cost" is - it seems a dimensionless number, but if an execution plans segments are broken into bits, and each bit has a % cost, the overall of the plan can never exceed 1 ( 100% ). Additionally if we look at the "Cost Threshold for Parallelism" of say 5 ( as is the default ) - then if cost can never exceed 1, why is it set to 5?

    And how does the cost threshold relate to the max degree of parallelism, or maybe it doesnt?

    Any help trying to understand this is much appreciated........

    Cheers

    SG.
  2. Shehap MVP, MCTS, MCITP SQL Server

    Speaking of CPU Parallelism perspective and its impact on performance , we could come up with the major considerations below:

    1. CPU Parallelism=0 is the default which = max degree of CPU Parallelism.

    2. Indeed, This default one keep the best practice for most of workloads particularly more OLAP + Data warehousing workload where CPU parallelism is highly needed

    3. But if needed to change it , it could be done 3 choices :

    · Change it through Advanced properties of SQL Service (Not recommended since it is circulated to all Users +Applications)

    · Change it through the new 2008 technology “Resource Governor” where you could create specific Resource pools for some Users or APP with :
    I. Specific CPU Parallelism
    II. Specific Memory grant %
    III. Specific memory wait time (sec)

    1. Changing it through Query Hints using OPTION (MAXDOP n)to identify specific CPU parallelism for some scripts

    But please note :

    1. Proceeding the 1st + 2nd choices might need to revalidate all query execution plans of query which might impact on end users for some while

    2. But the last choice OPTION (MAXDOP n) is not recommended to be used except with special circumstances like:

    · Some long running queries like (heavy data warehousing or OLAP transactions) where it doesn’t matter its time , but it matters only its impact on other end users then it could be set to
    OPTION (MAXDOP 1)

    · Some developers queries run directly on production then it could be set to OPTION (MAXDOP 1)

    Finally , changing it isn’t absolute performance improvement , but it is relevant to what you have of workload and so it needs to be tested well against that workload
  3. satya Moderator

    Adding to what Shehap referred above, in any case when you set MAXDOP to 1 the query optimizer will use serial plans.
    The main difference between MAXDOP and Resource Governor is, RG will compile the plan in cache and MAXDOP is applied during runtime. To obtain the reference you can take help of STATISTICS_PROFILE setting from query window.

    When you set the MAXDOP setting as you do is RECONFIGURE to affect the change which causes the plan cache flush..so the plan is compile at run time.
    In this regard I dusted out one of my old blog http://sqlserver-qa.net/blogs/perftune/archive/2010/06/24/9144.aspx post too.

Share This Page