I am looking for an example of a SP that shows the best practices for internal activation? In BOL this topic describes the typical patter for reading messages from a queue. What is the typical pattern for reading messages from a queue using an internally activated SP? Do we still need to loop (considering the message arrival actually fired the sp)?
Any advice provided would be helpful.
Thanks!
Yes, you should still loop. If you don't loop, you risk getting at maximum only one activated procedure, instead of the value set for max_queue_readers. If a procedure is launched and it exits imedeatly (expecting to be launched again), the activation algorithm timers will be reset by this event, so the activation will never reach the point when it has to launch a new instance of the procedure.
The Activation algorithm is described here: http://msdn2.microsoft.com/en-us/library/ms171601.aspx
Another important reason why you should loop is the need to batch commit. If your procedure RECEIVEs one message, process it and commits, you won't be able to scale. Even the best tuned system will only be able to process only tens of messages per second, maybe a best a few hundred. You have to batch several tens, even hundreds, of messages in one transaction, to be able to process thousands of messages per second. The trick here is to avoid holding up a transaction in wait for a message. That is, if you have a batch pending, do a plain RECEIVE, not a WAITFOR(RECEIVE). If the RECEIVE returns empty, commit the pending batch and only then do the WAITFOR(RECEIVE).
Do linger few seconds in your loop before exiting. That is, have a WAITFOR (RECEIVE), TIMEOUT 1000 (or 2000) to drive the loop. This way, the activated procedure will be already started if a new message arrives quickly after you just drained the queue. Don't linger to much, as you're keeping a server thread occupied.
Don't fake an empty queue. That is, don't have a RECEIVE ... WHERE that will return empty rowset even when there are messages in the queue. This will fool the activation algorithm to think that you drained the queue, and will prevent new instances of the procedure to be launched.
Activated procedures have a different execution context than user connection launched procedures. They are under an EXECUTE AS USER context, thus under all the restrictions described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx. So try to design the procedure avoiding the need to access resources outside the database. If you must, then you'll have to either turn on the trustworthy bit on the database, or folllow these complex steps: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx
Do make sure your procedure handles the system message types:
http://schemas.microsoft.com/SQL/ServiceBroker/Error
http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer
Don't rollback intentionally in your procedure. It will trigger the poison message detection and disable your queue.
Do experiment with various ways of writing the RECEIVE loop, e.g.:
- TOP (1) into T-SQL variables
- INTO @.tableVariable and open cursor over @.tableVariable
- CLR procedure and RECEIVE a whole resultset into a SqlDataReader
See which one gives the bets performance for your environment. Some are better when you're likely to have always one message returned, some are better when you're likely to have many messages returned by RECEIVE. Consider that RECEIVE returnes only messages on one conversation_group.
HTH,
~ Remus
I has been brought to my attention that one of my comments is wrong:
If a procedure is launched and it exits imedeatly (expecting to be launched again), the activation algorithm timers will be reset by this event, so the activation will never reach the point when it has to launch a new instance of the procedure.
This is incorrect, the exiting the procedure does not reset the timer and the activation algorithm works as expected in these conditions: the max_queue_readers is reached.
HTH,
~Remus
