应用中简单INSERT语句超时，SSMS中执行却快速完成

基础信息 #

应用中有一条INSERT...SELECT语句，通过Profiler捕获的语句如下：

insert into ford.tblFordCompoundFlowVehicle
  (FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, Status2ToSend, FordFlowTriggerID, SendTriggerSatisfied, DateSend, FordCompoundFlowDefinitionID)
select fcfd.FordCompoundFlowID, 
       9711, 
       fcfdd.SortOrder, 
       fcfdd.Status1ToSend, 
       fcfdd.Status2ToSend, 
       fcfdd.FordFlowTriggerID, 
       0, 
       null,
       2
from   ford.tblFordCompoundFlowDefinitionDetail fcfdd
  inner join ford.tblFordCompoundFlowDefinition fcfd on fcfdd.FordCompoundFlowDefinitionID = fcfd.FordCompoundFlowDefinitionID 
where  fcfdd.FordCompoundFlowDefinitionID = 2
order by fcfdd.SortOrder

最初使用Dapper字符串拼接方式执行（认为int参数无SQL注入风险），代码如下：

string sql = 
    $"""
     insert into ford.tblFordCompoundFlowVehicle
       (FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, Status2ToSend, FordFlowTriggerID, SendTriggerSatisfied, DateSend, FordCompoundFlowDefinitionID)
     select fcfd.FordCompoundFlowID, 
            {compoundVehicleID}, 
            fcfdd.SortOrder, 
            fcfdd.Status1ToSend, 
            fcfdd.Status2ToSend, 
            fcfdd.FordFlowTriggerID, 
            0, 
            null,
            {fordCompoundFlowDefinitionID}
     from   ford.tblFordCompoundFlowDefinitionDetail fcfdd
       inner join ford.tblFordCompoundFlowDefinition fcfd on fcfdd.FordCompoundFlowDefinitionID = fcfd.FordCompoundFlowDefinitionID 
     where  fcfdd.FordCompoundFlowDefinitionID = {fordCompoundFlowDefinitionID.Value}
     order by fcfdd.SortOrder 
     OPTION(RECOMPILE)
     """;

connection.Execute(sql, null, transaction);

核心问题 #

该语句在应用中执行时超时，但将Profiler捕获的语句在SSMS中执行速度极快。已尝试添加OPTION(RECOMPILE)，无改善。

已获取SSMS执行计划与应用中慢查询的执行计划，无法自行对比排查。

事务处理代码 #

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();

    using (SqlTransaction transaction =  connection.BeginTransaction())
    {
        try
        {
            fordCompoundFlowDefinitionID = CreateFordCompoundFlowVehicle(connection, transaction, compoundVehicleID);
            transaction.Commit();
        }
        catch (Exception ex)
        { 
            transaction.Rollback();
            throw;
        }
    }
}

private int? CreateFordCompoundFlowVehicle(SqlConnection connection, SqlTransaction transaction, int compoundVehicleID)
{
    int? result = null;

    // 检查该compoundVehicleID是否有导入数据，无则直接返回
    int? fordCompoundFlowDefinitionID = GetFordCompoundFlowDefinitionID(connection, transaction, compoundVehicleID);

    if (fordCompoundFlowDefinitionID.HasValue)
    {
        string sql = 
            $"""
             insert into ford.tblFordCompoundFlowVehicle
               (FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, Status2ToSend, FordFlowTriggerID, SendTriggerSatisfied, DateSend, FordCompoundFlowDefinitionID)
             select fcfd.FordCompoundFlowID, 
                    {compoundVehicleID}, 
                    fcfdd.SortOrder, 
                    fcfdd.Status1ToSend, 
                    fcfdd.Status2ToSend, 
                    fcfdd.FordFlowTriggerID, 
                    0, 
                    null,
                    {fordCompoundFlowDefinitionID}
             from   ford.tblFordCompoundFlowDefinitionDetail fcfdd
               inner join ford.tblFordCompoundFlowDefinition fcfd on fcfdd.FordCompoundFlowDefinitionID = fcfd.FordCompoundFlowDefinitionID 
             where  fcfdd.FordCompoundFlowDefinitionID = {fordCompoundFlowDefinitionID.Value}
             order by fcfdd.SortOrder 
             OPTION(RECOMPILE)
             """;

        // 此处执行超时
        connection.Execute(sql, null, transaction);

        result = fordCompoundFlowDefinitionID;
    }

    return result;
}

