Service Broker locks

The situation: I use Service Broker to develop data push approach.

Now I consider a scenario:

• Use separate DB ( called Broker ) which has services, queues and stored procedure to send data.

• Use triggers on necessary databases and tables which transform data to JSON and execute SP from Broker database to send data. I have 39 databases / 264632 tables / separated trigger in every table for I/U/D. 793896 triggers at all. Yes, I know that it's huge but we have such data model and I'm not able to change it.

• Now I don't use Activation Stored Procedure, because some client will consume data from SB target queue.

Scripts for Service Broker installation in Broker database:

```
-- installation
use master
go
if exists ( select * from sys.databases where name = 'Broker' )
begin
alter database [Broker] set restricted_user with rollback immediate;
drop database [Broker];
end
go
create database [Broker]
go
alter database [Broker] set enable_broker with rollback immediate;
alter database [Broker] set read_committed_snapshot on;
alter database [Broker] set allow_snapshot_isolation on;
alter database [Broker] set recovery full;
go
use [Broker]
go
create message type datachanges_messagetype
validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.datachanges_initiatorqueue
with status = on
, retention = off
, poison_message_handling ( status = on )
on [default];
go
create queue dbo.datachanges_targetqueue
with status = on
, retention = off
, poison_message_handling ( status = on )
on [default];
go
create service datachanges_initiatorservice
on queue datachanges_initiatorqueue
( datachanges_contract );
go
create service datachanges_targetservice
on queue datachanges_targetqueue
( datachanges_contract );
go
-- conversation additional table
create table dbo.[SessionConversationsSPID] (
spid int not null
, handle uniqueidentifier not null
, primary key (

IS it ok to use one queue and ~800000 triggers? :) I mean maybe there are some thresholds I need to consider.

No. Not really. You must ensure that your triggers are always short-running or your throughput will suffer.

Having 800000 triggers write to a single queue is not going to be a good idea. A queue is backed by a regular table, and at some scale page hotspots are going to be your bottleneck. And:

Messages sent to services in the same instance of the Database Engine
are put directly into the queues associated with these services.

SEND

If your target service is on a remote SQL Server instance then the messages will be written and committed to to each database's transmission queue. But for target queues on the same instance messages go directly to the target queue.

I think the bottom line is that writing directly to the target queue is not the right solution here. Imagine having an empty target queue at the time of peak transaction throughput. That queue's backing table simply doesn't have enough pages to spread out the page latching to accommodate a large number of concurrent writers needed in this scenario.

And if all your tables are in the same database, then the transmission queue could become the bottleneck. But the transmission queue has a different structure than normal queues. The transmission queue has a single clustered index:

select i.name index_name, i.type, c.name, c.column_id, t.name type_name, c.max_length, ic.key_ordinal
from 
sys.indexes i 
join sys.index_columns ic
  on ic.object_id = i.object_id
join sys.columns c
  on c.object_id = ic.object_id
 and c.column_id = ic.column_id
join sys.types t
  on t.system_type_id = c.system_type_id
  and t.user_type_id =c.user_type_id
where c.object_id =  object_id('sys.sysxmitqueue')

outpts

index_name  type name         column_id   type_name             max_length key_ordinal
----------- ---- ------------ ----------- --------------------- ---------- -----------
clst        1    dlgid        1           uniqueidentifier      16         1
clst        1    finitiator   2           bit                   1          2
clst        1    msgseqnum    8           bigint                8          3

So you won't have hot page contention on the transmission queue, and you'll have as many insertion points as you have dialog conversations (dlgid).

A normal queue has two indexes, a clustered index on

(
status,
conversation_group_id,
priority,
conversation_handle,
queuing_order
)

and a non-clustered index on

(
status,
priority,
queuing_order,
conversation_group_id,
conversation_handle,
service_id
)

which you can see with this query

select q.name queue_name, i.name index_name, i.index_id, ic.index_id, i.type, c.name column_name, c.column_id, t.name type_name, c.max_length, ic.key_ordinal
from 
SYS.SERVICE_QUEUES q
join sys.internal_tables it 
    ON it.parent_object_id = q.object_id
join sys.indexes i
    on i.object_id = it.object_id
join sys.index_columns ic
  on ic.object_id = i.object_id
  and ic.index_id = i.index_id
join sys.columns c
  on c.object_id = ic.object_id
 and c.column_id = ic.column_id
join sys.types t
  on t.system_type_id = c.system_type_id
  and t.user_type_id =c.user_type_id
order by q.object_id, i.index_id, ic.key_ordinal

So you might be better off moving the target service to a remote SQL instance. This would offload and writing and reading of the target queues, and might have less of a bottleneck. Your triggers would only have to put the message on the transmission queue, which is what you thought was happening in the first place.

You can watch the routing and transmission queue usage with an Extended Events session like:

CREATE EVENT SESSION [ServiceBrokerRouting] ON SERVER 
ADD EVENT sqlserver.broker_dialog_transmission_body_dequeue,
ADD EVENT sqlserver.broker_dialog_transmission_queue_enqueue,
ADD EVENT sqlserver.broker_forwarded_message_sent,
ADD EVENT sqlserver.broker_message_classify,
ADD EVENT sqlserver.broker_remote_message_acknowledgement

Also in your current design and in the remote service option, you can see from the index structures how how reusing the right number dialog conversations can optimize the solution. Too few and you have locking and page contention issues. Too many and you have overhead of creating and managing them, and you can't do message batching. It looks like you've already read Reusing Conversations, and are using a conversation-per-session pattern, which Remus recommends for this pattern. It would be interesting to see which index the page latch contention is on, and whether it's a leaf or non-leaf page. But in any case queue tables with concurrent SEND and RECEIVE don't usually have enough pages to spread out page latch contention.

So the design alternative is to have the triggers drop changes on N intermediate queues, and then have activation procs on those forward the messages to the single destination queue. You may still have waits on

StackExchange Database Administrators Q#271605, answer score: 7