Running more than 1 procedure at a time may not be as simple a task as it seems, especially if individual procedures have dependencies between them – meaning that a set of procedures relies on the execution of other procedures. So, how can we achieve parallelism when running sql server queries? Let’s recap the options available to us:

• Parallelism of procedures within a single session in an SQL script – Not possible
• Parallelism of procedures within the steps of a single job – Not possible
• Parallelism of procedures across multiple jobs – Possible, but we don’t control dependencies

Does anyone have any other ideas? I only think of using an SSIS package (SQL Server Integration Services).

Parallelism and parallel data processing – running multiple SQL procedures and scripts simultaneously with dependency control via SSIS

Running multiple transactions at once is not much of a problem (see the previous summary). We can create multiple jobs scheduled to run at the same time. This approach is suitable when there are no dependencies between procedures or SQL scripts. In the case of dependencies, it’s advisable to use an external tool or utilize the integrated tool SSIS (SQL Server Integration Services). How to do it?

Example Scenario: Let’s have 4 procedures (A, B, C, D), each running for a different duration. Procedures A, B, and C are independent of each other and can be executed in parallel. Procedure D must wait until all other procedures successfully complete before it can be executed.

Let’s Create Test Procedures

• Procedure_A runs for 1 second
• Procedure_B runs for 20 seconds
• Procedure_C runs for 40 seconds
• We only execute Procedure_D after all other procedures have completed

CREATE PROCEDURE Procedure_A
AS
--running 1 sec
WAITFOR DELAY '0:00:01'
Print 'End of Procedure_A'

GO

CREATE PROCEDURE Procedure_B
AS
--running 20 sec
WAITFOR DELAY '0:00:20'
Print 'End of Procedure_B'

GO

CREATE PROCEDURE Procedure_C
AS
--running 40 sec
WAITFOR DELAY '0:00:40'
Print 'End of Procedure_C'

     GO

CREATE PROCEDURE Procedure_D
AS
Print 'End of Procedure_D'

Let’s Create an SSIS Project and Package

The project is created in Visual Studio (SSDT) 2017, but it can be created in any version of SQL Server Data Tools.

From the SSIS Toolbox (on the left), drag and drop the “Execute SQL Task” into the middle area

Do this for each procedure, which means four times. Rename the text of each task to the name of the procedure, and connect arrows from procedures A, B, C to procedure D.

We Need to Attach Our Procedures to the Execute SQL Tasks

First, we need to create a Connection Manager for our SQL Server (see the image). Right-click in the Connection managers area, select New OLE DB connection, fill in the SQL Server instance name and database name, and click OK.

Configure the First Execute SQL Task (Procedure_A)

• Double-click on the task
• In the window, set our Connection Manager
• In the SQL statement section, click the three dots
• In the next window, enter `EXEC Procedure_A`

This completes the configuration, and you should proceed similarly for all other tasks.

After setting up all tasks from the previous step, the crosses should disappear

This means that the tasks are valid, the connection manager works, and the SQL scripts are valid. The SSIS package is ready. You can test if parallel data processing is working.

Final Testing of Parallel Data Processing

Run the task and observe what happens. You will see that procedures A, B, and C are executed at the same time, and procedure D waits until the last of procedures A, B, C finishes (see images).