参数化改造后的尝试 #

改为参数化查询后，代码如下：

string sql = 
    """
     insert into ford.tblFordCompoundFlowVehicle
       (FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, Status2ToSend, FordFlowTriggerID, SendTriggerSatisfied, DateSend, FordCompoundFlowDefinitionID)
     select fcfd.FordCompoundFlowID, 
            @CompoundVehicleID, 
            fcfdd.SortOrder, 
            fcfdd.Status1ToSend, 
            fcfdd.Status2ToSend, 
            fcfdd.FordFlowTriggerID, 
            0, 
            null,
            @FordCompoundFlowDefinitionID
     from   ford.tblFordCompoundFlowDefinitionDetail fcfdd
       inner join ford.tblFordCompoundFlowDefinition fcfd on fcfdd.FordCompoundFlowDefinitionID = fcfd.FordCompoundFlowDefinitionID 
     where  fcfdd.FordCompoundFlowDefinitionID = @FordCompoundFlowDefinitionID
     order by fcfdd.SortOrder 
     OPTION(RECOMPILE)
     """;

var parameters = new {CompoundVehicleID = compoundVehicleID, FordCompoundFlowDefinitionID = fordCompoundFlowDefinitionID };

connection.Execute(sql, parameters, transaction);

Profiler捕获的参数化语句：

exec sp_executesql N'insert into ford.tblFordCompoundFlowVehicle
  (FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, Status2ToSend, FordFlowTriggerID, SendTriggerSatisfied, DateSend, FordCompoundFlowDefinitionID)
select fcfd.FordCompoundFlowID, 
       @CompoundVehicleID, 
       fcfdd.SortOrder, 
       fcfdd.Status1ToSend, 
       fcfdd.Status2ToSend, 
       fcfdd.FordFlowTriggerID, 
       0, 
       null,
       @FordCompoundFlowDefinitionID
from   ford.tblFordCompoundFlowDefinitionDetail fcfdd
  inner join ford.tblFordCompoundFlowDefinition fcfd on fcfdd.FordCompoundFlowDefinitionID = fcfd.FordCompoundFlowDefinitionID 
where  fcfdd.FordCompoundFlowDefinitionID = @FordCompoundFlowDefinitionID
order by fcfdd.SortOrder 
OPTION(RECOMPILE)',N'@CompoundVehicleID int,@FordCompoundFlowDefinitionID int',@CompoundVehicleID=9711,@FordCompoundFlowDefinitionID=2
go

改造后仍然超时，错误信息：

System.Data.SqlClient.SqlException HResult=0x80131904
Message=Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Operation cancelled by user.
Source=Core .Net SqlClient Data Provider
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action2 paramReader) in /_/Dapper/SqlMapper.cs:line 2965 at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 656 at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable1 commandType) in /_/Dapper/SqlMapper.cs:line 527 at WebServiceMobile.Repositories.Compound.RepositoryFordFlow.CreateFordCompoundFlowVehicle(SqlConnection connection, SqlTransaction transaction, Int32 compoundVehicleID) in C:\Development\Git\WebServices\WebServiceMobile\Repositories\Compound\Ford\RepositoryFordFlow.cs:line 124 at WebServiceMobile.Repositories.Compound.RepositoryFordFlow.HandleIncomingVehicle(Int32 compoundVehicleID, Nullable1 dateInActual) in C:\Development\Git\WebServices\WebServiceMobile\Repositories\Compound\Ford\RepositoryFordFlow.cs:line 29

Inner Exception 1: Win32Exception: The wait operation timed out.

尝试移除事务执行，仍然超时：

System.Data.SqlClient.SqlException: 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Operation cancelled by user.'

阻塞排查结果 #

执行sp_who2发现查询处于SUSPENDED状态，被SPID 77阻塞，结果如下：

执行计划差异说明 #

• SSMS执行计划：采用高效的索引查找，快速关联tblFordCompoundFlowDefinitionDetail和tblFordCompoundFlowDefinition，返回少量数据后插入目标表，整体执行成本极低。
• 应用中慢查询执行计划：出现不必要的表扫描或嵌套循环，执行路径与SSMS版本差异明显，即使添加OPTION(RECOMPILE)也未生成最优计划。

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