Run stored procedures in parallel

Question

I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time.

I'm using SQL 2014

The reason for this is that the procedure takes around 7 hours to complete. It actually does the same process many times. So for instance it might build a new database and tables for each branch.

What I want to do is break down the stored procedure so I can run in per branch but then run each query in parallel. I've run tested this by running it in separate query windows and it runs almost 80% quicker.

Can anyone give me a dummies guide into running queries in parallel?

Answer 1

At one point I answered this question over at StackOverflow, but it seems like it would be useful to have that information at DBA.SE as well, revised and updated.

Just to be totally explicit: TSQL does not (by itself) have the ability to launch other TSQL operations asynchronously.

That doesn't mean you don't still have a lot of options (some of them mentioned in other answers):

• SQL Agent jobs: Create multiple SQL jobs, and either schedule them to run at the time desired, or start them asynchronously from a "master control" stored proc using sp_start_job. If you need to monitor their progress programatically, just make sure the jobs each update a custom JOB_PROGRESS table (or you can check to see if they have finished yet using the undocumented function xp_sqlagent_enum_jobs as described in this excellent article by Gregory A. Larsen). You have to create as many separate jobs as you want parallel processes running, even if they are running the same stored proc with different parameters.
• SSIS Package: Create an SSIS package with a simple branching task flow. SSIS will launch those tasks in individual spids, which SQL will execute in parallel.
• Custom application: Write a simple custom app in the language of your choice (C#, Powershell, etc), using the asynchronous methods provided by that language. Call a SQL stored proc on each application thread.
• OLE Automation: In SQL, use sp_oacreate and sp_oamethod to launch a new process calling each other stored proc as described in this article, also by Gregory A. Larsen.
• Service Broker: Look into using Service Broker, a good example of asynchronous execution in this article.
• CLR Parallel Execution: Use the CLR commands Parallel_AddSql and Parallel_Execute as described in this article by Alan Kaplan (SQL2005+ only).
• Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

If it were me, I'd probably use multiple SQL Agent Jobs in simpler scenarios, and an SSIS package in more complex scenarios.

In your case, unless you're trying to launch 200 separate threads, multiple scheduled Agent jobs sounds like a simple and manageable choice.

One final comment: SQL already attempts to parallelize individual operations whenever it can*. This means that running 2 tasks at the same time instead of after each other is no guarantee that it will finish sooner. Test carefully to see whether it actually improves anything or not.

We had a developer that created a DTS package to run 8 tasks at the same time. Unfortunately, it was only a 4-CPU server :)

*Assuming default settings. This can be modified by altering the server's Maximum Degree of Parallelism or Affinity Mask, or by using the MAXDOP query hint.

Answer 2

Your best bet is to create three separate jobs with the same schedule to kick the jobs off at the same time. Depending on what the jobs are doing you should be careful to monitor blocking and deadlocking.

Another option is create an SSIS package with N number of operators to call the SPs in parallel

Answer 3

You could use Powershell. Assuming you are working with SQL Server you could do something like this: (tested and cleaned up now)

#This script creates a number of connections (one per entry in $Commands) 
# to a SQL Server instance ($Server) and database ($DBName)
#Driver variables


#Set Initial collections and objects    
$Server= "(local)\sql2016cs" ; #Server to connect to
$DBName = "Test" ; #Database to connect to

$Commands = @()
$Commands += "EXEC sp_LogMe 'a'"
$Commands += "EXEC sp_LogMe 'b'"

