Spawning multiple SQL tasks in SQL Server 2005

Question

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.

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)?

I have thought of:

• Launching multiple connections from a client, having each start the appropriate SP.
• Setting up jobs for each SP and starting the jobs from a SQL Server connection or SP.
• Using xp_cmdshell to start additional runs equivalent to osql or whetever
• 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

In the job and cmdshell cases, I'm probably going to run into permissions level problems from the DBA...

SSIS could be a good option - if I can table-drive the SP list.

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.

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.

Answer 1

Is SSIS an option for you? You can create a simple package with parallel Execute SQL tasks to execute the stored procs simultaneously. However, depending on what your stored procs do, you may or may not get benefit from starting this in parallel (e.g. if they all access the same table records, one may have to wait for locks to be released etc.)