Post reply

MAXDOP ,Server Core & SELECT

  • October 11, 2018 at 3:06 am

    #370780

    Hi Experts,

    I have a complex select statement with Union All and have multiple select inside which fetches around 1 million records.
     It creates around 30 threads when ran where my server is having only 2 cores. I tried giving OPTION (MAXDOP 2) but it didnt make any difference. 

    How can I restrict the query to 2 threads? and why the query is running under 30 threads when i have only 2 core server?

  • October 12, 2018 at 1:31 am

    #2009294

    MAXDOP 2 limits the query to 2 *running* threads, as does a 2-core server. Why are you worried about the number of non-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

  • October 23, 2018 at 6:59 am

    #2010758

    GilaMonster - Friday, October 12, 2018 1:31 AM

    MAXDOP 2 limits the query to 2 *running* threads, as does a 2-core server. Why are you worried about the number of non-executing threads?

    Thanks Gail.

    If I specify MAXDOP 2 why its still taking more than 2 threads?

  • October 23, 2018 at 7:18 am

    #2010763

    VastSQL - Tuesday, October 23, 2018 6:59 AM

    GilaMonster - Friday, October 12, 2018 1:31 AM

    MAXDOP 2 limits the query to 2 *running* threads, as does a 2-core server. Why are you worried about the number of non-executing threads?

    Thanks Gail.

    If I specify MAXDOP 2 why its still taking more than 2 threads?

    MAXDOP limits the threads per operation, not for the entire query.

    Sue

  • October 23, 2018 at 9:37 am

    #2010798

    VastSQL - Tuesday, October 23, 2018 6:59 AM

    GilaMonster - Friday, October 12, 2018 1:31 AM

    MAXDOP 2 limits the query to 2 *running* threads, as does a 2-core server. Why are you worried about the number of non-executing threads?

    Thanks Gail.

    If I specify MAXDOP 2 why its still taking more than 2 threads?

    Because Maxdop limits the number of threads that may be running. Not the total number of 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 5 posts - 1 through 4 (of 4 total)

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