execute multiple jobs in parallel SQL Agent

  • Hello SSC,

    I have about 5 jobs that I need to execute simultaneously in a SQL Agent job. It is my understanding that SQL Agent doesn't support parallel processing? Anyway, I am trying to devise a plan to handle this. My question is...

    If I add this script to a SQL job, will this execute each job in parallel or would this script execute the jobs in increments (Job1 completes, Job2 starts, Job2 completes, Job3 starts, etc)?

    Thank you all for your continued help and support! 😀

    EXEC msdb.dbo.sp_start_job 'Job1'

    EXEC msdb.dbo.sp_start_job 'Job2'

    EXEC msdb.dbo.sp_start_job 'Job3'

    EXEC msdb.dbo.sp_start_job 'Job4'

    EXEC msdb.dbo.sp_start_job 'Job5'

    The are no problems, only solutions. --John Lennon

  • Quick thought, if you execute a script like you posted then it will run sequentially. To execute in parallel then simply have one task for each statement on the same schedule.

    😎

  • Your script will kick off a start for each job. It will wait for a return to state that the sp_start_job procedure was executed, but it will not wait for the job to finish before moving to the next.

    sp_start_job is fire and forget.



    Shamless self promotion - read my blog http://sirsql.net

  • Eirikur Eiriksson (9/12/2016)


    Quick thought, if you execute a script like you posted then it will run sequentially. To execute in parallel then simply have one task for each statement on the same schedule.

    😎

    Though the jobs which the script starts will run in parallel, because sp_start_job completes as soon as the job has started.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Can you give me an example of... "simply have one task for each statement on the same schedule".

    I am not sure what you mean or how to do this.

    Thanks again!

    The are no problems, only solutions. --John Lennon

  • Hi Phil,

    So my script will have a one second delay before kicking off each job?

    Thanks

    The are no problems, only solutions. --John Lennon

  • Lord Slaagh (9/12/2016)


    Hi Phil,

    So my script will have a one second delay before kicking off each job?

    Thanks

    Depends on how quickly your server reacts, but, approximately, yes.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Lord Slaagh (9/12/2016)


    Can you give me an example of... "simply have one task for each statement on the same schedule".

    I am not sure what you mean or how to do this.

    Thanks again!

    I'm not sure, either 🙂

    I assume that the suggestion was to have multiple jobs, one per statement, all running on the same schedule.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks!

    The are no problems, only solutions. --John Lennon

  • To execute them 100% in parallel, you could create an SSIS package with 5 execute SQL Tasks, where each task kicks off a job.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Another approach to get as near to simultaneous as possible would be to use Powershell where you can spawn multiple threads on the machine. The degree to which these things are actually going to run in literal parallel is also dependent on the load currently on the system, what each of these processes is doing, the number of CPUs you have available to SQL Server, and your memory. The question is, do you have some process that literally requires five simultaneous threads running in synchronization, or do you just want to kick off five processes and they'll finish when they finish? If the former, I think I'd push back on the design or at least I'd start looking at external transaction management. If the latter, Powershell will do it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Whether jobs are started asynchronously by sp_start_job or sqlagent's call to it, there is a per-sqlagent-subsystem max worker thread limit. Once that limit is reached, SQLAgent.out logs that stall. Best to inspect sqlagent.out while racking up a number of concurrently executing jobs. Otherwise one risks seeing a delay, but not understanding why :).

    The job subsystem limits are reported as max_worker_thread values, returned by

    use msdb

    exec sp_enum_sqlagent_subsystems

Viewing 12 posts - 1 through 11 (of 11 total)

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