sp_start_job delay

Question

Users of our app can submit validation requests which result in about 50 stored procedures being launched as SQL agent jobs using sp_start_job. We want them to run in parallel to minimize the overall validation time.

But there seems to be a 3 second delay for each call to sp_start_job, so to launch the 50 jobs takes 50 X 3 = 150 seconds.

Any hints or suggestions? I can find no documentation about the reason for this delay.

Answer 1

Are you acquainted with Service Broker? From your brief description, that sounds like a good fit here. With Service Broker you would post messages on a Service Broker queue, and then would configure your queue to have a high number of MAX_QUEUE_READERS.

If you have never heard of Service Broker before, there is certainly a learning curve, but it may be worth investigating.

I can't say that using Agent for this task sounds appealing to me.

Answer 2

Hi @Bindu Purhar ,

Do you run same transaction in different style? I mean the implicit one and explicit one are running the same t_sql and they run the same periods?

excute code as next one by one and then post the result here:
--CHECK WAITTIME SPID DBID
select * from sys.sysprocesses

--check resource  
sp_lock  
  
--check tablename  
select * from sys.objects  where object_id = 581577110   
  
--check running t_sql   
select * from sys.dm_exec_requests as p  
cross apply  sys.dm_exec_sql_text (p.sql_handle) as qt where p.session_id >50  

And you can also choose use SSIS to run your parallel jobs,please reference: parallel-execution-of-tasks-in-ssis

More information: running-sql-agent-jobs-in-parallel, how-can-i-execute-stored-procedures-in-parallel-and-dynamically-

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Bindu Purhar ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.