How to call multiple Store Procedures in parallel in MS SQL

Question

Hi All,

I have 3 store procedures in MSSQL DB.

EXEC sp_test_1 (ID)

GO
EXEC sp_test_2 (ID)

GO
EXEC sp_test_3 (ID)

GO

When I execute each store procedure is taking 15 minutes to complete and total of 45 minutes.

When I execute each Store Procedure in each session .

Session 1 : EXEC sp_test_1 (ID)

Session 2 : EXEC sp_test_2 (ID)

Session 3 : EXEC sp_test_3 (ID)

It is getting completed in 15 minutes and giving good performance gain.

I'm looking for any option in MS SQL to achieve parallelism and any other direction apart from SSIS .

Can any one suggest me on this

Answer

MS SQL to achieve parallelism

There is no direct concept of parallelism execution of stored procedures in SQL Server.
You have 2 option:

Open several sessions (= Query Windows) in SSMS and run one SP each
Use Service Broker with a Execution Queue, but that requires dedicated knowledge about Service Broker

Answer

You can setup 3 separate agent jobs - and schedule them to run at the same time. Each agent job would execute a separate stored procedure...

With that said - I would focus on why these are taking 15 minutes each to execute. Fix that and then you don't need to worry about parallel execution.

Answer

you could have each tsql in it's own job. job1, job2 and job3. you execute each of the jobs from an sp_exec_job. i have done something smilar. you can even create the sql agent job inside the sproc and set success to delete the job.

this is something that does not do exactly what you want. but it is used to create a sql agent job, execute the sql agent job, delete on completion (just an option of the job step), and wait for competion (which you do not need). and it writes to our log table ifs_runhistory.

CREATE Procedure [dbo].[util_CommandExecute]

/********************************************************************

Executes Windows commands.

Takes the input command and creates a SQL Agent Job named with the format
Bank01_CommandExecute_2019-03-01_15:24:36.313. The job executes as CmdExec with
the input command as the script. After the job is submitted the sproc waits for the job to
finish. Once the job is complete the status and message are retrieved and the job
is deleted. If there were any errors the sproc will RAISERROR. Log records are written
to ifs_runhistory.

Replaces the original version that used xp_cmdshell.

Example

exec util_commandexecute @pCommand = 'RENAME "E:\H360\Backups\copytestrename7.txt" "copytestrename8.txt"'

exec util_commandexecute @pCommand = 'DEL "E:\H360\Backups\copytestrename8.txt"'

exec util_commandexecute @pCommand = 'MOVE "E:\H360\Backups\ExportRunHistory2.txt" "E:\H360\DATA\ExportRunHistory2.txt"'

exec util_commandExecute 'net start "IBM Cognos:9301"'

exec util_commandExecute 'net stop "IBM Cognos:9301"'

exec util_commandexecute @pCommand = 'bcp "SELECT * from ifs_batchcontrol" queryout "e:\h360\backups\ifs_batchcontrol.txt" -e "e:\h360\backups\ifs_batchcontrolError.txt" -d Bank74 -c -T -t~'

Changes

2019_01 bxg written to replace earlier version of sproc that used xp_cmdshell. This
sproc requires user to have enough permissions not only execute a stored
procedure, but to create a sql agent job, run an sql agent job, and you
have to be a system admin to RAISERROR. And of course enough permissions to
run the Windows OS CMD.

*********************************************************************/

@pCommand varchar(max),
@DeleteJob int = 1 -- set to 0 if want to retain job, probably to retrieve the error message for debugging

AS

BEGIN

declare @Command varchar(max) = @pCommand
declare @DBName varchar(128) = db_name()
declare @DateTime datetime = dateadd(ss, -1, getdate())  -- subtract 1 second because job history has all 0 for subseconds
declare @varDateTime varchar(50) = replace(convert(varchar, @DateTime, 121), ' ' , '_')
declare @JobName varchar(128) = @DBName + '_CommandExecute_' + @varDateTime
declare @User varchar(128) = (select system_user)
declare @JobFinished int = 0
declare @JobStatus int 
declare @JobMessage varchar(500) = '' 
declare @Note varchar(500) = ''
declare @SQL nvarchar(max) = ''

