Division of Labor; SSIS and Sql Server

SSIS was born for parallel processing, while sql server wasn't; well not from the users perspective. Yes, sql server breaks a user's query into parallel processes, but there is no way I can 'fork' within a stored procedure, or do an asynchronous call. I originally designed a process which I outline here "Parallel Processing in SQL"which works beautifully if in fact the work to be done is broken into equal parts.

When threads run amuck.

 Sometimes more isn't always better. I ran into a situation where the number of threads being spawned for a particular process was clogging up the server. The machine this query was running on had 16-cpus running sql server 2005 with 16GB of memory, far from a slouch. I fired up this query and did a quick check of the active processes to find over 40 threads attached to these processes. To add to this I had 8 of these queries going. I figured this would be great, parallel processing, I should be done in no time at all. Well...not quite. I ran into an error at night:

Parallel processing in SQL Server

There have been manyoccasionswhere I've needed to run multiple copies of the same process for performance reasons. Let's say for example that a stored procedure can process 10 records/sec, but I have 1,000,000 records to process. It would take 27.7 hours to process at the previous rate. If I could break up this process into discrete pieces I could run multiple copies and reduce the total run time in a linear fashion (that is if it takes 10 hours to run 1 copy, it would take 5 hours to run 2 copies...1 hour to run 10 copies). There is a computer operation known as, Modulo.