InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:32:07 GMT20http://www.sqlservercentral.com/Forums/Topic1257078-1281-1.aspxFor those trying to duplicate this experiment, for example to learn some Service broker basics from it, here is the same experimental code with documented improvements in it:[code="sql"]create database t2gouse mastergoalter database t2 set enable_brokergouse t2gocreate table AsyncExecResults ( token uniqueidentifier primary key, start_time datetime null, finish_time datetime null)gocreate queue AsyncExecQueue;gocreate service AsyncExecService authorization dbo on queue AsyncExecQueue ( [DEFAULT] -- default is NOT a keyword here. i.e. it MUST be -- enclosed by square brackets. );GOif object_id('usp_AsyncExecActivated') is not nulldrop procedure dbo.usp_AsyncExecActivated;goif object_id('usp_AsyncExecActivated') is not null drop procedure dbo.usp_AsyncExecActivated;goalter procedure dbo.usp_AsyncExecActivatedasset nocount on;declare @h uniqueidentifier,@messageTypeName sysname,@messageBody varbinary(max),@xmlBody xml,@procedureName sysname,@startTime datetime,@finishTime datetime,@token uniqueidentifier;while 1 = 1begin begin tran; waitfor( receive top(1) @h = conversation_handle, @messageTypeName = message_type_name, @messageBody = message_body from AsyncExecQueue ), TIMEOUT 5000; if not @@rowcount > 0 begin commit tran; break; -- exit while 1 = 1 end if (@messageTypeName = N'DEFAULT') begin select @xmlBody = CAST(@messageBody as xml); select @procedureName = @xmlBody.value('(//procedure/name)[1]', 'sysname'); select @startTime = CURRENT_TIMESTAMP; exec(@procedureName); select @finishTime = CURRENT_TIMESTAMP; select @token = conversation_id from sys.conversation_endpoints where conversation_handle = @h; insert AsyncExecResults (start_time, finish_time, token) values( @startTime, @finishTime, @token); -- The chosen protocol says the conversation ends after the 1st -- message is received. So let SSSB know we're done by ending the -- conversation after each received message. Not ending the conversation -- will leave all conversations open. When you get into the millions -- of open conversations (see sys.conversation_endpoints), your server -- will go slower and slower. end conversation @h; end else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') begin end conversation @h; end -- Don't forget to handle error messages too. -- These also indicate the end of a conversation, just like EndDialog. else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error') begin -- Read the xml document that we were sent in the message body. declare @nCode int; declare @vchDescription nvarchar(2000); declare @xmlError xml; -- (document select @xmlError = convert(xml, @messageBody); with xmlnamespaces( default 'http://schemas.microsoft.com/SQL/ServiceBroker/Error') select @nCode = Errors.error.value( 'Code[1]', 'int'), @vchDescription = Errors.error.value( 'Description[1]', 'nvarchar(max)') from @xmlError.nodes( '/Error[1]') as Errors(error); -- Any text output by a SSSB queue reader will by default end up in the sql log, -- so I just write out the output with nowait to get errors shown in the sql log. -- Using "with log" would put the error in the sql log twice: once because of -- the with log, plus once more because all output from an auto activated procedure -- is written into the log. So don't specify with log here. raiserror('Service broker error received. Error %d:%s. (%s)', 0, 0, @nCode, @vchDescription, '%PROC%') with nowait; -- An Error message means the conversation has ended, just like with a EndDialog message. -- So we tell SSSB we got the message and have it clean up our end of the conversation too. end conversation @h; end commit tran;endgoalter queue dbo.AsyncExecQueue with activation ( procedure_name = usp_AsyncExecActivated , max_queue_readers = 25 , execute as owner , status = on ), status = on, retention = off;goif object_id('usp_AsyncExecInvoke') is not nulldrop procedure dbo.usp_AsyncExecInvoke;goalter procedure dbo.usp_AsyncExecInvoke @procedureName sysname, @token uniqueidentifier outputas declare @h uniqueidentifier, @xmlBody xml set nocount on; begin dialog conversation @h from service AsyncExecService to service N'AsyncExecService'--, 'current database' with encryption = off; select @xmlBody = ( select @procedureName as name for xml path('procedure'), type); send on conversation @h (@xmlBody);goif object_id('usp_MyLongRunningProcedure') is not nulldrop procedure dbo.usp_MyLongRunningProcedure;gocreate procedure dbo.usp_MyLongRunningProcedureasbegin waitfor delay '00:00:10';endgotruncate table AsyncExecResults;declare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;goselect * from AsyncExecResultsorder by start_time, finish_time;[/code]Wed, 19 Sep 2012 01:47:45 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1257078-1281-1.aspxYour async handler routine should not exit after each message: Service broker expects its handlers to continue processing messages for as long as there are more messages available. As you discovered, it only rechecks after 5 seconds to see if the queue has emptied. Only if it hasn't then it starts another handler, for as far as the max_queue_readers setting permits it.So the problem you need to fix is first of all in your handler procedure. Try this for example (not tested):[code="sql"]if object_id('usp_AsyncExecActivated') is not null drop procedure usp_AsyncExecActivated;gocreate procedure usp_AsyncExecActivatedasset nocount on;declare @h uniqueidentifier,@messageTypeName sysname,@messageBody varbinary(max),@xmlBody xml,@procedureName sysname,@startTime datetime,@finishTime datetime,@token uniqueidentifier;while 1 = 1begin begin tran; waitfor( receive top(1) @h = conversation_handle, @messageTypeName = message_type_name, @messageBody = message_body from AsyncExecQueue ), TIMEOUT 5000; if not @@rowcount > 0 begin commit tran; break; -- exit while 1 = 1 end if (@messageTypeName = N'DEFAULT') begin select @xmlBody = CAST(@messageBody as xml); select @procedureName = @xmlBody.value('(//procedure/name)[1]', 'sysname'); select @startTime = CURRENT_TIMESTAMP; exec(@procedureName); select @finishTime = CURRENT_TIMESTAMP; select @token = conversation_id from sys.conversation_endpoints where conversation_handle = @h; insert AsyncExecResults (start_time, finish_time, token) values( @startTime, @finishTime, @token); end else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') begin end conversation @h; end commit tran;endgo[/code]This will process your messages sequentially, until for more than 5 seconds the queue could not be emptied by a single instance. If the queue is not empty 5 seconds after the 1st instance got started a 2nd instance will be started, and 2 messages will be processed simultaneously. Then after 10 seconds and the queue is still not empty, a 3rd instance is started and 3 messages are processed at the same time (provided your server has at least 3 cpu's, otherwise you'll just be context switching) and so on, until either the maximum number of max_queue_readers is reached or the queue becomes empty for at least 5 seconds (= the timeout value on your waitfor( receive) statement).In practice 25 queue readers is probably way to high.Tue, 18 Sep 2012 08:03:55 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1257078-1281-1.aspxI would like to call stored procs asynchronously, but there seems to be a delay in service broker so that not all of the procedures finish at around the same time. Actually, the delay is significant at several seconds. I have max readers higher than the amount of procedures I am calling. I have tried using the same conversation, multiple conversations, receiving more than top(1) --- although for high concurrency top(1) should be best --- and using waitfor with a timeout. Here's what it looks like:[code="sql"]create table AsyncExecResults ( token uniqueidentifier primary key, start_time datetime null, finish_time datetime null)gocreate queue AsyncExecQueue;gocreate service AsyncExecService on queue AsyncExecQueue (DEFAULT);GOif object_id('usp_AsyncExecActivated') is not nulldrop procedure usp_AsyncExecActivated;gocreate procedure usp_AsyncExecActivatedasset nocount on;declare @h uniqueidentifier,@messageTypeName sysname,@messageBody varbinary(max),@xmlBody xml,@procedureName sysname,@startTime datetime,@finishTime datetime,@token uniqueidentifier;waitfor(receive top(1)@h = conversation_handle,@messageTypeName = message_type_name,@messageBody = message_bodyfrom AsyncExecQueue), TIMEOUT 5000if (@h is not null)begin if (@messageTypeName = N'DEFAULT') begin select @xmlBody = CAST(@messageBody as xml); select @procedureName = @xmlBody.value('(//procedure/name)[1]', 'sysname'); select @startTime = CURRENT_TIMESTAMP; exec(@procedureName); select @finishTime = CURRENT_TIMESTAMP; select @token = conversation_id from sys.conversation_endpoints where conversation_handle = @h; insert AsyncExecResults (start_time, finish_time, token) values( @startTime, @finishTime, @token); end else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') begin end conversation @h; endendgoalter queue AsyncExecQueue with activation ( procedure_name = usp_AsyncExecActivated , max_queue_readers = 25 , execute as owner , status = on ), status = on, retention = off;goif object_id('usp_AsyncExecInvoke') is not nulldrop procedure usp_AsyncExecInvoke;gocreate procedure usp_AsyncExecInvoke @procedureName sysname, @token uniqueidentifier outputas declare @h uniqueidentifier, @xmlBody xml set nocount on; begin dialog conversation @h from service AsyncExecService to service N'AsyncExecService', 'current database' with encryption = off; select @xmlBody = ( select @procedureName as name for xml path('procedure'), type); send on conversation @h (@xmlBody);goif object_id('usp_MyLongRunningProcedure') is not nulldrop procedure usp_MyLongRunningProcedure;gocreate procedure usp_MyLongRunningProcedureasbegin waitfor delay '00:00:10';endgotruncate table AsyncExecResults;declare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;goselect * from AsyncExecResultsorder by finish_time;[/code]Code borrowed heavily from [url=http://rusanu.com/2009/08/05/asynchronous-procedure-execution/]here[/url] for reference.I have really taken it down to just the bare-bones on purpose here so that I can test performance. What I would like is for there to be as little delay between the first start-time and last finish-time when results are sorted in ascending order. I have done some research and it seems the issue is that service broker is not constantly scanning the queue and then activating whatever is on it. It waits for a bit and then scans again. However, after I run all of the waitfor delay procedures, they should all enter the queue. So by the time SB checks a second time, they should surely all be there for SB to dispatch activation procedures. Thanks for any help!Thu, 23 Feb 2012 20:57:12 GMTblueazul