Hi,

I hope I've got the right forum for this but let me know if not...

I have a maintenance job of say 6 steps which I wish to speed up with by placing some of the jobs in parallel. The hardware is a single XEON quad core with hyper threading.

Currently the 6 jobs run serially within 1 job of which the CPU load is about 25% overall. Some of the cores hit 100% but others are 0%).  I was thinking I could speed up the task by running steps 1-3 in series, then 4, 5 and 6 in parallel as they are not dependent one another, only dependant on steps 1-3 completing.

Is this possible? If so, how?

Cheers,

Bassmann

Hello Bassmann,

Then create separate Agent Jobs for step 4-6

Hi Olaf,

I've split my steps into a couple of jobs but I'm not sure what the syntax is to start an agent job from another agent jobs step. I want to start Job2 at step 4 within Job 1. what's the syntax for that and is the job type a t-sql or operating system request?

To clarify what I'm thinking of doing

Job1 runs step 1-3, at step 4 starts Job2 which has 3 steps. Job 1 continues steps 5-8. The intention is for Job2 and Job1 steps 5-8 to run in parallel.

I assume Job 1 only needs to start Job2 at step 4 and doesn't need to wait for Job2's 3 steps to complete before starting step 5 in Job1. Is that correct?

Regards,

Bassmann

I believe that is a difficult strategy to implement. Can you specify the nature of your jobs? We could also try using Alert conditions to trigger a SQL Agent job based on your job nature.

As SQL agent job steps just have only the constraint as On failure/On success, it looks not practically possible unless someone else have a different opinion.

You are correct Job1 would not wait for Job2 to finish once you start it in Step4. At your Step 4 on job1 you can start the job2 at step 1 by using sp_start_job stored procedure. Then the job1 would continue to Step5 while Job2 would start executing at Step1.

Syntax link for your convenience:

https://msdn.microsoft.com/en-us/library/ms186757.aspx?f=255&MSPPError=-2147217396

Only thing you have to keep in mind is that any step on job2 cant not be dependant on any steps past step3 in job1.

Hope this helps

• Edited by SQLGru 5 hours 46 minutes ago

There is a simple way to implement this without the Agent, using Service Broker.

The basic Idea is that we can use Service broker instead of the Agent, like we do with SQL EXPRESS maintenance. This is how I backup all my client's SQL EXPRESS databases :-)

* i found this link for example (I did not read it all but it look like a tutorial of the issue that I am talking about):
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express-2

Now that we know how to use Service Broker in order to Schedule tasks, we can disugn this procedure

Let's assume that we have 3 steps that need to be execute in ordered way, but only step 2 can be execute in parallel, and we need to execute it 4 times, next only when all the 4 times of step 3 end we need to execute step 3 once. Our time line will be:

------- time line ----

Step 1 start

Step 1 End -> activate step 2 four times (Service Broker work synchronicity on all message, we just need to activate 4 messages)

Step 2 Start first
Step 2 start second time
Step 2 start third time
Step 2 start fourth time

Step 2 end one after the other...
each time step 2 end we check if this is the last execution of step 2

Only when step 2 ended 4 times then we activate Step 3 which start 

-------  ----

The implementation:

In order to use the service broker we created table with all the tasts that we need to execute. We will have one column for "active" 1 or 0. At the start we mark only step 1 as active. In step 1 in the end of the task we add simple query that activate (update the table) all the 4 tasks of step 2, so now afyter step 1 end it start step 2 four times parallel. We store a simple parameter in table or any pother way that we want that hold the number of "step 2" processes  ended. In the end of step 2 (each executing of step 2) we check what is the value of the parameter. if the value is 3 then we finished the fourth execution now => therefore we will activate step 4, if the value is less then 3 then we update this parameter and add 1 (dont forget to lock the table that store the parameter during the check-> update procedure)

that is all the idea....
I hope you can understand my poor English :-(
I need to go, and I did not have time to check with Google translator

* it worked for me in practice on production in the past (on SQL EXPRESS but there is no reason not to use this procedure on other versions as well). In my case It is a bit more complex since "step 3" need information from all the parallel execution of step 2, therefore i store this info in a simple table, and step 3 use it.