Recommended Articles
- Asynchronous procedure execution
- How to reliably invoke procedures asynchronously in your database by means of Service Broker Activation. By leveraging the internal activation mechanism, your client does not have keep a connection open, nor does it have to rely on some sort of helping service process.
- Troubleshooting Conversations
- The original article published in 2005 that started this blog. Step by step advice to troubleshoot Service Broker message delivery. Still valid today.
- Reusing Conversations
- Getting the high message throughput in Service Broker depends on proper conversation management. This article shows a technique of reusing conversations based on session ID. Using this technique you can easily achieve throughputs of thousands of messages per send end-to-end between two sites.
- Read-Write deadlock
- A presentation of a common deadlock scenario between a simple SELECT and an UPDATE. This is common deadlock that happens in content management systems when updating the view count for images and posts.
- Signing activated procedures
- Because of the EXECUTE AS impersonation context activated procedures by default cannot access objects in other databases, like msdb.dbo.sp_send_mail, nor server scoped objects like linked servers. This article shows how to use code signing to overcome these problems.
- The Mysterious Notification
- Are you’re using SqlDependency on your site? If you want to learn more about how this feature works and the underlaying technology this article is good starter. A brief presentation into Query Notifications, SqlNotificationRequest and SqlDependency.
Writing Service Broker Procedures
Originally published in 2006 this article goes over every technique of writing Service Broker activated procedures: single message, cursor, set oriented etc. It shows the basic skeleton of each one accompanied with performance measurements. If you are now only able to dequeue about 50 messages per second, read this article to understand how to get to 5000 messages per second.
