Service Broker Whitepaper on MSDN: the 150 trick

A new SQL Customer Advisory Team whitepaper was published recently: Service Broker: Performance and Scalability Techniques authored by Michael Thomassy.

The whitepaper documents the experience of a test done in Microsoft labs that measured the message throughput attainable between three initiators pushing data to a target. This scenario resembles a high scale ETL case. The test was able to obtain a rate a nearly 18000 messages per second, which is a rate that can satisfy most high load OLTP environments. To obtain this rate Michael and his team had to overcome the high contention around updates of the dialogs system tables. He presents a very interesting trick: create 149 dialogs that remain unused and only use every 150th. This way the updates done on the system tables occur on different pages and the high PAGELATCH contention on the page containing the dialog metadata during SEND is eliminated. A very clever trick indeed. But this is a typical OLTP insert/update trick and that is the very point of the whitetpaper: that typical OLTP techniques can and should be applied to Service Broker performance tuning.