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

Service Broker External Activator response take long

I have two databases on SQL Server 2014, SourceDB and LogDB. On SourceDB, Service Broker; and on server, Service Broker External Activator service are activated.

On SourceDB I have TargetQueue of which a table's (Product) insert trigger sends changes on TargetQueue and TargetQueue has Event notification which nudges my external exe client. Inside exe client I finally dequeue data via WAITFOR(RECEIVE TOP (1)).. and log them directly to LogDB.

So, when I start the SBEA service and on very first insertion into table a/a few record (after delete all records), TargetQueue immediately filled but the interval from time of insertion to SourceDB till insertion to LogDB is approx 3-6 seconds, event notification based time consumption here I guess, not sure. For further insertions after this, the interval becomes 100ms as seen below.

First

First

Further

Further

  1. Why is the first insertion take too long, why after delete all records of table, it becomes to take long again? Why, further ones take shorter than the first?
  2. Can I decrese the interval under 10ms as I can achieve the almost same structure with SQLCLR under 10ms and the fastest response is crucial for my application as well? (Both structures are on same SQL Server Instance works locally)
Solution

  • You can streamline the process by ditching the External Activator and the Event Notification. Instead have your program continuously running WAITFOR (RECEIVE directly on the target queue in a loop.

    Here's a sample to get you started: https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316