存储过程调用27次后性能骤降问题排查及优化咨询

• 执行计划缓存异常：SQL Server 默认对用户自定义表类型参数的基数估算为1，前27次调用生成的执行计划适配小数据量场景，运行正常。当累计调用达到阈值触发执行计划自动重编译时，统计信息偏差导致优化器生成了错误的执行计划（比如选错关联顺序、走全表扫描），直接导致耗时飙升。重启后执行计划缓存清空，重新生成的小数据量适配计划会让性能暂时恢复，完全符合你观测到的现象。
• 存储过程实现存在多处性能隐患：
  1. 循环取值逻辑错误：循环内读取表参数时未加WHERE条件匹配当前行，每次循环都会读取表参数的最后一行重复执行，相当于做了大量无效重复写入，数据量稍大就会导致耗时指数级上涨。
  2. 循环内频繁操作临时表：每次循环都执行DROP TABLE IF EXISTS + 建临时表，会频繁申请/释放系统表锁，压测下锁等待累积就是性能跳变的直接触发点。
  3. 非关系型设计开销：将子ID存储为逗号分隔字符串再拆分的实现，每次循环都产生额外的字符串处理开销，累积后会放大性能问题。

紧急规避方案（改动量极小，可快速上线缓解问题） #

• 修复循环取值逻辑：给自定义表类型PenguineHouseRoleMemberUpdateType新增自增序号列SeqId，循环内读取时加WHERE SeqId = @CounterId过滤，避免重复执行同一行数据。
• 将临时表创建移到循环外部，循环内仅执行TRUNCATE TABLE #TempSubRoleUpdateTable清空数据，避免反复创建删除表的元数据开销。
• 给PenguinHouseRole表的PenguinHouseId字段加普通索引，优化开头DELETE语句的关联查询性能，避免全表扫描。
• 存储过程开头添加OPTION (OPTIMIZE FOR (@PenguineHouseRoleMemberUpdate UNKNOWN))提示，修正表参数的基数估算偏差，避免执行计划重编译后性能退化。

根治优化方案（完全移除循环，性能稳定无跳变） #

你提到的自增ID无法提前获取的问题可以通过SQL Server的OUTPUT子句完美解决，完全不需要循环：

1. 批量插入所有父表记录，用OUTPUT子句将生成的自增ID和对应的PenguinHouseId、RoleType映射关系写入临时表，拿到父记录自增ID和业务字段的对应关系。
2. 提前将逗号分隔的SubRoleIds拆分为多行（或直接调整表参数结构，去掉逗号分隔字段，改成父子结构的表参数传入），关联上面的映射表即可一次性批量插入所有子表记录。

优化后的参考代码：

CREATE OR ALTER PROCEDURE dbo.sp_DeleteThenCreateRoleAndMembers
    @PenguineHouseRoleMemberUpdate PenguineHouseRoleMemberUpdateType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 先删除原有数据
    DECLARE @TargetHouseId INT = (SELECT TOP 1 PenguinHouseId FROM @PenguineHouseRoleMemberUpdate);
    DELETE phrm 
    FROM PenguinHouseRoleMember phrm 
    INNER JOIN PenguinHouseRole ph on phrm.PenguinHouseRoleId = ph.Id 
    WHERE ph.PenguinHouseId = @TargetHouseId;

    -- 临时表存储父表自增ID映射关系
    CREATE TABLE #RoleIdMap (
        PenguinHouseId INT,
        RoleType INT,
        RoleId INT
    );

    -- 批量插入父记录，同时输出生成的自增ID
    INSERT INTO PenguinHouseRole (RoleType, PenguinHouseId, CreatedAt, CreatedBy, UpdatedBy, UpdatedAt)
    OUTPUT inserted.PenguinHouseId, inserted.RoleType, inserted.Id INTO #RoleIdMap
    SELECT DISTINCT RoleType, PenguinHouseId, GETDATE(), 'system', 'system', GETDATE()
    FROM @PenguineHouseRoleMemberUpdate;

    -- 批量插入子记录
    INSERT INTO dbo.PenguinHouseRoleMember
    (
        workflowinstanceroleid, subroleid, AllTasksCompleted, Approved,
        SuggestedBySystem, SelectedByCurator, CreatedAt, CreatedBy, UpdatedBy, UpdatedAt
    )
    SELECT
        m.RoleId,
        CONVERT(INT, s.value),
        0, 0, 1, 0,
        GETDATE(), 'system', 'system', GETDATE()
    FROM @PenguineHouseRoleMemberUpdate u
    INNER JOIN #RoleIdMap m ON u.PenguinHouseId = m.PenguinHouseId AND u.RoleType = m.RoleType
    CROSS APPLY string_split(u.SubRoleIds, ',') s
    WHERE LEN(LTRIM(RTRIM(u.SubRoleIds))) > 0;
END

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