Hello All:
I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior. Service Broker relies on "Queues" to store messages that need to be processed. Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue). A Queue can have an "Activation Stored Procedure" associated with it. This procedure is what actually processes the messages in the Queue.
The first behavior I obeserved related to the setting of a Queue's RETENTION parameter. The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives. By default, the parameter's value is "OFF" (meaning it will not retain messages). In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message. I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail. I was unable to capture it, because the Queue's RETENTION parameter was "OFF". When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful. The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.
The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting. This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure. As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting. This allows parallel processing of messages in the Queue. There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages. In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.
If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely. Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue. I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming. The e-mails did not stop until I executed an ALTER QUEUE statement. I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;" After that, the changes were reflected in the procedure.
Be aware of this behavior as you design your Queues.
"The moral of the story is you apparently need to have RETENTION = ON if you need to capture information from a Queue."
I wrong about this. The reason I wasn't capturing the information the first time around was because I didn't alter the queue to reset the STATUS. I have since been able to turn off RETENTION while having the ability to capture information from the queue.
|||Ken, some necessary corrections:
- conversation_handle identifies conversations endpoints, not messages. Initiator and target have distinct conversation_handles. Conversation_id is identifies conversations and is shared by initiator and target. A message is unique by conversation_handle AND message sequence number.
- RETENTION has nothing to do with the capability to 'capture' conversation handles. To obtain the the conversation handle on the initiator side one must get it from the output of BEGIN DIALOG. To obtain the conversation handle on the target side one must put the conversation_handle into the RECEIVE projection list. RETENTION is totally unrelated to this, it is intended for helping application implement compensation logic on failed dialogs. RETENTION keeps a copy of sent and received messages for the duration of the conversation. These messages are visible for the SELECT statement, but not for the RECEIVE statement.
- any stored proc that has an infinite loop, once launched, will stay in memory until killed (explicitly or implicitly) or until server shutdown. This is not specific to activation, is general SQL Server behavior. The only difference is that when this happens by invocation from a user connection it is usually killed implicitly, by that connection being closed or by the batch being aborted. Turning the queue OFF will cause the infinite WAITFOR(RECEIVE...) in the procedure to error and thus exit the RECEIVE loop. One could just as easily KILL the procedure (lookup the SPID in sys.dm_broker_activated_tasks). Needless to say, activated procedures with infinite loops are a very bad practice. It leads to unnecessary consumtion of server resources (threads at the very least) when there are not messages to receive.
- altering the code of an stored proc does not affect the exisiting running instances of that stored proc. This behavior is general in SQL Server, not particular to activated stored procs. Only new invocations of the stored proc will detect the change.
I hope this clarifies some of the behavior you observed.
HTH,
~ Remus
|||Thanks for the clarifications. I wasn't thinking of the RECEIVE LOOP as an infinite loop, since you don't see the timer on the Query Editor window keep running, but it obviously is one. I also wasn't clear about the circumstances under which a stored procedure stays in memory, so thanks for that information as well.
Now I want to show you a code sample to clarify what I am doing. Is this what you mean by "put the conversation_handle into the RECEIVE projection list"?
<code>
USE ServiceBrokerTest
GO
/*
DROP PROCEDURE usp_FTPService
*/
ALTER PROCEDURE usp_FTPService
AS
BEGIN
DECLARE @.conversationHandle UNIQUEIDENTIFIER
DECLARE @.messageBody NVARCHAR(MAX)
DECLARE @.messageTypeName SYSNAME
DECLARE @.eMailBody NVARCHAR(MAX)
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE @.messageTypeName=message_type_name,
@.conversationHandle=conversation_handle,
@.messageBody=message_body
FROM [FTPQueue])
-- If a message was received, process it, else skip
IF (@.@.rowcount <= 0)
BREAK;
-- Process ObjectFTPStartRequest message
IF @.messageTypeName = N'ObjectFTPStartRequest'
BEGIN
SET @.eMailBody = 'FTP job for Object conversation ' + CAST(@.conversationHandle AS NVARCHAR(MAX)) + ' completed'
EXEC msdb.dbo.sp_send_dbmail @.profile_name = 'Ken',
@.recipients = 'username@.domain.com',
@.subject = 'FTP Service Response (ASP)',
@.body = @.eMailBody;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE [LookupRaceFTPStartResponse]
(N'<ObjectFTPStartResponse>
FTP job for Object completed
</ObjectFTPStartResponse>')
END CONVERSATION @.conversationHandle
END
COMMIT
END
COMMIT
END
</code>
If that's what you meant, then that's what I was doing. The reason I was having a problem was solely due to the fact that the first version of the procedure was still running in memory.
|||To put the conversation_handle into the projection list it simply means to have the conversation_handle in the list of columns you RECEIVE. You are doing this fine.
The code you posted needs two changes:
- the WAITFOR(RECEIVE ...) must have a timeout. Typically, the value should be a few seconds. The ideea is that the procedure should linger a bit, in hope a new message arrives and it can be processed. Too short interval and the procedure is gonna miss new messages, exiting and being re-launched, which is a somewhat expensive operation. Too long and your procedure is going to consume server resources w/o doing anything.
- You must add a TOP(1) clause to the RECEIVE. Otherwise you will receive multiple messages, but only process the last one.
HTH,
~ Remus
|||I don't think it's proper to say the TIMEOUT is required since the T-SQL language does not require a TIMEOUT with the WAITFOR clause. If it were required I would get a syntax or runtime error upon execution of the code.
As for whether it's a good idea to use a TIMEOUT, here's what Roger Wolter (Microsoft's Group Program Manager for SQL Server Service Broker) says in his book "The Rational Guide to SQL Server 2005 Service Broker" (Page 56):
"A WAITFOR with no timeout means the RECEIVE will wait for a message no matter how long it takes. Waiting for a message to appear on the queue is generally more efficient than polling the queue by using the RECEIVE command periodically. The main exception to this would be a low priority queue that is only polled for messages periodically when higher priority queues are empty."
As for the use of the TOP clause, I quote Roger again (again on Page 56):
"Retreiving all the messages available utilizes fewer server resources than multiple RECEIVE commands, so you should avoid the TOP clause whenever possible. The main reason to use the TOP clause is in stored procedures where receiving one message at a time into TSQL variables simplifies programming, or when there's a chance that a RECEIVE command will retrieve more messages than you want to process in a single transaction."
|||Indeed, the TIMEOUT is not syntactically required. But no TIMEOUT in activated stored proc loop practicaly turns it into an infinite loop and you'll run into the problems you encountered.
Assignment in RECEIVE projection list (e.g. @.conversation_handle = conversation_hanlde) requires a TOP(1). It is true that using the TOP clause is less efficient, but otherwise the procedure gets significantly more complex. You'd have to RECEIVE INTO @.tableVariable, open a cursor over the table variable and scan this cursor. That is what the "simplifies programming" part of he's quote reffers to. You either use a simple T-SQL program (i.e. @.variables), but pay the penalty of the TOP clause, or you use a more complex T-SQL program (i.e. @.tablevariable and cursor). Roger himself mentions this, see the two examples of RECEIVE on page 57.
Note that if 99% of your RECEIVEs would return only one message anyway (as is often the case), then the TOP is not expensive at all.
See the Receive Loop example on page 63, you'll see that Roger uses both a TOP(1) clause and a 5s TIMEOUT.
HTH,
~ Remus
No comments:
Post a Comment