0

I have 3 stored procedures in a SQL Server database.

EXEC sp_test_1 (ID)
GO

EXEC sp_test_2 (ID)
GO

EXEC sp_test_3 (ID)
GO

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

When I execute each stored 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 completed in 15 minutes and giving good performance gain.

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

Can any one suggest a way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • 1
    Does this answer your question? [How can I run sql server stored procedures in parallel?](https://stackoverflow.com/questions/277900/how-can-i-run-sql-server-stored-procedures-in-parallel) – Evaldas Buinauskas Feb 18 '21 at 08:35
  • @EvaldasBuinauskas I have gone through it already but enough concrete answer to work out – mohan111 Feb 18 '21 at 08:37
  • Are you trying to call these 3 in parallel from any ETL tool or by using SQL only? – Teja Goud Kandula Feb 18 '21 at 08:48
  • In ETL (SSIS) I know the Process . I'm looking in SQL only – mohan111 Feb 18 '21 at 08:50
  • SQL is run sequentially, @mohan111 . You can't write a batch with multiple statements that are run at the same time. If you want to run 3 separate statements at the same time, you need 3 separate connections to run said statements. This is why you can achieve this in SSIS, as you have 3 Execute T-SQL Tasks, each running a different statement. – Thom A Feb 18 '21 at 11:37
  • @mohan111 . . . You can use SQL Server Agent to create a job that runs separate steps in parallel. – Gordon Linoff Feb 18 '21 at 12:40
  • Or use PowerShell to kick off 3 separate batches – Martin Cairney Feb 18 '21 at 21:49

1 Answers1

0

you can do like this:

first you should create a job every proc.

Declare @Job1 uniqueidentifier,
@Job2 uniqueidentifier,
@Job3 uniqueidentifier

-- CREATE JOB1
EXEC msdb.dbo.sp_add_job @job_name = 'JOB1' , @job_id = @Job1 OUTPUT;
EXEC msdb.dbo.sp_add_jobserver @job_id = @Job1 , @server_name = N'(LOCAL)'
EXEC msdb.dbo.sp_add_jobstep @job_id = @Job1 , @step_name = 'execute' , @command = 'EXEC sp_test_1' , @database_name = 'DBNAME'

-- CREATE JOB2
EXEC msdb.dbo.sp_add_job @job_name = 'JOB2' , @job_id = @Job2 OUTPUT;
EXEC msdb.dbo.sp_add_jobserver @job_id = @Job2 , @server_name = N'(LOCAL)'
EXEC msdb.dbo.sp_add_jobstep @job_id = @Job2 , @step_name = 'execute' , @command = 'EXEC sp_test_2' , @database_name = 'DBNAME'

-- CREATE JOB3
EXEC msdb.dbo.sp_add_job @job_name = 'JOB3' , @job_id = @Job3 OUTPUT;
EXEC msdb.dbo.sp_add_jobserver @job_id = @Job3 , @server_name = N'(LOCAL)'
EXEC msdb.dbo.sp_add_jobstep @job_id = @Job3 , @step_name = 'execute' , @command = 'EXEC sp_test_3' , @database_name = 'DBNAME'

now you can execute proc be async like this

-- now you can, execute proc be async like this
EXEC msdb.dbo.sp_start_job @job_name = 'JOB1';
EXEC msdb.dbo.sp_start_job @job_name = 'JOB2';
EXEC msdb.dbo.sp_start_job @job_name = 'JOB3';
Karcan
  • 173
  • 1
  • 5