How to Run SQL Server Stored Procedures in Parallel

Executing stored procedures in Parallel

You are right. Next iteration will start when the last is done. But here, you are running jobs in those iterations not the actual statements. So imagine it like sp_start_job is called in async manner. It will just start the job and return immediately. The job itself may continue to do it's steps.

How can I run sql server stored procedures in parallel?

sp _ start _ job

I'm doing a similar thing at the moment, and the only way I've found to avoid using SSIS or some external shell is to split my load routine into 'threads' manually, and then fire a single master sqlagent job which in turn executes as many sp _ start _ job's as I have threads. From that point, they all run autonomously.

It's not exactly what we're looking for, but the result is the same. If you test the job status for the sub jobs, you can implement your conditional start of sproc 3 as well.

What's the point in 8 cores if we can't use them all at once?

Start stored procedures sequentially or in parallel

I had to research this recently, so found this old question that was begging for a more complete answer. 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):

• Custom application: Write a simple custom app in the language of your choice, using asynchronous methods. Call a SQL stored proc on each application thread.
• SQL Agent jobs: Create multiple SQL jobs, and start them asynchronously from your proc using sp_start_job. 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. (Or have the jobs themselves update your own JOB_PROGRESS table as Chris suggests.) You would literally have to create separate job for each parallel process you anticipate running, even if they are running the same stored proc with different parameters.
• OLE Automation: Use sp_oacreate and sp_oamethod to launch a new process calling the other stored proc as described in this article, also by Gregory A. Larsen.
• DTS Package: Create a DTS or SSIS package with a simple branching task flow. DTS will launch tasks in individual spids.
• Service Broker: If you are on SQL2005+, look into using Service Broker
• 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.

I don't have much experience with Service Broker or CLR, so I can't comment on those options. If it were me, I'd probably use multiple Jobs in simpler scenarios, and a DTS/SSIS package in more complex scenarios.

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.

Execute stored procedures in parallel

The database.Connection.ConnectionString is a static string otherwise you cant compile due to a "An object reference is required for the non-static field, method, or property".

With that in mind, its not the Connection String that's un-intstatiated because its static... and even if you purposely initialized the static string to Null then the error message would be:

InnerException = {"The ConnectionString property has not been initialized."}

Here is a repro and the error cannot be produced unless your GetData Methods are in empty objects:

namespace database
{
public class Program
{
    static void Main(string[] args)
    {
        //WORKS!!
        var repro = new database.Data();
        var task1 = Task.Factory.StartNew(() => repro.GetData1(3));
        var task2 = Task.Factory.StartNew(() => repro.GetData2(5));
        var taskList = new List<Task> { task1, task2 };
        Task.WaitAll(taskList.ToArray());

        //FAILS WITH ERROR REPORTED!!
        repro = null;
        var task1 = Task.Factory.StartNew(() => repro.GetData1(3));
        var task2 = Task.Factory.StartNew(() => repro.GetData2(5));
        var taskList = new List<Task> { task1, task2 };
        Task.WaitAll(taskList.ToArray());
    }
}

class Data
{
    private string connectionString = "Server=.;Database=CRUD_Sample;Integrated Security=True;Asynchronous Processing = True;";
    public DataTable GetData1(int Id)
    {
        DataTable dt = new DataTable();
        using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Get_CustomerbyID", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id });
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
        }
        return dt;
    }

    public DataTable GetData2(int Id)
    {
        DataTable dt = new DataTable();
        using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Get_CustomerbyID", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id });
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
        }
        return dt;
    }
}
}

Debugging

How do you find the source of a NullReferenceException? Apart from looking at the exception itself - the key is a NRE will be thrown exactly at the location where it occurs then you hover your mouse over the variables on the Line Of Code and see which object is null.

How to run multiple stored procedures in parallel using ssis?

If the SPs run for longer, I would have categorized the 1000 SPs into 5-10 numbers, then 1 SSIS package for each category and then Agent Jobs for each package. Then, schedule those jobs at same time.

There are many ways like Loops, Scripting and multiple factors to achieve it. You can test with different ways and go with the best one.

Note: Performance of the SSIS execution depends on your Memory, Processor and Hardware.

---

Related Topics