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