September 12, 2016 at 9:46 am
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
September 12, 2016 at 10:16 am
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.
😎
September 12, 2016 at 10:20 am
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.
September 12, 2016 at 11:03 am
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
September 12, 2016 at 11:51 am
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
September 12, 2016 at 11:54 am
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
September 12, 2016 at 12:14 pm
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
September 12, 2016 at 12:17 pm
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
September 12, 2016 at 12:17 pm
Thanks!
The are no problems, only solutions. --John Lennon
September 13, 2016 at 2:57 am
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
September 13, 2016 at 3:30 am
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
September 13, 2016 at 8:50 am
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