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.
Structure
The queue table consists of the following columns
ID
The Id
is a Guid
/uniqueidentifier
generated by the sending code. It is not used by SQL Server transport itself.
CorrelationId
The 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
The 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
The Recoverable
column should always contain the value 1
to ensure wire-level compatibility with transport Version 2 and lower.
Expires
The 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.
Starting from version 2.2.2, 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
The Headers
column contains a JSON representation of message headers.
Body
The Body
column contains the serialized message body.
RowVersion
The 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.
Behavior
The following section describes the runtime behavior of SQL Server transport when sending and receiving messages.
Sending
Messages are sent by executing an insert
command against the queue table.
Receiving
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.
Versions 2.0.x
Each endpoint running SQL Server transport spins up a fixed number of threads (controlled by MaximumConcurrencyLevel
property of TransportConfig
section) for each input queue. Each thread runs in a loop, using the delete
command to poll the database for messages awaiting processing.
By default, there are 5 input queues created for every endpoint (apart from the main one, there are two for handling timeouts, one for the retries and another one for callbacks). As a consequence, if MaximumConcurrencyLevel
is set to 10, there are 41 threads running and constantly polling the database. The callback queue has a separate concurrency settings which default to 1 polling thread.
Versions 2.1 and above
SQL Server transport uses an adaptive concurrency model. The transport adapts the number of polling threads based on the rate of messages coming in. A separate instance of the algorithm is executed by each polling thread. The algorithm counts consecutive successful and failed poll (delete
) attempts (the attempt succeeds if it finds a message waiting in a queue).
If the number of consecutive successful polls is greater than an internal threshold, a new polling thread is started (provided the MaximumConcurrencyLevel
is not exceeded). On the other hand, if the number of consecutive failed polls is greater than a threshold, the thread dies.
Read more information about tuning endpoint message processing