how to force SS to run certain SP in parallel?

Question

Post reply

how to force SS to run certain SP in parallel?

water490

SSC Eights!

Points: 935
More actions
October 26, 2024 at 3:01 am

#4473914
hi everyone
I have a SP that is run in SSIS. That particular SP is basically running a bunch of SP. See below for code. SP1 and SP2 are independent of each other so they can definitely be run in parallel. They all read data from the same table but each writes to a different table. I know that SS has logic that automatically tries to run items in parallel when it can but I would like to have more control over the entire process. Specifically, I would like to explicitly ask SS to run these SP1 and SP2 in parallel and once they are done then run SP3 and SP4 in serial within the below SP. Is this possible? If yes, how would I do that?
```
/****** Object:  StoredProcedure [dbo].[RunStoredProcedures]    Script Date: 2024-10-25 7:52:43 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[RunStoredProcedures]
as

EXEC dbo.SP1
EXEC dbo.SP2
EXEC dbo.SP3
EXEC dbo.SP4
```
Thank you
- This topic was modified 2 days, 7 hours ago by water490.
October 28, 2024 at 7:02 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246929 More actions · Answer 1

As far as I know, you cannot force parallel execution of procs within a single session. Options include:

Creating Agent jobs to run the procs and then starting the Agent jobs within your proc
Use SSIS

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16147 More actions · Answer 2

and your C# code that you were using to execute the SP's is also a good place to execute them in parallel

water490 SSC Eights! Points: 935 More actions · Answer 3

frederico_fonseca wrote:

and your C# code that you were using to execute the SP's is also a good place to execute them in parallel

My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?

water490 SSC Eights! Points: 935 More actions · Answer 4

Phil Parkin wrote:

As far as I know, you cannot force parallel execution of procs within a single session. Options include:
Creating Agent jobs to run the procs and then starting the Agent jobs within your proc
Use SSIS

do you have sample code for item 2?

Phil Parkin SSC Guru Points: 246929 More actions · Answer 5

water490 wrote:

frederico_fonseca wrote:
and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?

Use multiple ExecuteSQL tasks. Each task runs a proc. If you connect them with precedence constraints, they run in series, but if you leave them unconnected, they run in parallel. No code (other than the T-SQL to execute the proc) is required.

This reply was modified 23 hours, 28 minutes ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

water490 SSC Eights! Points: 935 More actions · Answer 6

Phil Parkin wrote:

water490 wrote:
frederico_fonseca wrote:
and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?
Use multiple ExecuteSQL tasks. Each task runs a proc. If you connect them with precedence constraints, they run in series, but if you leave them unconnected, they run in parallel. No code (other than the T-SQL to execute the proc) is required.

perfect. thank you! I have a few more questions.

I am using i9 processor. Does SSIS know to use the other cores? Or do I have to explicitly state which core to use?
I have a rollback logic in C# that I need to have in place. If any of the SP fail then roll back all of them. Is there a way to do this in SSIS without C#? If not, then how can I run the SP in parallel in C#?