CODEWITHSUNDEEP
c#.netsql-servernservicebus
fouks
fouks

Reputation: 67

NServicebus SQL server performance issues

We have a problem where our NServicebus application can't handle all the messages in the SQL Server transport input queue. The default SQL-query to select messages is:

WITH message AS (SELECT TOP(1) * FROM QueueName WITH (UPDLOCK, READPAST, ROWLOCK) ORDER BY [RowVersion] ASC)
DELETE FROM message
OUTPUT deleted.Id, deleted.CorrelationId, deleted.ReplyToAddress,
deleted.Recoverable, CASE WHEN deleted.Expires IS NOT NULL THEN DATEDIFF(ms, GETUTCDATE(), deleted.Expires) END, deleted.Headers, deleted.Body;

We would like to configure this so that instead of only selecting 1 message we want to select a batch of 100. Is that possible, or do you know another way to improve the performance?

Upvotes: 0

Views: 78

Answers (1)

Ramon Smits
Ramon Smits

Reputation: 2618

Batching isn't designed into the NServiceBus SQL transport. This is because its optimized for transactional consistency between the "queue" and any "data" that gets modified in a single transaction. Meaning, each processed message is one database transaction.

More guidance on transport selection is available at: https://docs.particular.net/transports/selecting

If throughput is more important and you're using different techniques to ensure data consistency like outbox you could select a transport with a lower transaction model to achieve much higher throughputs.

For example, this is listed under the SQL Transport:

Advantages

  • SQL Server is already present in many organizations. This could result in lower licensing and training costs, as well as a reduction in operational risk, since the skills and knowledge required to run SQL Server are already present.
  • Mature tooling, such as SQL Server Management Studio (SSMS) and Azure Data Studio
  • Free to start with the SQL Server Express or Developer editions
  • Easy scale-out through competing consumers. Multiple instances of the same endpoint consume messages from a single queue.
  • Supports distributed transactions, allowing atomic message processing and data manipulation in database systems which also support distributed transactions (e.g. SQL Server), using the Microsoft Distributed Transaction Coordinator (MSDTC)
  • Can store both queues and business data in a single backup, making it easier to restore a system to a consistent state

Disadvantages

  • Adds pressure to the database server due to polling for new messages
  • Depending on throughput, can add significant load to an existing SQL Server installation
  • Can have significant costs in Production for high throughput systems where extra servers or cores are required to support the load = Inherently not designed as a messaging broker which can lead to lower performance and connection limitations when utilized for a larger system with many endpoints

When to select this transport

  • When it's not possible to introduce a native queueing technology
  • When the benefits of introducing a native queueing technology are outweighed by the cost of licensing, training, and ongoing maintenance compared with using existing -SQL Server infrastructure
  • For integration with a legacy application that uses SQL Server, using database triggers
  • When the system has a small number of endpoints or a medium number of endpoints with low throughput.

Upvotes: 0

Related Questions