title: Executing stored procedures in Parallel
body: <p>I have 35 stored procedures that I need to run in parallel so as to reduce the execution time.</p>
  
  <p>I came across concept of Agent jobs <a href="http://www.codeproject.com/Articles/541834/How-to-execute-multiple-long-running-SQL-Statement" rel="nofollow">here</a>.</p>
  
  <p>The link suggests using this piece of code to achieve this:</p>
  
  <pre><code>CREATE PROCEDURE ExecuteSQL_ByAgentJob_usp(
      @SqlStatemet            VARCHAR(4000),
      @SPNameOrStmntTitle     VARCHAR(100),
      @JobRunningUser         VARCHAR(100) = NULL,
      @JobIdOut               UNIQUEIDENTIFIER OUTPUT
  )
  AS
  BEGIN
  
      SET NOCOUNT ON;  
  
      DECLARE @JobId          UNIQUEIDENTIFIER,
              @JobName        VARCHAR(250) = NULL,
              @DBName         VARCHAR(100) = DB_NAME(),
              @ServerName     VARCHAR(100) = @@SERVERNAME
  
  
      --Creating Unique Job Name by combining @SPNameOrStmntTitle and a GUID.     
      SET @JobName = @SPNameOrStmntTitle + '_' + CONVERT(VARCHAR(64), NEWID()) 
  
      --Currently logged user name will be used to execute the job if not provided one.
      IF @JobRunningUser IS NULL
          SET @JobRunningUser = SUSER_NAME()
  
      --Adds a new job executed by the SQLServerAgent service
      EXECUTE msdb..sp_add_job @job_name = @JobName, @owner_login_name = @JobRunningUser, 
      @job_id = @JobId OUTPUT 
  
      --Targets the specified job at the specified server 
      EXECUTE msdb..sp_add_jobserver @job_id = @JobId, @server_name = @ServerName 
  
      --Tell job for its about its first step.
      EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step1', @command 
      = @SqlStatemet,@database_name = @DBName, @on_success_action = 3 
  
      --Preparing the command to delete the job immediately after executing the statements 
      DECLARE @sql VARCHAR(250) = 'execute msdb..sp_delete_job @job_name=''' + @JobName + ''''
  
      EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step2', @command = @sql 
  
      --Run the job
      EXECUTE msdb..sp_start_job @job_id = @JobId
  
      --Return the Job via output param.
      SET @JobIdOut = @JobId
  END
  </code></pre>
  
  <p>Despite of having read this over and over, I still do not understand which part of it will help execute stored procedures in parallel? If possible, please shed some light on it. I am curious as hell to know which part of the script does this magic.</p>
  
  <p>It is called like this :</p>
  
  <pre><code>SET @Itr = 1 --Seeting the initial value.
      SET @RecCount = (
              SELECT COUNT(*)
              FROM   @Scripts
          )
  
  -----------------PART3------------------------------------
      WHILE (@Itr &lt;= @RecCount)
      BEGIN
          SELECT @sql = t.Script
          FROM   @Scripts t
          WHERE  id = @Itr
          --Just o identify the script name getting first 10 char of the SP
          SET @ScriptTitle = LEFT(REPLACE(@sql, 'EXEC ', ''), 10) 
  
          EXEC ExecuteSQL_ByAgentJob_usp
               @SqlStatemet = @sql,
               @SPNameOrStmntTitle = @ScriptTitle,
               @JobRunningUser = 'sa',
               @JobIdOut = @JobId OUTPUT
  </code></pre>
  
  <p>Is it how it is called in a loop ? But I believe next iteration of loop will start only when last is done executing, so how come it runs in parallel ?</p>