Post reply

One SPID - Split into 127?

  • January 28, 2013 at 1:44 pm

    #273514

    My query is paralleled , when i query sys.sysprocesses i see 127 records for that same spid with CXPAcket wait types. There are 24 logical cores on the server, shouldn't i see only 24 records (one for each thread), why do i see 127?

  • January 28, 2013 at 3:34 pm

    #1581833

    Each operator in the parallel portion of the query plan can parallel up to maxdop/number of cores. They won't all be running, but they can all exist.

    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

  • January 28, 2013 at 3:41 pm

    #1581835

    GilaMonster (1/28/2013)

    Each operator in the parallel portion of the query plan can parallel up to maxdop/number of cores. They won't all be running, but they can all exist.

    I see 127 records ?

  • January 28, 2013 at 3:53 pm

    #1581837

    sqldba_newbie (1/28/2013)

    GilaMonster (1/28/2013)

    Each operator in the parallel portion of the query plan can parallel up to maxdop/number of cores. They won't all be running, but they can all exist.

    I see 127 records ?

    I explained why you can have so many. If there are multiple operators in the query can parallel, there can be up to maxdop threads for each operator, so 5 operators each paralleling over 24 cores would be 120 threads plus control threads. They won't all be running at the same time, but they can all exist.

    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

  • January 28, 2013 at 5:14 pm

    #1581856

    GilaMonster (1/28/2013)

    sqldba_newbie (1/28/2013)

    GilaMonster (1/28/2013)

    Each operator in the parallel portion of the query plan can parallel up to maxdop/number of cores. They won't all be running, but they can all exist.

    I see 127 records ?

    I explained why you can have so many. If there are multiple operators in the query can parallel, there can be up to maxdop threads for each operator, so 5 operators each paralleling over 24 cores would be 120 threads plus control threads. They won't all be running at the same time, but they can all exist.

    Thanks. If they are in parallel shouldn't atleast 24 of them be running?

  • January 29, 2013 at 1:26 am

    #1581925

    sqldba_newbie (1/28/2013)

    Thanks. If they are in parallel shouldn't atleast 24 of them be running?

    At most 24 will be running at any one time (that's how many cores you have).

    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