• If you simply sp_exec in loops, notice still they (many worker spids) are all controlled by current master SPID and in theory they will all run in the same parallel scope. However, you can use sp_exec_wait to control the running sequence between sql groups. Like the following pseudo code:

    sp_exec_init

    loop begin

    sp_exec '...'

    sp_exec '...'

    loop begin

    sp_exec '...'

    loop end

    sp_exec_wait

    sp_exec '...'

    sp_exec '...'

    loop end

    sp_exec_end

    If you really want to start other parallel scopes within current parallel process, the following pseudo code shows the trick. You can launch a worker spid to run the sp_exec script.

    --This is in current master process

    sp_exec_init

    --This will launch a worker process who will master a bunch of worker spids

    sp_exec '

    sp_exec_init

    sp_exec ''...''

    sp_exec ''...''

    sp_exec_wait

    sp_exec_end

    '

    --This will launch another worker process who will master a bunch of worker spids

    sp_exec '

    sp_exec_init

    sp_exec ''...''

    sp_exec ''...''

    sp_exec_wait

    sp_exec_end

    '

    sp_exec_wait

    sp_exec_end

    Anytime you can look at pmaster.dbo.exec_queue table to debug the running of your script.