Powershell Run Stored Procedures in Parallel in Database

Question

We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be working. Is there anymore efficient or recommended method to conduct this, or does the code seem valid?

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure 
    }
}
TestRunParallelExecute
cls

I read many resources below, wanted to come up with easy way using Powershell new ForEach -Parallel function.

Run stored procedures in parallel

https://stackoverflow.com/questions/29193805/executing-stored-procedures-in-parallel

https://stackoverflow.com/questions/48803101/run-several-stored-procedure-in-parallel-in-sql-server

Answer 1

Regarding your question:

Is there anymore efficient or recommended method to conduct this, or does the code seem valid?

I'm not aware of a more efficient or recommended method, but I believe I did validate the basic code you are using.

I created 5 identical stored procedures (SP1 - SP5) that each have a WAITFOR '00:01:00' to force the procedure to wait for 1 minute before exiting.

DROP PROCEDURE IF EXISTS [dbo].[sp1];
go
CREATE PROCEDURE [dbo].[sp1]
AS
BEGIN
    SET NOCOUNT ON;

    waitfor delay '00:01:00'
END

I tweaked your original Powershell code to only select stored procedures where the name was like SP% to make sure that only SP1 - SP5 would be selected.

I started your Powershell script and immediately went back into SSMS to execute sp_WhoIsActive so I could see whether the stored procedures were being executed in parallel and noted that all 5 stored procedures were executing.