Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Service Broker (2005)  Using Service Broker as queue?

Author  Topic  kbbpll Starting Member 1 Post Posted - 2009-03-27 : 14:46:41 We have a (potentially long-running) stored proc that can be executed by possibly thousands of clients simultaneously. This consumes all CPU such that nothing else goes through. We have implemented a home-grown queue to alleviate this - WAITFOR this SPID to be in Top N of queue table before running query - but now have issues with too many WAITFOR threads. I am trying Service Broker on this and would like a "mother may I" approach without getting into having the activation proc do the heavy lifting and passing result set in message, client parsing it out, etc. (The result set can exceed a million rows). What I've tried is this pattern: Begin dialog Send "May I run?" message Receive "Yes you may" reply Run large query Send "I am done" message Receive "Thanks you're done" reply End conversation However, it appears the only "throttling" Service Broker does is how fast activation proc can send the replies; we still get many simultaneous executions of the large query. I'd like activation proc to queue the "Yes you may run" replies while waiting for each "I am done" from the client. Can this be accomplished with Service Broker?