SQL Server - Using SQL Agent JOBs to Schedule and Execute Multiple Stored Procedures in Parallel

This article introduces a way to schedule and kick off multiple stored procedures in parallel. Suppose we need to build an ETL system to migrate data from the source system to the target in T-SQL script; we've created multiple stored procedures (SP) each of which contains the payload of extracting data from one module of the source system and loading it to the target system; any of those stored procedures can be executed independently without relying on the execution statuses of any other stored procedures; and to speed up the whole process, we need to run multiple stored procedures simultaneously.

Ideally, the above task can be handled easily in SSIS. Of course we could've created a Sequence Container and dropped a batch of SP execution tasks to it in SSIS, and then another Sequence Container containing the next batch of SP execution tasks, etc., but what if using SSIS has become a luxury for us (for example, your boss is not a fan of SSIS and prohibits you from using it as well; or you're using a company-granted computer the type of which your grandpa once used in the last century and SSIS is not pre-installed in it, plus you're not allowed to install SSIS in it by yourself due to the company's policy, and so forth)? Well, in that case we'll have to find replacements of SSIS. You could build C# code and compile it as a CLR stored procedure. If you're an expert in PowerShell, you'd love to create a JOB with a 'PowerShell' type of step that contains the payload PowerShell script. Or else, as what we're going to introduce in the below paragraphs, use JOBs and WHILE loop to manage the scheduling and executions of those SPs in parallel.

Assume our SPs are named as 'ETL_SP_A', 'ETL_SP_B', 'ETL_SP_C'... We need to follow the below steps to achieve our target.


 1. For each stored procedure, we need to create a corresponding JOB that contains the execution payload script. To be more specific (let's take the SP 'ETL_SP_A' for example), we create a JOB named 'ETL_JOB_SP_A', in which we create a T-SQL step that contains the script 'EXEC ETL_SP_A'.

2. Create a table and insert into it the data for scheduling and executions. In our example, we use the below SQL script 

create table ETL_Scheduling_Control
(
[Job_Name_N_Exec_Script] varchar(128)
,[Status] varchar(16)
)

insert into ETL_Scheduling_Control
(
[JOB_Name_N_Exec_Script]
,[JOB_Status]
)
values ('EXEC msdb.dbo.sp_start_job N''ETL_JOB_SP_A'';','Not Started')
,('EXEC msdb.dbo.sp_start_job N''ETL_JOB_SP_B'';','Not Started')
,('EXEC msdb.dbo.sp_start_job N''ETL_JOB_SP_C'';','Not Started')
...
...

...

3. Execute the below script to handle the scheduling and paralleled executions of the JOBs.

DECLARE @Parallel_Execution_Number int = 3,
@Number_ETL_Job_To_Kickoff int,
@ETL_Job_Execution_Script nvarchar(max),
@Total_Job_Number int = (SELECT COUNT([JOB_Status]) FROM [ETL_Scheduling_Control])

WHILE 1 = 1
BEGIN
IF EXISTS (SELECT TOP 1 [JOB_Status] FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = 'Failed')
BEGIN
   RAISERROR ('ETL failed.',16,1)
   BREAK
END

IF (SELECT COUNT([JOB_Status]) FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = 'Successful') = @Total_Job_Number
BEGIN
   PRINT 'ETL JOBs executed successfully. terminate the loop'
   BREAK
END

IF (SELECT COUNT([JOB_Status]) FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = 'Running') < @Parallel_Execution_Number
   AND (SELECT COUNT([JOB_Status]) FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = 'Not Started') >= 1
BEGIN
   SET @Number_ETL_Job_To_Kickoff = @Parallel_Execution_Number - (SELECT COUNT([JOB_Status]) FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = 'Running')
   SET @ETL_Job_Execution_Script = 'SELECT @ETL_Job_Execution_Script = (SELECT TOP ' + cast(@Number_ETL_Job_To_Kickoff as nvarchar) +' [JOB_Name_N_Exec_Script] + '''' FROM [ETL_Scheduling_Control] WHERE [JOB_Status] = ''Not Started'' FOR XML PATH(''''))'

EXEC sp_executesql @ETL_Job_Execution_Script, N'@ETL_Job_Execution_Script nvarchar(max) output',@ETL_Job_Execution_Script = @ETL_Job_Execution_Script output

   EXEC (@ETL_Job_Execution_Script)

     WAITFOR DELAY '00:00:05'
END
ELSE BEGIN
   WAITFOR DELAY '00:00:05'
END
END

Let's have a quick look at what the above script does. Firstly, some variables are defined. Please be reminded that the @Parallel_Execution_Number controls the max number of JOBs to be executed at the same time (in our example, it's set as 3). If the resources in the SQL Server which the script will be executed against are abounded, you can raise this number to a higher value. 

Secondly, a WHILE loop with three IF statements inside it. The first IF statement examines whether any JOB execution is failed. If there is, it will break the loop instantly before raising an error for the user to check; the second if checks if all the JOBs are executed successfully, and breaks the loop reporting success if it's true; and the third IF...ELSE statement is the payload of scheduling and kicking off the JOBs in parallel. Now let's analyse it in details.

At the beginning, it checks if there are three JOBs running at the same time, and if there are it will wait for 5 seconds and loop again. Otherwise, it will kick off an offset number of JOBs. 

We can imagine that when it's looping for the first time, three JOBs will kicked off in parallel, and that's exactly the reason why we compile each of our stored procedures into a single JOB - once you execute the script EXEC msdb.dbo.sp_start_job N'' to kick off a JOB, it finishes instantly, leaving the JOB to run at the back, and so it gives us the ability to kick off three JOBs simultaneously, equivalent to running three stored procedures in parallel.

The keys of how we generate and kick off the dynamic SQL of kicking off three JOBs are the FOR XML PATH() statement and the EXEC sp_executesql with output statement. If you don't have good understandings of how they work, please refer to the abounded resources online for references.

It then loops for the second time immediately. Since three JOBs are just kicked off, and if we assume the executions are all running OK, it waits for 5 seconds and then loops again.

Let's assume that 1 JOB is finished successfully in the third loop. Our scripts finds that, and then it picks another one from the pool and executes it. Now we can see the importance of the controlling table we created at the beginning. With its existence, we can easily get the number of JOBs to be executed and their execution status. It also means that in each of the JOBs, a message insertion mechanism needs to be built, so that the status of each JOB will be updated in the controlling table ETL_Scheduling_Control. That's why we recommend TRY...CATCH block is to be contained in each SP.

The loop goes on and on until all JOBs are finished successfully, or any JOB returns a failure. Please also be noticed that here we use a infinite loop that will only terminates when some condition triggers the break. Before you use it, make sure that all the conditions can be met so that the loop can end. If you are still worries about the infinite loop, you can set it to loop for a set number of times, and in each time make sure your WAITFOR DELAY value is big enough to ensure all JOBs can be kicked off in the limited times of loops. 

Comments

Popular posts from this blog

sp_executesql – Let's learn How to Use It