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

--running 1 sec
Print 'End of Procedure_A'


--running 20 sec
Print 'End of Procedure_B'


--running 40 sec
Print 'End of Procedure_C'


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.

Parallelism - založení projektu a package - ukázka

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.

Parallelism - vytvoření Execute SQL Task pro každou z procedur A,B,C,D - ukázka

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.

SSIS Parallelism - vytvoření SSIS connection manageru - ukázka

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.

Parallelism - nastavení Execute SQL task na proceduru A - ukázka

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.

Parallelism - stav po nastavení Execute SQL task pro všechny procedury - ukázka

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).

Parallelism - Testování paralelního zpracování procedur - ukázka

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *