# SQL Transport Design 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](/transports/sql/callbacks.md). ## 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.CorrelationId` header. This value is kept in a separate column to maintain wire-level compatibility with [NServiceBus.SqlServer](https://www.nuget.org/packages/NServiceBus.SqlServer) transport Version 1. ### ReplyToAddress The `ReplyToAddress` column contains the value of the `NServiceBus.ReplyToAddress` header. This value is kept in a separate column to maintain wire-level compatibility with [NServiceBus.SqlServer](https://www.nuget.org/packages/NServiceBus.SqlServer) transport Version 1. ### Recoverable The `Recoverable` column should always contain the value `1` to ensure wire-level compatibility with [NServiceBus.SqlServer](https://www.nuget.org/packages/NServiceBus.SqlServer) transport Version 1. ### Backwards Compatibility The `CorrelationId`, `ReplyToAddress` and `Recoverable` columns are required for backwards compatibility with version 1 of the [NServiceBus.SqlServer](https://www.nuget.org/packages/NServiceBus.SqlServer) transport. When receiving messages sent by endpoints that use later versions, the values of correlation ID and reply-to address should be read from the headers (`NServiceBus.CorrelationId` and `NServiceBus.ReplyToAddress`) instead. The value `Recoverable` can be ignored as it is always `true`/`1`. When sending messages to endpoints that use later versions, the values of correlation ID and reply-to address columns could be set to `NULL` with the actual values provided in the headers (`NServiceBus.CorrelationId` and `NServiceBus.ReplyToAddress`). The value `Recoverable` should always be `true`/`1`. ### Expires The `Expires` column contains the optional date and time when the message will 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](/transports/sql/discard-expired-messages.md). 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: ```sql 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. ### BodyString The `BodyString` column contains the message body formatted in a human-readable format. It must be explicitly enabled using transport configuration options: ```cs var transport = new SqlServerTransport("connectionString") { CreateMessageBodyComputedColumn = true }; ``` ### 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. A non-clustered index on the `RowVersion` column allows the receive query to efficiently locate the message with the lowest `RowVersion`. If this index is missing, the transport logs a warning recommending that it be created. Systems created with earlier transport versions may still use a clustered index; see the [non-clustered index upgrade guide](/transports/upgrades/sqlserver-non-clustered-idx.md) for migration steps. ## 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. SQL Server transport operates in two modes: *peek* and *receive*. It starts in the *peek* mode and estimates the number of pending messages from the difference between the highest and lowest `RowVersion` values in the queue table. If the estimate is greater than 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 a 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 have 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. A value less than 100 milliseconds is rejected because it puts too much unnecessary stress on the database, while a value larger than 10 seconds should be used with caution as it may result in messages backing up in the queue. ### Queue peek settings #### Peek delay configuration Use the following code: ```cs var transport = new SqlServerTransport("connectionString") { QueuePeeker = { Delay = TimeSpan.FromSeconds(5) } }; ``` Read more information about [tuning endpoint message processing](/nservicebus/operations/tuning.md).