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