Reducing the number of queue readers via MAX_QUEUE_READERS parameter might take long time to take effect if there are many messages in the queue
This is another issue that was kindly exposed by our colleague, John Huang, from Canada, through the MCM community distribution list.
Once an activated stored procedure finishes its execution, Service Broker runtime code doesn’t verify whether the maximum number of queue readers has been exceeded. Therefore, when the number of queue readers is reduced via the MAX_QUEUE_READERS parameter in the ALTER QUEUE statement, if there are many messages in the queue, it could take a long time to take effect. Meaning that, as long as there are messages to process in the queue, an activated task will not deactivate even if the maximum number of queue readers has been decreased to any number lower than the number of currently activated tasks. When the queue is emptied, those activated tasks will shutdown and when new messages arrive new tasks will become activated as long as the maximum number hasn’t been surpassed.
At the time this post is published, the fix to this issue is already checked in against the branch from where the next major release of SQL Server coming after SQL Server 2012 will be build. Meanwhile, in any version ranging from 2005 up to 2012, this is the way SSB runtime behaves.
The following annotated script demonstrates how to reproduce the problem:
use master
if DB_ID('ServiceBrokerTest') is not null
drop database ServiceBrokerTest
go
create database ServiceBrokerTest
go
alter database ServiceBrokerTest set enable_broker
go
use ServiceBrokerTest
go
create procedure ActivationProc
as
begin
set nocount on
set xact_abort off
begin transaction
declare @handle uniqueidentifier = null, @message_type sysname = null
declare @ExecutionEngineLogID int
waitfor(
receive top(1)
@message_type = message_type_name,
@handle = conversation_handle
from Queue1
), timeout 10;
if @handle is null
begin
goto ___Quit___ -- can do nothing if handle is empty
end
if @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
or @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
begin
end conversation @handle;
goto ___Quit___
end
if @message_type = 'DEFAULT'
begin
commit
waitfor delay '00:00:20'
end
___Quit___:
if @@trancount > 0
commit
end
go
create queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 10 , EXECUTE AS N'dbo' )
GO
create service Service1 on queue Queue1 ([DEFAULT])
GO
create procedure SendCommand
as
begin
set nocount on
declare @handle uniqueidentifier
select @handle = conversation_handle
from sys.conversation_endpoints
where is_initiator = 1
and far_service = 'Service1'
and state <> 'CD'
if @handle is null
begin
begin dialog conversation @handle
from service Service1
to service 'Service1'
with encryption = off;
end;
send on conversation @handle message type [DEFAULT](0x01);
end
GO
set nocount on
go
exec SendCommand
go 200
--- run this few times until you see 10 activation procedure instances
select * from sys.dm_broker_activated_tasks
go
alter queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )
go
select * from sys.dm_broker_activated_tasks --- you will see 10 records returned
waitfor delay '00:00:30'
select * from sys.dm_broker_activated_tasks -- you will see 10 records returned here too
go
select * from sys.dm_broker_activated_tasks -- 10 threads will stay there for a while
/*
--clean up
use master
alter database ServiceBrokerTest set single_user with rollback immediate
drop database ServiceBrokerTest
*/