#Loop through commands array, create script block for establishing SMO connection/query
#Start-Job for each script block
foreach ($sql in $Commands ) {

# All of that extra information after "Smo" tells it to load just v12 (for when you have multiple
#   versions of SQL installed.)  Note: V13 is 2016.
 $cmdstr =@"
`Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$(13).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
`[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
`$SqlConn = New-Object Microsoft.SqlServer.Management.Smo.Server ("$Server")
`$SqlConn.Databases["$DBName"].ExecuteNonQuery("$sql")
"@

#Uncomment the next like to print the command string for debugging
# $cmdstr
#Execute script block in jobs to run the command asyncronously
$cmd = [ScriptBlock]::Create($cmdstr)
Start-Job -ScriptBlock $cmd
}

Note: I took this from something similar I did here that is tested: https://sqlstudies.com/2016/02/24/powershell-script-to-create-multiple-sql-server-connections/

In that one I was running a loop to create a bunch of commands doing the same thing. This script uses the script block to run each command asynchronously but with different actual commands. To make things easier I've put the list of commands you want to run into an array and loop through the array.

Answer 4

I use a C# app with multithread Parallel.ForEach to call sp with different parameters. Have three sections. Init, Body, localFinally

public void NearLinkParallelGeneration(avl_range avl_pending, DateTime dt_start_process)
    {
        var parallelOptions = new ParallelOptions
        {
            MaxDegreeOfParallelism = Environment.ProcessorCount + 2
        };

        // create the partition based on the input
        var partitions = Partitioner
                            .Create(
                                fromInclusive: avl_pending.begin,
                                toExclusive: avl_pending.end,
                                rangeSize: 100
                            )
                            .GetDynamicPartitions();

        Parallel.ForEach(
            source: partitions,
            parallelOptions: parallelOptions,
            localInit: () =>
            {
                NpgsqlConnection conn = new NpgsqlConnection(strConnection);
                NpgsqlCommand cmd = new NpgsqlCommand();
                try
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT * FROM avl_db.process_near_link(@begin, @end, @start_time);";
                    cmd.CommandType = CommandType.Text;

                    NpgsqlParameter p = new NpgsqlParameter("@begin", NpgsqlDbType.Bigint);
                    cmd.Parameters.Add(p);

                    p = new NpgsqlParameter("@end", NpgsqlDbType.Bigint);
                    cmd.Parameters.Add(p);

                    p = new NpgsqlParameter("@start_time", NpgsqlDbType.Timestamp);
                    p.Value = dt_start_process;
                    cmd.Parameters.Add(p);
                }
                catch (NpgsqlException ex)
                {
                    Console.WriteLine(ex.InnerException);
                }
                catch (System.Exception ex)
                {
                    Console.WriteLine(ex.InnerException);
                }

                return new { Connection = conn, Command = cmd };
            },
            body: (source, state, local) =>
            {
                if (local.Connection.State == ConnectionState.Open)
                {
                    string strResult = String.Format("From: {0} - To: {1}", source.Item1, source.Item2);
                    Console.WriteLine(strResult);

                    try
                    {
                        local.Command.Parameters["@begin"].Value = source.Item1;
                        local.Command.Parameters["@end"].Value = source.Item2;
                        local.Command.ExecuteNonQuery();
                    }
                    catch (NpgsqlException ex)
                    {
                        Console.WriteLine(ex.InnerException);
                    }
                    catch (System.Exception ex)
                    {
                        Console.WriteLine(ex.InnerException);
                    }

                    //strResult = String.Format("DONE From: {0} - To: {1}", source.Item1, source.Item2);
                    //Console.WriteLine(strResult);

                }
                return local;
            },
            localFinally: local =>
            {
                local.Command?.Dispose();
                local.Connection?.Dispose();
            }
        );
    }

Answer 5

You can also use ForEach -Parallel in Powershell.

The example below (taken from my question Powershell Run Stored Procedures in Parallel in Database ) will run all stored procedures in a database:

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

Answer 6

As this remind me of a use case I had at work, I'll put how we solve it:

First as already said, I don't think any Unix "nohup"-like exists in SQL: one connexion = one statement, with everything that go along (lock, commit, error...)

We find our way using the free ETL Talend, configuring it to connect to the DB, and run a bunch of parallel job wrapping the stored procedure.

We used the Iterate component and loop as many time as we need, enabling the multi-threads option.