title: Spawning multiple SQL tasks in SQL Server 2005
body: <p>I have a number of stored procs which I would like to all run simultaneously on the server.  Ideally all on the server without reliance on connections to an external client.</p>
  
  <p>What options are there to launch all these and have them run simultaneously (I don't even need to wait until all the processes are done to do additional work)?</p>
  
  <p>I have thought of:</p>
  
  <ul>
  <li>Launching multiple connections from
  a client, having each start the
  appropriate SP.</li>
  <li>Setting up jobs for
  each SP and starting the jobs from a
  SQL Server connection or SP.</li>
  <li>Using
  xp_cmdshell to start additional runs
  equivalent to osql or whetever</li>
  <li>SSIS - I need to see if the package can be dynamically written to handle more SPs, because I'm not sure how much access my clients are going to get to production</li>
  </ul>
  
  <p>In the job and cmdshell cases, I'm probably going to run into permissions level problems from the DBA...</p>
  
  <p>SSIS could be a good option - if I can table-drive the SP list.</p>
  
  <p>This is a datawarehouse situation, and the work is largely independent and NOLOCK is universally used on the stars.  The system is an 8-way 32GB machine, so I'm going to load it down and scale it back if I see problems.</p>
  
  <p>I basically have three layers, Layer 1 has a small number of processes and depends on basically all the facts/dimensions already being loaded (effective, the stars are a Layer 0 - and yes, unfortunately they will all need to be loaded), Layer 2 has a number of processes which depend on some or all of Layer 1, and Layer 3 has a number of processes which depend on some or all of Layer 2.  I have the dependencies in a table already, and would only initially launch all the procs in a particular layer at the same time, since they are orthogonal within a layer.</p>