NServiceBus inefficient SQL with persistence

BCas · February 17, 2022, 11:31am

There is an issue within the codebase when persistence is being used in that Inserts and Updates generated do not match the column description within SQL Server. In these cases SQL Server will add CONVERT functions to the plan which is driving up cpu usage and restricts the actual plans available to SQL Server to use.

DECLARE @MessageId NVarChar(36) SET @MessageId = '1a3161d8-6d2f-429e-a761-ae3c01370460'
DECLARE @Operations NVarChar(2) SET @Operations = '[]'
DECLARE @PersistenceVersion NVarChar(7) SET @PersistenceVersion = '6.1.1.0'

-- Executed query

insert into [dbo].[Services_Response_OutboxData]
(
    MessageId,
    Operations,
    PersistenceVersion
)
values
(
    @MessageId,
    @Operations,
    @PersistenceVersion
)

Plan
[Expr1003] = CONVERT_IMPLICIT(nvarchar(200),[@MessageId],0)
[Expr1004] = CONVERT_IMPLICIT(nvarchar(max),[@Operations],0)
[Expr1005] = CONVERT_IMPLICIT(varchar(23),[@PersistenceVersion],0)

The actual columns in the table have different descriptors on the sizes leading to SQL placing CONVERT functions into the generated plan.

Can this be fixed please as it is restricting the maximum performance that persistence can be used at for us?

Brian

andreasohlund · February 17, 2022, 12:35pm

Hi Brian!

You are correct, I have raised an issue to track a fix for this

github.com/Particular/NServiceBus.Persistence.Sql

Not specifying parameter size for commands force type conversion which results in higher database CPU usage

opened 12:28PM - 17 Feb 22 UTC

andreasohlund

candidate-for-next-release

