execute multiple jobs in parallel SQL Agent

Question

execute multiple jobs in parallel SQL Agent

Lord Slaagh

SSCertifiable

Points: 5741
More actions
September 12, 2016 at 9:46 am

#329785

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

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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.

😎

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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

Lord Slaagh SSCertifiable Points: 5741 More actions · Answer 4

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

Lord Slaagh SSCertifiable Points: 5741 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

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

Lord Slaagh SSCertifiable Points: 5741 More actions · Answer 8

Thanks!

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 11

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