Service Broker SEND performance

Question

Previously I asked about latch contention / locks which can happen during Service Broker work: Service Broker locks. I got a great answer on general question but I still have narrowly focused question.

MSDN ( SEND ):

Messages that are sent to services in other instances of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queues in the remote instances. Messages sent to services in the same instance of the Database Engine are put directly into the queues associated with these services.

Question: Is there a way to force a message to be put in a local transmission_queue even if services are located on the same Database Engine? It could be preferred solution in my circumstances where I have sufficient hardware resources but sometimes I get latch contention.

Why it can help is described in the link above

---

Test after David's answer.

Preparation:

-- Source DB
use master
go
drop database [TestSendMessage]
go
create database [TestSendMessage]
go
alter database [TestSendMessage] set enable_broker with rollback immediate;
go
use [TestSendMessage]
go
create message type datachanges_messagetype 
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestSendMessage_initiatorqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestSendMessage_initiatorservice
  on queue TestSendMessage_initiatorqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH  
    ADDRESS = 'TCP://localhost:4022' ;   
go
grant send on service::[TestSendMessage_initiatorservice] to public;
go

-- Target DB
use master
go
drop database [TestReceiveMessage]
go
create database [TestReceiveMessage]
go
alter database [TestReceiveMessage] set enable_broker with rollback immediate;
go
use [TestReceiveMessage]
go
create message type datachanges_messagetype 
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestReceiveMessage_destinationqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestReceiveMessage_destinationservice
  on queue TestReceiveMessage_destinationqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH  
    ADDRESS = 'TCP://localhost:4022' ;   
go
grant send on service::[TestReceiveMessage_destinationservice] to public;
go

-- endpoint
if not exists ( select * from sys.endpoints where name = 'ServiceBrokerEndpoint' )
create endpoint ServiceBrokerEndpoint
  state = started
  as tcp ( listener_ip = (127.0.0.1), listener_port = 4022)
  for service_broker();
go

-- Tests
use [TestSendMessage]
go

declare @handle  uniqueidentifier = null

begin dialog conversation @handle 
                    from service TestSendMessage_initiatorservice
                    to   service 'TestReceiveMessage_destinationservice'
                      on contract datachanges_contract
                    with encryption = off;

select @handle; -- 0EC9CCEE-13D3-EA11-B4E1-983B8F11A147
;send on conversation '0EC9CCEE-13D3-EA11-B4E1-983B8F11A147' message type datachanges_messagetype( '[{aaa:1}]' );
select * from sys.transmission_queue

And yeah I see that local sys.transmission_queue is used using query and XE Trace:

BUT! Some time later ( maybe several seconds, I don't know exactly ) if I send several messages using the conversation, I do not see messages in sys.transmission_queue and that's what's in the XE:

IF I create a new conversation or new_broker I'll get the same situation: sys.transmission_queue usage at the beginning and then - no

select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) 
    Mar 18 2018 09:11:49 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: ) (Hypervisor)

Answer 1

Perhaps the routing configuration change didn't take effect immediately. To be sure test with a new broker. Try this from a database other than the database containing the target service:

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

GO
ALTER EVENT SESSION [ServiceBrokerRouting] ON SERVER STATE = START;
go
DROP ROUTE [AutoCreatedLocal]
alter database current set single_user with rollback immediate
alter database current set new_broker
alter database current set multi_user

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are delayed

--add the forwarder route
CREATE ROUTE Forwarder
    WITH  
    ADDRESS = 'TCP://localhost:4022' ;   

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are routed through the transmission queue