Not specifying the [size of the parameters](https://docs.microsoft.com/en-us/dot…net/api/system.data.sqlclient.sqlparameter.size) forces the execution engine to convert parameters to the max size specified in the column definition. This increases CPU usage on the database server. While this should apply to all commands issued by persister we have a user report specifically for the outbox inserts https://discuss.particular.net/t/nservicebus-inefficient-sql-with-persistence/2945

can you describe the impact this has on your system? Is this somehow blocking you?

Cheers,

Andreas

BCas · February 17, 2022, 12:56pm

Thanks Andreas.
We have only started using it and this got spotted as an issue. Glad it has been raised already!

Brian

shoffman11 · July 26, 2023, 6:09pm

I noticed this issue in my system recently as well and found this thread and the linked GitHub issue. This still occurs with persistence version 6.5.1. I know that is a bit out-of-date, but I don’t see any release notes indicating it would be fixed in a later version, although open to trying it.

The outbox operations consistently are in the top most duplicated queries in my SQL Server plan cache. Compiling plans for each unique Operations value length requires more CPU usage compared to reusing plans, and caching more plans takes memory away from caching data.

Here’s a sample of duplicate plans for nearly identical statements, except where the Operations parameter length varies:

(@MessageId nvarchar(36),@Operations nvarchar(3690),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(3803),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(3804),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(2012),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(3800),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(3805),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(2054),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(2013),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(3948),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )
(@MessageId nvarchar(36),@Operations nvarchar(2055),@PersistenceVersion nvarchar(7))  insert into [persistence].[Endpoint_OutboxData]  (      MessageId,      Operations,      PersistenceVersion  )  values  (      @MessageId,      @Operations,      @PersistenceVersion  )

I believe this statement from Microsoft Docs site is what is happening:

For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.
(DbParameter.Size Property (System.Data.Common) | Microsoft Learn)

ramonsmits · July 31, 2023, 8:17pm

@shoffman11 This optimization has not yet been applied. I advise to add most details on that issue. This is not considered a bug. Enhancements/features are only added to the latest version.

Do you have more insights on the performance penalty due to this in your environment? If you have can you please add that info to the GitHub issue?

https://github.com/Particular/NServiceBus.Persistence.Sql/issues/873

ramonsmits · July 31, 2023, 8:54pm

FYI: Based on your details I created the following improvement by initializing size for the @Operations parameter:

ramonsmits · October 20, 2023, 5:21pm

FYI: The earlier fix has been tested. Check the github issue for more details.

While testing I discovered that the SQL Transport is affected too and we will fix this too to ensure the queries are optimally making use of the query cache:

github.com/Particular/NServiceBus.SqlServer

Set Size to -1 to optimize SQL Server query plan execution

opened 05:18PM - 20 Oct 23 UTC

ramonsmits

Improvement

### Describe the suggested improvement Various queries are not ideally construc…ted resulting the SQL query cache misses. Delayed queue table: ```sql (@Headers nvarchar(1362),@Body varbinary(177),@DueAfterDays int,@DueAfterHours int,@DueAfterMinutes int,@DueAfterSeconds int,@DueAfterMilliseconds int) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON; DECLARE @DueAfter DATETIME = GETUTCDATE(); SET @DueAfter = DATEADD(ms, @DueAfterMilliseconds, @DueAfter); SET @DueAfter = DATEADD(s, @DueAfterSeconds, @DueAfter); SET @DueAfter = DATEADD(n, @DueAfterMinutes, @DueAfter); SET @DueAfter = DATEADD(hh, @DueAfterHours, @DueAfter); SET @DueAfter = DATEADD(d, @DueAfterDays, @DueAfter); INSERT INTO [NsbSamplesSqlOutbox].[receiver].[Samples.SqlOutbox.Receiver.Delayed] ( Headers, Body, Due) VALUES ( @Headers, @Body, @DueAfter); IF(@NOCOUNT = 'ON') SET NOCOUNT ON; IF(@NOCOUNT = 'OFF') SET NOCOUNT OFF; ``` Queue table: ``` (@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(734),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON; INSERT INTO [NsbSamplesSqlOutbox].[receiver].[Samples.SqlOutbox.Receiver] ( Id, Recoverable, Expires, Headers, Body) VALUES ( @Id, 1, CASE WHEN @TimeToBeReceivedMs IS NOT NULL THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END, @Headers, @Body); IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF; (@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON; INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] ( Id, Recoverable, Expires, Headers, Body) VALUES ( @Id, 1, CASE WHEN @TimeToBeReceivedMs IS NOT NULL THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END, @Headers, @Body); IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF; (@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON; INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] ( Id, Recoverable, Expires, Headers, Body) VALUES ( @Id, 1, CASE WHEN @TimeToBeReceivedMs IS NOT NULL THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END, @Headers, @Body); IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF; (@Id uniqueidentifier,@TimeToBeReceivedMs int,@Headers nvarchar(800),@Body varbinary(max) ) DECLARE @NOCOUNT VARCHAR(3) = 'OFF'; IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON' SET NOCOUNT ON; INSERT INTO [NsbSamplesSqlOutbox].[sender].[Samples.SqlOutbox.Sender] ( Id, Recoverable, Expires, Headers, Body) VALUES ( @Id, 1, CASE WHEN @TimeToBeReceivedMs IS NOT NULL THEN DATEADD(ms, @TimeToBeReceivedMs, GETUTCDATE()) END, @Headers, @Body); IF (@NOCOUNT = 'ON') SET NOCOUNT ON; IF (@NOCOUNT = 'OFF') SET NOCOUNT OFF; ``` Subscriptions: ```sql (@Topic_0 varchar(14),@Topic_1 varchar(13),@Topic_2 varchar(18),@Topic_3 varchar(20)) SELECT DISTINCT QueueAddress FROM [NsbSamplesSqlOutbox].[dbo].[Subscriptions] WHERE Topic IN (@Topic_0, @Topic_1, @Topic_2, @Topic_3) ``` ## Related This was discovered while working on the following SQLP PR: - https://github.com/Particular/NServiceBus.Persistence.Sql/pull/1254 ### Additional Context _No response_

ramonsmits · November 2, 2023, 9:47am

We released NServiceBus.Persistence.Sql 7.0.3 which contains a patch to address this problem: