how to force SS to run certain SP in parallel?

Question

how to force SS to run certain SP in parallel?

water490

SSC Eights!

Points: 952
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 3 weeks, 5 days ago by water490.
October 28, 2024 at 7:02 am

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

I'm not a ninja at it but you could try it in a "Maintenance Plan". To execute a proc, you would use an execute SQL block. You could have the path of sp1 and sp2 merge on the same block and then split that to sp3 and sp4.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Here you are courtesy of ChatGPT:

Yes, it’s possible to run specific stored procedures (SP1 and SP2) in parallel and then have SP3 and SP4 execute in series within a main stored procedure in SQL Server. Since SQL Server doesn't support built-in parallel processing directly within T-SQL, you would typically use SSIS for orchestration or leverage asynchronous processing in SQL Server.

Two possible approaches:

Approach 1: Control Parallel Execution in SSIS

In SSIS, create a package that orchestrates the parallel execution of SP1 and SP2, then sequentially executes SP3 and SP4. Here’s a high-level setup:

Set up an SSIS Control Flow:
Add two Execute SQL Tasks for SP1 and SP2.
Configure them to run in parallel (i.e., ensure no dependencies between them).
Set up precedence constraints so that SP3 only runs after both SP1 and SP2 are complete, and SP4 follows SP3.
Configure Task Parallelism:
Control maximum parallel execution by setting the MaxConcurrentExecutables property on the package level if needed.
Error Handling:
Add error handling to manage task failures and retries as needed.
Approach 2: Parallel Execution Using Asynchronous T-SQL
If you need to control this within SQL Server itself, you can use Service Broker or SQLCLR. Here’s a simplified example using SQL Server Agent to trigger SP1 and SP2 in parallel, then wait for completion before executing SP3 and SP4 in sequence.

Approach 2: Parallel Execution Using Asynchronous T-SQL

If you need to control this within SQL Server itself, you can use Service Broker or SQLCLR. Here’s a simplified example using SQL Server Agent to trigger SP1 and SP2 in parallel, then wait for completion before executing SP3 and SP4 in sequence.

CREATE PROCEDURE Main_SP
AS
BEGIN
    DECLARE @SP1_Handle INT, @SP2_Handle INT;

    -- Start SP1 in an async session
    EXEC sp_start_job @job_name = 'SP1_Job';
    SET @SP1_Handle = SCOPE_IDENTITY();

    -- Start SP2 in an async session
    EXEC sp_start_job @job_name = 'SP2_Job';
    SET @SP2_Handle = SCOPE_IDENTITY();

    -- Wait for SP1 and SP2 to finish
    WAITFOR DELAY '00:00:10'; -- Adjust delay based on expected completion time for SP1/SP2

    -- Ensure SP1 and SP2 are complete
    WHILE EXISTS (SELECT * FROM msdb.dbo.sysjobactivity WHERE job_id IN (@SP1_Handle, @SP2_Handle) AND stop_execution_date IS NULL)
    BEGIN
        WAITFOR DELAY '00:00:05';
    END

    -- Run SP3 and SP4 in sequence
    EXEC SP3;
    EXEC SP4;
END;
GO

Explanation:

SP1 and SP2: Triggered as SQL Server Agent jobs asynchronously to run in parallel.
Completion Check: Uses a loop to ensure these jobs are complete before proceeding.
SP3 and SP4: Runs sequentially after SP1 and SP2 have completed.

In summary:

SSIS: Preferred for better control over parallelism and sequence, particularly for complex workflows.
Asynchronous T-SQL: Useful if SSIS isn’t an option, but requires SQL Server Agent job setup for asynchronous execution and monitoring.

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

I have two versions of SSIS setup to test the benefits of parallelism.

Version 1 - no parallelism setup. The precedence constraint I am using between the Execute SQL Tasks is success (ie, they are running in serial)

Version 2 - parallelism is setup. There is no precedence constraint setup between the Execute SQL Tasks.

Version 1 ran with no issues.

Version 2 has an issue. For some reason, there are two Execute SQL tasks that are hanging forever. I stopped the SSIS package after about 30 minutes. Each of those two tasks take less than 2 seconds each to run so I don't think it is the complexity of the SP behind those two tasks that is the issue.

What could be causing Version 2 to fail? What should I look into?

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

Jonathan AC Roberts wrote:

Here you are courtesy of ChatGPT:
Yes, it’s possible to run specific stored procedures (SP1 and SP2) in parallel and then have SP3 and SP4 execute in series within a main stored procedure in SQL Server. Since SQL Server doesn't support built-in parallel processing directly within T-SQL, you would typically use SSIS for orchestration or leverage asynchronous processing in SQL Server.
Two possible approaches:
Approach 1: Control Parallel Execution in SSIS In SSIS, create a package that orchestrates the parallel execution of SP1 and SP2, then sequentially executes SP3 and SP4. Here’s a high-level setup:
Set up an SSIS Control Flow: Add two Execute SQL Tasks for SP1 and SP2. Configure them to run in parallel (i.e., ensure no dependencies between them). Set up precedence constraints so that SP3 only runs after both SP1 and SP2 are complete, and SP4 follows SP3.
Configure Task Parallelism: Control maximum parallel execution by setting the MaxConcurrentExecutables property on the package level if needed.
Error Handling: Add error handling to manage task failures and retries as needed. Approach 2: Parallel Execution Using Asynchronous T-SQL If you need to control this within SQL Server itself, you can use Service Broker or SQLCLR. Here’s a simplified example using SQL Server Agent to trigger SP1 and SP2 in parallel, then wait for completion before executing SP3 and SP4 in sequence.
Approach 2: Parallel Execution Using Asynchronous T-SQL If you need to control this within SQL Server itself, you can use Service Broker or SQLCLR. Here’s a simplified example using SQL Server Agent to trigger SP1 and SP2 in parallel, then wait for completion before executing SP3 and SP4 in sequence.
CREATE PROCEDURE Main_SP
AS
BEGIN
    DECLARE @SP1_Handle INT, @SP2_Handle INT;

    -- Start SP1 in an async session
    EXEC sp_start_job @job_name = 'SP1_Job';
    SET @SP1_Handle = SCOPE_IDENTITY();

    -- Start SP2 in an async session
    EXEC sp_start_job @job_name = 'SP2_Job';
    SET @SP2_Handle = SCOPE_IDENTITY();

    -- Wait for SP1 and SP2 to finish
    WAITFOR DELAY '00:00:10'; -- Adjust delay based on expected completion time for SP1/SP2

    -- Ensure SP1 and SP2 are complete
    WHILE EXISTS (SELECT * FROM msdb.dbo.sysjobactivity WHERE job_id IN (@SP1_Handle, @SP2_Handle) AND stop_execution_date IS NULL)
    BEGIN
        WAITFOR DELAY '00:00:05';
    END

    -- Run SP3 and SP4 in sequence
    EXEC SP3;
    EXEC SP4;
END;
GO
Explanation:
SP1 and SP2: Triggered as SQL Server Agent jobs asynchronously to run in parallel. Completion Check: Uses a loop to ensure these jobs are complete before proceeding.
SP3 and SP4: Runs sequentially after SP1 and SP2 have completed.
In summary:
SSIS: Preferred for better control over parallelism and sequence, particularly for complex workflows.
Asynchronous T-SQL: Useful if SSIS isn’t an option, but requires SQL Server Agent job setup for asynchronous execution and monitoring.

Thanks for this. I am using SSIS for implementing parallelism.

water490 SSC Eights! Points: 952 More actions · Answer 5

I think the issue is different so I am going to start a new thread