Post reply

Max DOP

  • October 30, 2013 at 3:27 pm

    #278652

    Hi,

    We have 4 processors on SQL serve box.

    Question: what is the difference in keeping the MAXDOP value to default value and setting it to 4?

    Is there any difference or both means same?

    Thanks

  • October 30, 2013 at 3:46 pm

    #1662868

    4 processors or 4 cores?

    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

  • October 30, 2013 at 3:52 pm

    #1662870

    4 cores

  • October 30, 2013 at 3:55 pm

    #1662874

    Then they'll be the same.

    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

  • October 30, 2013 at 5:09 pm

    #1662884

    Then they'll be the same.

    Thanks Gail.

    But I heard different opinion from Microsoft support engineer that having default value for MAXDOP and setting it 4 is not same.

    If we keep MAXDOP to default, each operator can spread the work across as many threads as it can. But when we set it to 4, each operator can only spread the work across maximum of 4 threads.

    So I just wanted to get more information on this.

  • October 31, 2013 at 1:32 am

    #1662933

    If you keep maxdop at 0, then there can be as many active threads for each query as you have cores. If you put maxdop to 4, then each query can only have 4 executing threads at a time.

    MAXDOP does not limit number of threads per query, only the number of concurrently executing threads.

    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

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

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