Post reply

Max degree of parallelism and affinity mask

  • July 27, 2010 at 5:37 am

    #387436

    Hi Experts,

    I am alittle confused on Max degree of parallelism and affinity mask..

    As per my understanding affinity mask will restrict the number of processor for SQL Server.Say i have 8 processor and affinity mask is set to 4.What

    will happen if i gave max degree of parallelism as 6??Is it possible???

    Also whats the maximum number of processors we can have?(I mean if we use the latest server OS) 64 i hope.

    TIA

  • July 27, 2010 at 10:40 am

    #1198834

    MAXDOP - Maximum Degree of Parallism

    Sets the maximum number of processors the query processor can use to execute a statement.

    Fewer processors may be used depending on the current system workload.

    If the server has only 1 CPU, the answer is if the computer has only one processor, the max degree of parallelism value is ignored.

    affinity mask option

    The affinity mask option, which existed in earlier releases of SQL Server, dynamically controls CPU affinity.

    Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.

    If you don’t specify MAXDOP N query hints, by default max degree of parallelism is equal to number of processor that is allowed to give the thread to run corresponding parallelism operator.

    The number of allowed processor is determined through Affinity Mask in advanced configuration setting. If you specify MAXDOP N, it will used N DOP, but if you put MAXDOP 0, the max DOP will be equal to number of allowed processor in Affinity Mask.

    The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism configuration option in the sp_configure value only for the query that specifies this option.

    In SQL Server 2000, the override takes effect only if the value that is specified in the hint is less than or equal to the sp_configure value.

    In SQL Server 2005 and in SQL Server 2008, the override always takes effect. In SQL Server 2008, if MAXDOP exceeds the value that was configured by using the Resource Governor, the Database Engine uses the Resource Governor MAXDOP value.

    The MAXDOP option does not limit the number of processors that SQL Server uses. Use the affinity mask configuration option to configure the number of processors that SQL Server uses.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • July 27, 2010 at 10:49 am

    #1198842

    Refer the link for the Max. no of Processor supported on SQL Server

    Maximum Number of Processors Supported by the Editions of SQL Server

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • July 27, 2010 at 10:30 pm

    #1199066

    Thanks Siva..

    What you have mentioned is right and still that wont help me...Can u please answer to my sample Q??

  • July 28, 2010 at 1:47 am

    #1199099

    Say i have 8 processor and affinity mask is set to 4.What will happen if i gave max degree of parallelism as 6??Is it possible???

    Yes, you can have MAXDOP as 6 for the SQL Statement executing, it is not constrained by the affinity mask setting of 4.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • July 28, 2010 at 1:58 am

    #1199100

    sivaprasad (7/28/2010)

    Say i have 8 processor and affinity mask is set to 4.What will happen if i gave max degree of parallelism as 6??Is it possible???

    Yes, you can have MAXDOP as 6 for the SQL Statement executing, it is not constrained by the affinity mask setting of 4.

    However since there are only 4 processors available to SQL (set by the affinity mask), it will not be able to use 6 processors for the query. It won't give an error, but a query can't use more processors than are available.

    It's the same as setting maxdop to 6 on a computer that has only a single quad-code processor

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2010 at 2:01 am

    #1199101

    sivaprasad (7/27/2010)

    MAXDOP - Maximum Degree of Parallism ....

    When you quote from Books Online or MSDN, you should site your source.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2010 at 2:02 am

    #1199102

    Also note that MAXDOP sets the maximum allowed degree of parallelism. It doesn't set the exact parallelism used.

    Just because there's a MAXDOP hint on a query doesn't mean SQL will use exactly that number of processors. It can chose to use less.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2010 at 2:11 am

    #1199108

    Thanks a lot Gail Now I am Clear...

Viewing 9 posts - 1 through 8 (of 8 total)

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