NServiceBus inefficient SQL with persistence

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

Hi Brian!

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

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

Cheers,

Andreas

1 Like

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

Brian