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 ( 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://docs.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
Typical trigger code in an user database:
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
So, my questions are:
- Sometimes I see long PAGELATCH_EX / PAGELATCH_SH waits during trigger execution. The problem is that latches are waited on target_queue table. I don't understand why I see some kind of hot spot on target queue dbo.datachanges_targetqueue when I send data to Service Broker. I would understand if there was waiting related to sending system tables or transmission queue. I see target queue at resource_description column:
Using dbcc page I see that this page belongs to sys.queue_messages_597577167 which is a wrapper for dbo.datachanges_targetqueue. Total amount of waiting sessions on that moment was ~ 450, so it can be a bottleneck.
that in that time triggers were executed pretty long ( more than 10 sec, when usually it's less then 1 sec ). It happens in random time, so I don't see any dependency here
- My second problem is related to locking also. And also it happens in random time. My fake script to read data from target queue ( external client emulation ) is
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
which is executed periodically can be blocked also because of triggers activity. And I do not understand why.
IS it ok to use one queue and ~800000 triggers? :) I mean maybe there are some thresholds I need to consider.
What are the advantages/disadvantages to use "my" approach ( one db is a sender and a target ) or to use "every db is sender and one target"