SQL Server存储过程调用时TDS协议响应溢出问题排查

我正在开发一款实现工业控制器与Microsoft SQL Server数据库通信的应用，数据库部署在测试机上，已配置好登录信息。现有一个存储过程负责接收PLC发送的特定格式字符串（例如(1,2302,2403,0);(2,2303,2403,1)），将其解析后插入目标表。

当前测试固定字符串时功能正常，但发送数据行数增加后，数据库的TDS响应呈指数增长，导致控制器端缓冲区溢出。捕获的响应中包含大量DBCC CHECKIDENT的执行输出（如"Checking identity information..."），而我们只需要存储过程末尾返回的执行成功标识数值。

问题根源在于存储过程通过游标循环处理每条数据，每次循环都会执行DBCC CHECKIDENT(ResultTable, RESEED, 0)重置标识列，这部分输出是导致响应过大的核心原因。

快速修复：关闭DBCC CHECKIDENT的输出 #

DBCC命令默认会返回执行状态信息，要抑制这些输出，只需在DBCC CHECKIDENT语句后添加WITH NO_INFOMSGS选项，同时确保存储过程开头的SET NOCOUNT ON已启用（你的代码中已经包含此设置）。

修改后的DBCC语句：

DBCC CHECKIDENT(ResultTable, RESEED, 0) WITH NO_INFOMSGS;

这个选项会直接关闭DBCC的信息输出，不再向TDS流中发送冗余的检查信息，能立即缓解响应过大的问题。

彻底优化：重构存储过程（消除游标与重复操作） #

当前游标循环+每次重置标识列的设计效率极低，且完全没必要。通过集合式操作替代游标，直接批量解析数据并插入，从根源上解决问题，同时大幅提升性能。

重构后的存储过程代码 #

ALTER PROCEDURE [dbo].[DeserializeIOLData] 
    @WorkOrder nvarchar(50), 
    @SerialData nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- 拆分外层分号分隔的记录，去除首尾括号并过滤空记录
        DECLARE @TempRecords TABLE (Record NVARCHAR(MAX));
        INSERT INTO @TempRecords
        SELECT TRIM('()' FROM value) 
        FROM STRING_SPLIT(@SerialData, ';')
        WHERE TRIM('()' FROM value) <> '';

        -- 将每条记录拆分为字段，同时生成自增的IOLSerialID
        WITH SplitFields AS (
            SELECT 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS IOLSerialID,
                value AS FieldValue,
                ROW_NUMBER() OVER (PARTITION BY tr.Record ORDER BY (SELECT NULL)) AS FieldIndex
            FROM @TempRecords tr
            CROSS APPLY STRING_SPLIT(tr.Record, ',')
        )
        -- 行转列映射到目标表字段，批量插入
        INSERT INTO Tbl_IOL (
            WorkOrderID, 
            IOLSerialID, 
            Welder_ID, 
            WeldRecipe, 
            WeldResult, 
            IOL_StartDate, 
            IOL_WeldDate, 
            IOL_FinishDate, 
            IsComplete, 
            IOLStatus, 
            Reject_Code, 
            ChangedOn
        )
        SELECT
            @WorkOrder AS WorkOrderID,
            sf.IOLSerialID,
            MAX(CASE WHEN sf.FieldIndex = 1 THEN sf.FieldValue END) AS Welder_ID,
            CONVERT(INT, MAX(CASE WHEN sf.FieldIndex = 2 THEN sf.FieldValue END)) AS WeldRecipe,
            CONVERT(INT, MAX(CASE WHEN sf.FieldIndex = 3 THEN sf.FieldValue END)) AS WeldResult,
            CONVERT(DATETIME2(7), MAX(CASE WHEN sf.FieldIndex = 4 THEN sf.FieldValue END)) AS IOL_StartDate,
            CONVERT(DATETIME2(7), MAX(CASE WHEN sf.FieldIndex = 5 THEN sf.FieldValue END)) AS IOL_WeldDate,
            CONVERT(DATETIME2(7), MAX(CASE WHEN sf.FieldIndex = 6 THEN sf.FieldValue END)) AS IOL_FinishDate,
            CONVERT(INT, MAX(CASE WHEN sf.FieldIndex = 7 THEN sf.FieldValue END)) AS IsComplete,
            CONVERT(INT, MAX(CASE WHEN sf.FieldIndex = 8 THEN sf.FieldValue END)) AS IOLStatus,
            CONVERT(INT, MAX(CASE WHEN sf.FieldIndex = 9 THEN sf.FieldValue END)) AS Reject_Code,
            GETDATE() AS ChangedOn
        FROM SplitFields sf
        GROUP BY sf.IOLSerialID;

        -- 返回成功标识
        SELECT 1 AS Result;
    END TRY
    BEGIN CATCH
        -- 可选：返回错误标识，或添加异常日志逻辑
        SELECT 0 AS Result;
        -- THROW; -- 如需抛出异常可启用此语句
    END CATCH
END

重构优势 #

• 完全消除游标和循环操作，使用集合式处理，性能提升显著
• 不再依赖ResultTable和重复的DBCC CHECKIDENT操作，彻底解决TDS响应过大问题
• 批量插入减少数据库IO开销，更适配工业场景的大数据量传输需求
• 代码结构简洁，维护成本更低

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