服务代理锁

情况：我使用Service Broker开发数据推送方式。

现在我考虑一个场景：

1. 使用具有服务、队列和存储过程的单独数据库（称为代理）来发送数据。
2. 在必要的数据库和表上使用触发器，将数据转换为 JSON 并从Broker数据库执行 SP以发送数据。我在 I/U/D 的每个表中有 39 个数据库/264632 个表/单独的触发器。793896 触发。是的，我知道它很大，但我们有这样的数据模型，我无法更改它。
3. 现在我不使用激活存储过程，因为一些客户端会消耗来自 SB 目标队列的数据。

在Broker数据库中安装 Service Broker 的脚本：

-- 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 ( spid )
  , unique ( handle )  
)
go
-- SP which is used to send data from triggers
create procedure dbo.trackChanges_send
  @json nvarchar(max)
as 
begin
    set nocount on;

    if ( @json is null or @json = '' )
    begin
        raiserror( 'DWH Service Broker: An attempt to send empty message occurred', 16, 1);
        return;
    end

    declare @handle  uniqueidentifier = null
          , @counter int = 1
          , @error   int;

    begin transaction

    while ( 1 = 1 )
    begin

        select @handle = handle
          from dbo.[SessionConversationsSPID]
          where spid = @@SPID;

        if @handle is null
        begin

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

            insert into dbo.[SessionConversationsSPID] ( spid, handle )
              values ( @@SPID, @handle );

        end;

        send on conversation @handle 
          message type datachanges_messagetype( @json );

        set @error = @@error;

        if @error = 0
          break;

        set @counter += 1;

        if @counter > 5 
        begin
            declare @mes varchar(max) = 'db - ' + @db + '. schema - ' + @sch;
            raiserror( N'DWH Service Broker: Failed to SEND on a conversation for more than 10 times. Source: %s. Error: %i.', 16, 2, @mes, @error );
            break;
        end

        delete from dbo.[SessionConversationsSPID]
          where handle = @handle;

        set @handle = null;

    end

    commit;
        
end
go
-- And dialogs creation to mitigate hot spot problem on sys.sysdesend table.
-- Described here: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd576261
declare @i int, @spid int, @handle uniqueidentifier
select @i = 0, @spid = 50;

while (@i < 150*3000) -- 450000 dialogs
begin
       set @i = @i + 1
       begin dialog @handle 
         from service datachanges_initiatorservice
         to service 'datachanges_targetservice'
         on contract datachanges_contract
        with encryption = off;

       if ((@i % 150) = 0)
       begin
              set @spid += 1;
              insert into dbo.SessionConversationsSPID ( spid, handle ) values (@spid, @handle)
       end
       
end

用户数据库中的典型触发代码：

create trigger [<SCHEMA>].[<TABLE>_TR_I]
    on [<SCHEMA>].[<TABLE>]
    with execute as caller
    after insert
    as
    begin 
    
        set xact_abort off;
        set nocount on;
    
        declare @rc int = ( select count(*) from inserted );
    
        if ( @rc = 0 )
        begin
            return;
        end
    
        begin try

            declare @db_name sysname = db_name();

            declare @json nvarchar(max);
    
            set @json = (
                select getutcdate() as get_date, ''I'' as tr_operation, current_transaction_id() as cur_tran_id, ''<TABLE>'' as table_name, @@servername as server_name, @db_name as db_name, ''<SCHEMA>'' as tenant_schemaname
                      , *
                    from inserted
                    for json auto, include_null_values
            );
    
            exec dbo.trackChanges_send
                 @json = @json;
    
        end try
        begin catch 
            
            declare @error_message nvarchar(max); 
            set @error_message = ''[''       + isnull( cast( error_number()    as nvarchar( max ) ), '''' ) +''] ''
                                             + isnull( cast( error_severity()  as nvarchar( max ) ), '''' )
                                +'' State: ''+ isnull( cast( error_state()     as nvarchar( max ) ), '''' )
                                +'' Trigger: '' + ''[<SCHEMA>].[<TABLE>_TR_I]''
                                +'' Line: '' + isnull( cast( error_line()      as nvarchar( max ) ), '''' )
                                +'' Msg: ''  + isnull( cast( error_message()   as nvarchar( max ) ), '''' );
    
            raiserror( ''DWH Service Broker: An error has been occured while sending data changes. Error: %s'', 0, 0, @error_message ) with log;
    
            return;
        end catch
    end
    go

所以，我的问题是：

1. 有时我会在触发器执行期间看到很长的 PAGELATCH_EX / PAGELATCH_SH 等待。问题是在 target_queue 表上等待闩锁。我不明白为什么当我将数据发送到 Service Broker 时，我会在目标队列dbo.datachanges_targetqueue上看到某种热点。我会理解是否存在与发送系统表或传输队列相关的等待。我在resource_description列 看到目标队列：

使用dbcc 页面，我看到该页面属于sys.queue_messages_597577167，它是 dbo.datachanges_targetqueue 的包装器。那一刻的等待会话总数约为 450，因此它可能是一个瓶颈。

同时，跟踪触发器执行的 xEvent 会话告诉

在那段时间触发器执行的时间很长（超过 10 秒，而通常少于 1 秒）。它发生在随机时间，所以我在这里看不到任何依赖

2. 我的第二个问题也与锁定有关。而且它发生在随机时间。我从目标队列（外部客户端仿真）读取数据的假脚本是

declare @i int = 0;
while ( 1 = 1 )
begin

    declare @mb varbinary( max );
    receive top ( 1000 ) @mb = message_body from dbo.datachanges_targetqueue
    
    set @i = @@rowcount;
    
    if @i = 0
      break;

end

由于触发活动，它也可以被阻止定期执行。我不明白为什么。

3. 可以使用一个队列和约 800000 个触发器吗？:) 我的意思是也许我需要考虑一些门槛。

4. 使用“我的”方法（一个数据库是发送者和目标）或使用“每个数据库都是发送者和一个目标”的优点/缺点是什么