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.
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:
I have found an interesting quirk about table variables and sql server 2005. When I create a table variable to insert records into,all parallelism in the query plan is removed. I found that you can use table variables in joins and they themselves can partake in parallel operations. As an example.
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.