set @Note = 'Execute util_CommandExecute to run SQL Agent Job ' + @JobName + ' to run command ' + @pCommand + ' from ' + @User 
EXEC ifs_RunHistoryInsertV2 'util_CommandExecute', @Note, 'util_CommandExecute', 1;

-- create sql agent job using CmdExec that will execute the command
set @SQL = N' 
   IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
   EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1


   BEGIN TRANSACTION
   DECLARE @ReturnCode INT
   SELECT @ReturnCode = 0

   IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
   BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''[Uncategorized (Local)]''
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

   END

   DECLARE @jobId BINARY(16)
   EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@JobName, 
           @enabled=1, 
           @notify_level_eventlog=0, 
           @notify_level_email=0, 
           @notify_level_netsend=0, 
           @notify_level_page=0, 
           @delete_level=0, 
           @description=N''Execute a windows Command'', 
           @category_name=N''[Uncategorized (Local)]'', 
           @owner_login_name=N''sa'', @job_id = @jobId OUTPUT
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Step1'', 
           @step_id=1, 
           @cmdexec_success_code=0, 
           @on_success_action=1, 
           @on_success_step_id=0, 
           @on_fail_action=2, 
           @on_fail_step_id=0, 
           @retry_attempts=0, 
           @retry_interval=0, 
           @os_run_priority=0, @subsystem=N''CmdExec'', 
           @command=@command,
           @flags=0
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   COMMIT TRANSACTION
   GOTO EndSave
   QuitWithRollback:
      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
   EndSave:
   '
BEGIN TRY      
   exec sp_executesql @SQL, N'@JobName varchar(128), @Command varchar(max)', @JobName = @JobName, @Command = @Command

   exec msdb..sp_start_job @job_name = @JobName

   -- wait until sql agent job finished
   While @JobFinished = 0
      BEGIN
         IF NOT EXISTS (SELECT TOP 1 1
                      FROM msdb.dbo.sysjobactivity ja JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
                      WHERE ja.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC )
                      AND start_execution_date IS NOT NULL AND stop_execution_date IS NULL AND j.name = @JobName)
            set @JobFinished = 1
         ELSE
            WAITFOR DELAY '00:00:02'    
      END

   Set @JobFinished = 0  
   While @JobFinished = 0
      BEGIN 
         -- Get first step message and status - HIST.run_status = 1 is success
         SELECT  Top 1 @JobStatus = HIST.run_status , @JobMessage = HIST.message
            FROM msdb..sysjobs JOB
            INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id 
            WHERE JOB.name = @JobName and HIST.step_id = 1 and msdb.dbo.agent_datetime(run_date,run_time) >= @DateTime 
         IF @JobStatus is not NULL
            SET @JobFinished = 1    
         ELSE
            WAITFOR DELAY '00:00:02'    
      END

   IF @DeleteJob = 1 and EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
      EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1

   IF @JobStatus != 1 or @JobStatus is NULL
      BEGIN
         Set @Note = 'SQL Agent Error Message: ' + @JobMessage + '; ' + @Note
         EXEC ifs_RunHistoryUpdateV2 'util_CommandExecute', NULL, 'Fail', 0 
         RAISERROR (@Note, 20, 127) WITH LOG          
      END

   EXEC ifs_RunHistoryUpdateV2 'util_CommandExecute', NULL, 'Success', 0
END TRY
BEGIN CATCH
   Set @Note = 'Error Message: ' + CAST(Error_Number() AS VARCHAR(10)) + ' ' + ERROR_MESSAGE() + '; ' + @Note
   EXEC ifs_RunHistoryUpdateV2 'util_CommandExecute', NULL, 'Fail', 0
   RAISERROR (@Note, 20, 127) WITH LOG
END CATCH

END

Answer

Hi,

It might be a good choice to create three separate agent jobs with the same schedule.

Also please refer to this similar post for more options.
https://dba.stackexchange.com/questions/173265/run-stored-procedures-in-parallel

How to call multiple Store Procedures in parallel in MS SQL

4 answers

Example

Changes