.NET Core中SqlClient调用存储过程部分参数超时，.NET Framework/SSMS正常

场景重现与代码 #

.NET Core执行存储过程的代码 #

using (DBContext context = new DBContext())
{ 
    using (var command = context.Database.GetDbConnection().CreateCommand()) 
    { 
        command.CommandText = "Sp_Name"; 
        command.CommandType = CommandType.StoredProcedure; 
        command.Parameters.Add(new SqlParameter("@input", SqlDbType.VarChar ,3) { Value = InputValue }); 
        command.Parameters.Add(new SqlParameter("@Return_Value", SqlDbType.VarChar, 3) { Value = string.Empty }); 
        context.Database.OpenConnection(); 
        var dataReader = command.ExecuteReader(); 
        if (dataReader.Read()) 
        { 
            var code = dataReader.GetString(dataReader.GetOrdinal("")); 
        }
    }
}

测试场景对比 #

正常执行场景（EF代码与SSMS均正常）

-- SSMS执行结果正常
exec Sp_Name @input = 'PDX', @Return_Value = '' 
-- Result (No Column Name) - '3I9'

超时场景（EF代码异常，但SSMS正常）

-- SSMS执行结果正常
exec Sp_Name @input = 'N01', @Return_Value = '' 
-- Result (No Column Name) - 'WE5'

异常信息 #

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 
---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 
at System.Data.SqlClient.SqlDataReader.get_MetaData() 
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) 
at System.Data.SqlClient.SqlCommand.ExecuteScalar() 
at Mednax.ReferringPhysician.Data.PdxService.getGPMSCode(String practiceCode) in C:\Work\GIT\ReferringPhysician2\Mednax.ReferringPhysician.WebAPI\Mednax.ReferringPhysician.Data\PdxService.cs:line 971 
ClientConnectionId:199f2b1a-cb1b-4752-8632-9f2c54bcefd8 
Error Number:-2,State:0,Class:11

堆栈跟踪 #

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 
at System.Data.SqlClient.SqlDataReader.get_MetaData() 
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) 
at System.Data.SqlClient.SqlCommand.ExecuteScalar()

存储过程代码 #

( @Input varchar(3), @Return_Value varchar(3) output ) 
AS 
SET NOCOUNT ON 
SET @Return_Value = NULL 
SELECT TOP 1 @Return_Value = pacl.P_Code 
FROM TABLEA pacl with (nolock) 
LEFT OUTER JOIN TABLEB rpp with (nolock) 
    ON rpp.Code = pacl.Code AND rpp.P_Code = @Input 
WHERE rpp.P_Code IS NULL 
ORDER BY pacl.P_Code 
IF @@Rowcount = 0 
    SET @Return_Value = '***' 
Select @Return_Value

异常TargetSite详情 #

问题分析与解决方案 #

这种“特定参数超时、其他环境正常”的情况，核心原因大概率是**参数嗅探（Parameter Sniffing）**加上.NET Core与SSMS/.NET Framework的SqlClient连接属性差异共同导致的，另外你的代码里还有一个容易被忽略的错误：输出参数的方向设置不对。

为什么会出现差异？ #

1. 连接属性差异：SSMS默认开启ARITHABORT ON，而.NET Core的SqlClient默认是ARITHABORT OFF。SQL Server会根据这个属性的不同生成不同的执行计划缓存，当某个参数对应的执行计划在ARITHABORT OFF的环境下效率极低时，就会触发超时。
2. 参数嗅探：你的存储过程查询的数据集分布可能不均匀（比如'N01'对应的匹配数据量极大），SQL Server缓存了适合'PDX'的执行计划，当用'N01'时，这个缓存计划完全不适用，导致查询变慢超时。
3. 输出参数错误：你把@Return_Value这个输出参数当成了输入参数来设置，没有指定Direction = ParameterDirection.Output，这可能导致SQL Server执行时出现资源异常，间接引发超时。

具体解决步骤 #

1. 先修正输出参数的代码错误

这是最基础的问题，必须先解决：

using (DBContext context = new DBContext())
{ 
    using (var command = context.Database.GetDbConnection().CreateCommand()) 
    { 
        command.CommandText = "Sp_Name"; 
        command.CommandType = CommandType.StoredProcedure; 
        // 输入参数
        command.Parameters.Add(new SqlParameter("@input", SqlDbType.VarChar ,3) { Value = InputValue }); 
        // 修正输出参数的方向
        var returnParam = new SqlParameter("@Return_Value", SqlDbType.VarChar, 3);
        returnParam.Direction = ParameterDirection.Output;
        command.Parameters.Add(returnParam);
        
        context.Database.OpenConnection(); 
        var dataReader = command.ExecuteReader(); 
        if (dataReader.Read()) 
        {
            // 结果集是无列名的，用索引0更可靠
            var code = dataReader.GetString(0); 
        }
        // 也可以直接获取输出参数的值
        var returnValue = returnParam.Value.ToString();
    }
}

2. 解决参数嗅探与连接属性问题

可以选择以下任意一种方法：

方法一：在.NET Core代码中设置和SSMS一致的连接属性

在执行存储过程前，先执行SET ARITHABORT ON：

using (DBContext context = new DBContext())
{ 
    using (var command = context.Database.GetDbConnection().CreateCommand()) 
    { 
        context.Database.OpenConnection();
        // 先设置ARITHABORT ON，和SSMS保持一致
        command.CommandText = "SET ARITHABORT ON;";
        command.ExecuteNonQuery();

        // 再执行存储过程
        command.CommandText = "Sp_Name"; 
        command.CommandType = CommandType.StoredProcedure; 
        // ... 其他参数设置和上面修正后的代码一致
    }
}

方法二：修改存储过程，强制重新编译

让SQL Server每次执行都生成适合当前参数的执行计划：

( @Input varchar(3), @Return_Value varchar(3) output ) 
AS 
SET NOCOUNT ON 
SET @Return_Value = NULL 
SELECT TOP 1 @Return_Value = pacl.P_Code 
FROM TABLEA pacl with (nolock) 
LEFT OUTER JOIN TABLEB rpp with (nolock) 
    ON rpp.Code = pacl.Code AND rpp.P_Code = @Input 
WHERE rpp.P_Code IS NULL 
ORDER BY pacl.P_Code 
IF @@Rowcount = 0 
    SET @Return_Value = '***' 
Select @Return_Value
OPTION (RECOMPILE); -- 添加这一行，强制每次重新编译执行计划

方法三：用局部变量缓解参数嗅探

把输入参数赋值给局部变量，让SQL Server无法嗅探到原始参数，从而生成更通用的执行计划：

( @Input varchar(3), @Return_Value varchar(3) output ) 
AS 
SET NOCOUNT ON 
-- 定义局部变量接收输入参数
DECLARE @LocalInput varchar(3) = @Input;
SET @Return_Value = NULL 
SELECT TOP 1 @Return_Value = pacl.P_Code 
FROM TABLEA pacl with (nolock) 
LEFT OUTER JOIN TABLEB rpp with (nolock) 
    ON rpp.Code = pacl.Code AND rpp.P_Code = @LocalInput -- 使用局部变量
WHERE rpp.P_Code IS NULL 
ORDER BY pacl.P_Code 
IF @@Rowcount = 0 
    SET @Return_Value = '***' 
Select @Return_Value

验证方法 #

你可以先在SSMS里模拟.NET Core的环境，执行SET ARITHABORT OFF;后再运行超时参数的存储过程，如果此时也出现超时，就完全确认是连接属性+参数嗅探的问题了。

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