none
Service Broker Waitfor Receive and Activation Procedure RRS feed

  • Question

  • Can anyone explain this concept to me:

    In Service Broker (SQL Server) a queue can be configured with an "activation" procedure. This is a procedure that will execute when message arrive in the queue.

    Guidance on how to write such a procedure generally involves using the WAITFOR RECEIVE statement to get a message from the queue, and to wait if there are no messages.

    So, the question is -- why are we waiting for the message to arrive in the queue inside the procedure that is executed when messages arrive in the queue?

    Or, another way of putting the same question -- how can the arrival of the message trigger a procedure to wait for the arrival of a message?

    Thanks for any perspective on this. I just don't understand how this is really working.

    Thursday, November 29, 2018 7:46 PM

Answers

  • I guess what I am asking is, if the Activation procedure is activated when a message arrives in the queue, then why does it need to wait for messages to arrive on the queue? Wouldn't one assume that there is definitely a message in the queue, since the procedure was activated by the arrival of a message in the queue? This just makes no sense to me, and I think if I could understand what is really going on, it would help me as I venture into Service Broker applications.

    And that was the question I answered, but I will try again. The point with the WAITFOR is not when the first message arrive. The procedure starts and arrives the WAITFOR RECEIVE statement, but it does not have to wait, since there is a message to consume.

    As I said, an activation procedure typicall consists of a WHILE loop. So when one message has been processed, the activation procedure comes back to the WAITFOR RECEIVE statement and waits a while, just in a case a new message is coming straight away. The length of that WHILE is indeeded determined by the TIMEOUT clause. What is an appropriate value depends on the nature of the application.

    What I have said applies if MAX_QUEUE_READERS is 1, as there is never more than a single instance of the activaiton procedure running. If MAX_QUEUE_READERS is > 1, there can be multiple instances of the procedure running, and a new instance may arrive at an empty queue, because an other queue readers consumed the message that sparked firing up this new instance.

    You can code Service Broker applications without WHILE loops and WAITFOR, but you may get performance issues, since activation processes needs to be created for every message that arrives.

    • Marked as answer by dylbud Friday, November 30, 2018 11:27 PM
    Friday, November 30, 2018 10:45 PM

All replies

  • An activation procedure typically has a WHILE loop with the condition 1 = 1. That is, once a message has been processed, you back to the WAITFOR RECEIVE statement and wait a little while (a few seconds) to see if more messages are coming in. This avoid start-up time if messages are coming in a burst.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 29, 2018 10:39 PM
  • Thanks for your response. 

    I guess what I am asking is, if the Activation procedure is activated when a message arrives in the queue, then why does it need to wait for messages to arrive on the queue? Wouldn't one assume that there is definitely a message in the queue, since the procedure was activated by the arrival of a message in the queue? This just makes no sense to me, and I think if I could understand what is really going on, it would help me as I venture into Service Broker applications.



    • Edited by dylbud Thursday, November 29, 2018 11:20 PM
    Thursday, November 29, 2018 11:17 PM
  • One more question about your response.

    You mention that the WAITFOR RECEIVE statement waits a little (a few seconds, you say). Is that few seconds determined/set by a TIMEOUT clause? 

    Are there scenarios where you should or shouldn't include a TIMEOUT clause? 

    If a TIMEOUT is included, is there an suggested or optimal time to specify?

    Thanks again for any additional perspective.

    Thursday, November 29, 2018 11:43 PM
  • I guess what I am asking is, if the Activation procedure is activated when a message arrives in the queue, then why does it need to wait for messages to arrive on the queue? Wouldn't one assume that there is definitely a message in the queue, since the procedure was activated by the arrival of a message in the queue? This just makes no sense to me, and I think if I could understand what is really going on, it would help me as I venture into Service Broker applications.

    And that was the question I answered, but I will try again. The point with the WAITFOR is not when the first message arrive. The procedure starts and arrives the WAITFOR RECEIVE statement, but it does not have to wait, since there is a message to consume.

    As I said, an activation procedure typicall consists of a WHILE loop. So when one message has been processed, the activation procedure comes back to the WAITFOR RECEIVE statement and waits a while, just in a case a new message is coming straight away. The length of that WHILE is indeeded determined by the TIMEOUT clause. What is an appropriate value depends on the nature of the application.

    What I have said applies if MAX_QUEUE_READERS is 1, as there is never more than a single instance of the activaiton procedure running. If MAX_QUEUE_READERS is > 1, there can be multiple instances of the procedure running, and a new instance may arrive at an empty queue, because an other queue readers consumed the message that sparked firing up this new instance.

    You can code Service Broker applications without WHILE loops and WAITFOR, but you may get performance issues, since activation processes needs to be created for every message that arrives.

    • Marked as answer by dylbud Friday, November 30, 2018 11:27 PM
    Friday, November 30, 2018 10:45 PM
  • Thanks, Erland.

    I really appreciate your detailed explanation. I think I understand this better now. 

    Dylan

    Friday, November 30, 2018 11:27 PM