How to call multiple Store Procedures in parallel in MS SQL

varaprasadmohan pinagadi 1 Reputation point

Hi All,

I have 3 store procedures in MSSQL DB.

EXEC sp_test_1 (ID)

EXEC sp_test_2 (ID)

EXEC sp_test_3 (ID)


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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,883 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 29,281 Reputation points

    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
    1 person found this answer helpful.

  2. Jeffrey Williams 1,886 Reputation points

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. brenda grossnickle 186 Reputation points

    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.


    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~'


    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



    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
       DECLARE @ReturnCode INT
       SELECT @ReturnCode = 0
       IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
       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
       DECLARE @jobId BINARY(16)
       EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@JobName, 
               @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'', 
               @os_run_priority=0, @subsystem=N''CmdExec'', 
       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
       GOTO 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
             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 = @JobName)
                set @JobFinished = 1
                WAITFOR DELAY '00:00:02'    
       Set @JobFinished = 0  
       While @JobFinished = 0
             -- 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 = @JobName and HIST.step_id = 1 and msdb.dbo.agent_datetime(run_date,run_time) >= @DateTime 
             IF @JobStatus is not NULL
                SET @JobFinished = 1    
                WAITFOR DELAY '00:00:02'    
       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
             Set @Note = 'SQL Agent Error Message: ' + @JobMessage + '; ' + @Note
             EXEC ifs_RunHistoryUpdateV2 'util_CommandExecute', NULL, 'Fail', 0 
             RAISERROR (@Note, 20, 127) WITH LOG          
       EXEC ifs_RunHistoryUpdateV2 'util_CommandExecute', NULL, 'Success', 0
       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


  4. Cris Zhan-MSFT 6,566 Reputation points


    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.