Search code examples
sql-serversql-server-2008service-broker

Parallel execution of stored procedures in Transact SQL

Hello again everybody,

I would like to execute stored procedures in parallel in SQL Server 2008. I will not be using any external tool(SSIS, CLR). I decided to take the approach mentioned in rusanu's blog and tried to go with Service broker.

Now to the parallel execution - I have set MAX_QUEUE_READERS to 5 and tried to execute several long running procedures. I have noticed, they were still executed serially. I would assume that they are in different conversations thus activation should occur several times. Please correct me if I am assuming wrongly.

Thanks for explanation.

Solution

  • The activation launches one new thread every 5 seconds at most, if and only if during those 5 seconds there are RECIEVEs that hit bottom of queue (returned empty result set). So to achieve 5 threads you would need to wait for 20 seconds to start with, and during those 20 seconds you should have enough messages that no thread ever hits an idle state.