MSSQL Query Plan Cache

[simonfox]

Describe the suggested improvement

I opened Particular/NServiceBus.SqlServer#1708 which has been resolved via Particular/NServiceBus.SqlServer#1728

The same issue exists in this repository for Outbox and Saga operations using the MSSQL dialect.

The path through SqlDialect.AddJsonParameter for MsSqlServer dialect never sets the size of the parameter meaning numerous plans get cached for varying lengths of saga data or outbox operations values.

Additional Context

No response

[simonfox]

Metadata column on sagas is also nvarchar(max) and probably requires a size on the parameter.

[abparticular]

Thanks for bringing this to our attention. This suggestion has been added to our backlog for future potential platform enhancements.

[mikeminutillo]

@simonfox there was a bug with the original fix, which caused variable length data to not be properly replaced on updates. To counter this we modified the code to only set an explicit length on read operations. This should be happening for Outbox and Saga operations.

It is happening in these two places:

• https://github.com/Particular/NServiceBus.Persistence.Sql/blob/c2d93aef1d22a424401f9eac1dc82b9e36747a10/src/SqlPersistence/CommandWrapper.cs?plain=1#L74C22-L74C22
• https://github.com/Particular/NServiceBus.Persistence.Sql/blob/c2d93aef1d22a424401f9eac1dc82b9e36747a10/src/SqlPersistence/CommandWrapper.cs?plain=1#L67C28-L67C28

It happens after the call to AddJsonParameter().

Which operations are you seeing this for?

[simonfox]

@mikeminutillo I don't think that fix solves this particular issue? Data and Operations columns in sagas and outbox respectively have type nvarchar(max). The values that can be inserted are of variable length. Sql parameters for these columns should always use length -1 (i.e. nvarchar(max)). The length of the actual value that is assigned doesn't matter and in fact is what causes the problem because the variation in values means that you will get multiple plans generated for all the different parameter sizes.

here is an example from our plan cache for Outbox inserts:

The values the Operations parameter is taking has numerous lengths, and a lot of the time can be under the threshold for creating an nvarchar(max) parameter of 4000. This causes the plan cache to be polluted with different plans for all the varying parameter lengths i.e. for this particular Outbox insert operation there should really only need to be a single plan

(@MessageId nvarchar(36), @Operations nvarchar(max), @PersistenceVersion nvarchar(7))
insert into [some].OutboxTable(MessageId, Operations, PersistenceVersion)
values  (@MessageId, @Operations, @PersistenceVersion)

[mikeminutillo]

@simonfox thanks for the clarification. We'll keep it on the backlog and comment here when we start work on it.