In SQL Server Transport each queue is represented as table inside a database. Depending on the endpoint configuration, each endpoint might use multiple queues/tables e.g. for callbacks.
The queue table consists of the following columns
Id is a
uniqueidentifier generated by the sending code. It is not used by SQL Server transport itself.
CorrelationId column contains the value of
NServiceBus. header. This value is kept in a separate column to maintain wire-level compatibility with transport Version 2.
ReplyToAddress column contains the value of
NServiceBus. header. This value is kept in a separate column to he maintain wire-level compatibility with transport Version 2 and lower.
Recoverable column should always contain the value
1 to ensure wire-level compatibility with transport Version 2 and lower.
Expires column contains the optional date and time when the message is going to expire. An expired message is dropped by the transport. Depending on version, expired messages might be actively purged from the queue. For details see discarding expired messages.
There is a non-clustered index on the
[Expires] column. This index speeds up the purging of expired messages from the queue table. If the SQL Server transport discovers that a required index is missing, it logs an appropriate warning. The following SQL statement can be used to create the missing index:
create nonclustered index [Index_Expires] on [schema].[queuename] ( [Expires] asc ) include ( [Id], [RowVersion] )
Headers column contains a JSON representation of message headers.
Body column contains the serialized message body.
RowVersion column is used to define the FIFO order of the queue. It is auto-incremented by SQL Server (
identity(1,1)). The receive message T-SQL query returns a message with the lowest value of
RowVersion that is not locked by any other concurrent receive operation.
The clustered index of the queue table is based on the
RowVersion column to ensure the new messages are always added at the end of the table.
The following section describes the runtime behavior of SQL Server transport when sending and receiving messages.
Messages are sent by executing an
insert command against the queue table.
Messages are received by executing a
delete command against the queue table. The
delete is limited to a row with the lowest
RowVersion not locked by other concurrent
delete. This ensures that multiple threads within an endpoint instance and multiple instances of the same scaled-out endpoint can operate at full speed without conflicts.
SQL Server transport operates in two modes: peek and receive. It starts in the peek mode and checks, using a
select count query the number of pending messages. If the number is greater then zero, it switches to the receive mode and starts spawning receive tasks that use the
delete command to receive messages.
The maximum number of concurrent receive tasks never exceeds the value set by
LimitMessageProcessingConcurrencyTo (the number of tasks does not translate to the number of running threads which is controlled by the TPL scheduling mechanisms).
When all tasks are done the transport switches back to the peek mode.
In certain conditions the initial estimate of number of pending messages might be wrong e.g. when there is more than one instance of a scaled-out endpoint consuming messages from the same queue. In this case one of the receive tasks is going to fail (
delete returns no results). When this happens, the transport immediately switches back to the peek mode.
The default peek interval, if there is has been no messages in the queue, is 1 second. The recommended range for this setting is between 100 milliseconds to 10 seconds. If a value higher than the maximum recommended settings is used, a warning message will be logged. While a value less than 100 milliseconds will put too much unnecessary stress on the database, a value larger than 10 seconds should also be used with caution as it may result in messages backing up in the queue.
Use the following code:
var transport = endpointConfiguration.UseTransport<SqlServerTransport>(); transport.WithPeekDelay(TimeSpan.FromSeconds(5));
Read more information about tuning endpoint message processing.