Remus Rusanu

Improving SQL Service Broker Performance

A while back I was talking to a Microsoft Support Engineer and he had mentioned that in a high load Service Broker environment such as ours there can be some impressive performance improvement can be achieved by reusing the service broker sessions.

The cost of creating and closing a new conversation for every message is about 4x, while the performance increase when receiving messages is about 10x.  Remus Rusanu talks more about the numbers and shows a solution for reusing conversations on his blog posting Reusing Conversations.

I liked Remus’s solution, but an issue that I had with it was that I didn’t want to have a different conversation for each spid.  If I used this method I would end up having hundreds of conversations open and I’d need a job to close them.  Within our application any number of events can trigger a service broker message to be sent off.  We usually have a few hundred threads logging in and out of the database at any one time.

This required that I take Remus’s solution and make it more flexible before moving it into our environment.  The solution that I’ve come up with supports a single conversation for each process within our application.  And at random intervals those conversations are closed, and new ones are started. Continued »