How to execute in parallel using Transaction SQL?

Question

I need to call a stored procedure with hundreds different parameters in a scheduled SQL Agent job. Right now it's executed sequentially. I want to execute the stored procedure with N (e.g. N = 8) different parameters at the same time.

Is it a good way to implement it in Transaction SQL? Can SQL Server Service broker be used for this purpose? Any other options?

Answer 1

There is mention in a comment on the question of a table that holds the various parameters to call the proc with, and that the execution times vary a lot across the parameter values.

If you are able to add two fields to the table of parameters--StartTime DATETIME and EndTime DATETIME--then you can create 7 more SQL Agent Jobs and have them scheduled to run at the same time.

The Job Step of each Job should be the same and should be similar to the following:

DECLARE @Params TABLE (ParamID INT, Param1 DataType, Param2 DataType, ...);
DECLARE @ParamID INT,
        @Param1Variable DataType,
        @Param2Variable DataType,
        ...;

WHILE (1 = 1)
BEGIN
   UPDATE TOP (1) param
   SET    param.StartTime = GETDATE()  -- or GETUTCDATE()
   OUTPUT INSERTED.ParamID, INSERTED.Param1, INSERTED.Param2, ...
   INTO   @Params (ParamID, Param1, Param2, ...)
   FROM   Schema.ParameterTable param
   WHERE  param.StartTime IS NULL;

   IF (@@ROWCOUNT = 0)
   BEGIN
      BREAK; -- no rows left to process so just exit
   END;

   SELECT @ParamID = tmp.ParamID,
          @Param1Variable = tmp.Param1,
          @Param2Variable = tmp.Param2,
   FROM   @Params tmp;

   BEGIN TRY
      EXEC Schema.MyProc @Param1Variable, @Param2Variable, ... ;

      UPDATE param
      SET    param.EndTime = GETDATE() -- or GETUTCDATE()
      FROM   Schema.ParameterTable param
      WHERE  param.ParamID = @ParamID;
   END TRY
   BEGIN CATCH
      ... do something here...
   END CATCH;

   DELETE FROM @Params; // clear out last set of params
END;

That general structure should allow for the 8 SQL Jobs to run until all of the parameter value sets have been executed. It accounts for that fact that some sets will run faster than others as each Job will just pick the next available one off the queue until there are none left, at which time the Job will cleanly exit.

Two things to consider adding to the above structure:

• A way of resetting the StartTime field to be NULL so that the row can re-run later
• A way of handling errors (i.e. clean up of rows where StartTime IS NOT NULL AND EndTime IS NULL and the DATEDIFF between StartTime and GETDATE / GETUTCDATE is too much. A TRY / CATCH could do it by either setting StartTime back to NULL to get re-run OR maybe add a 3rd field for ErrorTime DATETIME that is reset to NULL at the start of the run (like the other 2 fields) but only set if an error happens. Those are just some thoughts.