Entity Framework Core批量更新致SQL执行计划缓存膨胀问题问询

我开发了一个监听服务总线事件的应用，基于收到的事件，使用Entity Framework Core批量更新数据库记录。应用会先批量接收消息，再查询数据库并更新实体属性，示例代码如下：

var dbResults = await _context.SampleDbSet
    .Where(s => s.Active)
    .ToListAsync();

var dbResultsLookup = dbResults
    .ToDictionary(
        entity => $"{entity.Id}_{entity.Name}",
        v => v
    );

foreach (var message in incomingMessages)
{
    if (dbResultsLookup.TryGetValue($"{message.Id}_{message.Name}", out var dbEntity))
    {
        dbEntity.PropertyOne = message.PropertyOne;
        dbEntity.LastChangeTime = DateTimeOffset.Now;
    }
}

await _context.SaveChangesAsync(cancel);

当前遇到的SQL执行计划缓存问题 #

1. 每次批量更新都会在dm_exec_cached_plans中生成唯一的预执行计划，原因是当PropertyOne未被修改时，EF Core不会将其纳入参数化更新，例如@p76 decimal(9,2)仅在PropertyOne更新时才会添加，生成的示例SQL如下：

(
    @p1 int,@p2 int,@p0 datetimeoffset(7),@p4 int,@p5 int,@p3 datetimeoffset(7),
    @p7 int,@p8 int,@p6 datetimeoffset(7),@p10 int,
    @p11 int,@p9 datetimeoffset(7),@p13 int,@p14 int,@p12 datetimeoffset(7),
    @p16 int,@p17 int,@p15 datetimeoffset(7),@p19 int,@p20 int,@p18 datetimeoffset(7),@p22 int,@p23 int,@p21 datetimeoffset(7),
    @p25 int,@p26 int,@p24 datetimeoffset(7),@p28 int,@p29 int,@p27 datetimeoffset(7),@p31 int,@p32 int,@p30 datetimeoffset(7),
    @p34 int,@p35 int,@p33 datetimeoffset(7),@p37 int,@p38 int,@p36 datetimeoffset(7),@p40 int,@p41 int,@p39 datetimeoffset(7),
    @p43 int,@p44 int,@p42 datetimeoffset(7),@p46 int,@p47 int,@p45 datetimeoffset(7),

@p77 int,@p78 int,@p75 datetimeoffset(7),@p76 decimal(9,2)

)SET NOCOUNT ON;

UPDATE [Sample_Table] SET [LastChangeTime] = @p0  WHERE [Id] = @p1 AND [Name] = @p2;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p3  WHERE [Id] = @p4 AND [Name] = @p5;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p6  WHERE [Id] = @p7 AND [Name] = @p8;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p9  WHERE [Id] = @p10 AND [Name] = @p11;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p12  WHERE [Id] = @p13 AND [Name] = @p14;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p15  WHERE [Id] = @p16 AND [Name] = @p17;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p18  WHERE [Id] = @p19 AND [Name] = @p20;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p21  WHERE [Id] = @p22 AND [Name] = @p23;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p24  WHERE [Id] = @p25 AND [Name] = @p26;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p27  WHERE [Id] = @p28 AND [Name] = @p29;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p30  WHERE [Id] = @p31 AND [Name] = @p32;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p33  WHERE [Id] = @p34 AND [Name] = @p35;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p36  WHERE [Id] = @p37 AND [Name] = @p38;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p39  WHERE [Id] = @p40 AND [Name] = @p41;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p42  WHERE [Id] = @p43 AND [Name] = @p44;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p45  WHERE [Id] = @p46 AND [Name] = @p47;
SELECT @@ROWCOUNT;
UPDATE [Sample_Table] SET [LastChangeTime] = @p75, [PropertyOne] = @p76  WHERE [Id] = @p77 AND [Name] = @p78;
SELECT @@ROWCOUNT;

2. 每次批量执行都会生成唯一参数化查询，导致SQL服务器执行计划缓存膨胀，进而清理其他执行计划以腾出空间。

咨询问题 #

目前暂未考虑存储过程+TVP的方案，想咨询无需使用存储过程的解决办法：

1. 能否让EF Core即使PropertyOne未更新，仍将其纳入参数化查询？
2. 能否阻止这类更新计划存入SQL查询缓存？可通过EF Core库或SQL数据库本身实现吗？

问题1：强制EF Core将PropertyOne纳入参数化查询 #

可以手动标记属性为已修改，让EF Core无论属性值是否变化，都将其包含在更新语句中，确保每次生成的SQL结构一致：

代码修改示例 #

foreach (var message in incomingMessages)
{
    if (dbResultsLookup.TryGetValue($"{message.Id}_{message.Name}", out var dbEntity))
    {
        dbEntity.PropertyOne = message.PropertyOne;
        dbEntity.LastChangeTime = DateTimeOffset.Now;
        // 手动标记PropertyOne为已修改，强制EF Core将其加入更新语句
        _context.Entry(dbEntity).Property(e => e.PropertyOne).IsModified = true;
    }
}

这样所有更新语句都会包含PropertyOne和LastChangeTime的参数，SQL Server就能复用执行计划，避免缓存膨胀。

问题2：阻止更新计划存入SQL查询缓存 #

有两种精准实现方式：

方式1：通过EF Core拦截器添加查询提示 #

自定义拦截器，针对批量更新的SQL注入OPTION (RECOMPILE)提示，告诉SQL Server不要缓存该执行计划：

// 自定义拦截器
public class NoCacheUpdateInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // 识别目标更新语句并添加RECOMPILE提示
        if (command.CommandText.Contains("UPDATE [Sample_Table]"))
        {
            command.CommandText += " OPTION (RECOMPILE)";
        }
        return result;
    }
}

// 在DbContext配置中注册拦截器
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new NoCacheUpdateInterceptor());
}

方式2：通过SQL Server会话级配置 #

在执行更新前，临时设置会话参数SET ARITHABORT ON; SET OPTION (RECOMPILE)，但这种方式需要在每次执行更新前手动执行，不如拦截器灵活。不推荐使用数据库级别的PARAMETERIZATION FORCED，会影响全局查询的执行计划复用。

内容的提问来源于stack exchange，提问作者Henri