SQL带参数SELECT直接执行正常 同逻辑存储过程超时排查

直接在SQL环境中执行带参数的SELECT查询可快速返回结果，将完全相同的查询逻辑封装为存储过程、传入相同参数执行时耗时极长；从C#应用端调用该存储过程时触发超时错误，错误信息：
The time allotted for execution has expired. The allotted time elapsed before the operation completed or the server did not respond.

可正常快速执行的SQL脚本 #

declare @nvGuide varchar(50)='6ABDA937-46B2-4932-AA76-27E6417EE3A7',@iSchoolId int =182,@iYearId int =5783
    select iQuantity as iBooks, t1.iStudentUserId as iStudentId, tClass.nvValue as nvClassName , cast( iClassNumber as nvarchar(10) ) as nvClassNumber, 
    TUser.nvFirstName , TUser.nvLastName , 1 as iGuideStatusId,case when isnull (t2.iStudentUserId ,0) = 0 then 0 else 1 end 
    as bNotReturn,
    isnull(TUser.nvMobile, TUser.nvPhone) as nvPhone, 
    t1.sPaymentReceivedWithReturn
    from (select TStudent.iStudentUserId, SUM(iQuantity) as iQuantity, SUM(nPaymentReceivedWithReturn) as sPaymentReceivedWithReturn
          from TStudentOrderItem
          inner join TStudentOrder on TStudentOrder.iStudentOrderId = TStudentOrderItem.iStudentOrderId
          inner join TStudent on TStudent.iStudentUserId = TStudentOrder.iStudentUserId
          inner join TSchoolBookList on TSchoolBookList.iSchoolBookId = TStudentOrderItem.iSchoolBookId
          inner join TBook on TBook.iBookId = TSchoolBookList.iBookId
          where TStudent.iSchoolId = @iSchoolId 

            and TStudentOrderItem.iYearType=@iYearId-1
            and TStudent.iYearType=@iYEarId-1
            and TStudentOrderItem.iSysRowStatus = 1 
            and TStudentOrder.iSysRowStatus = 1 
            and iUsingType in (14, 15, 513) 
            and bReturn<>1
          group by TStudent.iStudentUserId
          having SUM(iQuantity) > 0
      ) t1
inner join TUser on TUser.iUserId = t1.iStudentUserId
inner join TStudent on TStudent.iStudentUserId = t1.iStudentUserId
inner join TSysTableRow tClass on tClass.iSysTableRowId = TStudent.iRisingToClassType and TStudent.iYearType=@iYEarId-1
left join( select distinct TStudent.iStudentUserId
           from  TStudentOrderItem
              inner join TStudentOrder on TStudentOrder.iStudentOrderId = TStudentOrderItem.iStudentOrderId 
              inner join TStudent on TStudent.iStudentUserId = TStudentOrder.iStudentUserId
             inner join TSchoolBookList on TSchoolBookList.iSchoolBookId = TStudentOrderItem.iSchoolBookId
             inner join TBook on TBook.iBookId = TSchoolBookList.iBookId
              where  (isnull(bReturn,0)<>1 and isnull(bIsReturnInProperUse,0)<>1 )--or  bIsReturnInProperUse is null
               and TStudent.iYearType=@iYEarId-1 
               and TStudentOrderItem.iYearType=@iYEarId-1
               AND TStudent.iSchoolId = @iSchoolId 
               and TStudentOrderItem.iSysRowStatus = 1 
              and TStudentOrder.iSysRowStatus = 1 
              and iUsingType in (14, 15, 513)
              and TStudentOrder.iYearType=@iYEarId-1
              and TStudent.iSysRowStatus=1
              
             )t2 on t1.iStudentUserId=t2.iStudentUserId
    order by iRisingToClassType, iClassNumber,nvLastName, nvFirstName
    
    select  TStudent.iStudentUserId,dbo.TBookBase.nvBookName
          from TStudentOrderItem
          inner join TStudentOrder on TStudentOrder.iStudentOrderId = TStudentOrderItem.iStudentOrderId
          inner join TStudent on TStudent.iStudentUserId = TStudentOrder.iStudentUserId
          inner join TSchoolBookList on TSchoolBookList.iSchoolBookId = TStudentOrderItem.iSchoolBookId
          inner join TBook on TBook.iBookId = TSchoolBookList.iBookId
          inner join dbo.TBookBase on  dbo.TBookBase.iBookBaseId =TBook.iBookBaseId
          where TStudent.iSchoolId = @iSchoolId 
            and TStudentOrderItem.iYearType=@iYEarId-1
            and TStudent.iYearType=@iYEarId-1
            and TStudentOrderItem.iSysRowStatus = 1 
            and TStudentOrder.iSysRowStatus = 1 
            and iUsingType in (14, 15, 513) 
            and bReturn<>1
         order by  TStudent.iStudentUserId

执行超时的存储过程调用语句 #

exec [dbo].[StudentOrder_GetAllNotReturnBooks_SLCT] @nvGuide='6ABDA937-46B2-4932-AA76-27E6417EE3A7',@iSchoolId=182,@iYearI=5783

• 原因1：参数嗅探导致缓存了非最优执行计划
  单独执行Ad-hoc SQL时，查询优化器会根据当前传入的具体参数值生成匹配数据分布的执行计划；存储过程首次执行时会根据首次传入的参数生成执行计划并缓存，后续执行直接复用缓存计划，如果后续传入参数对应的数据分布和首次编译时差异极大，就会出现执行效率暴跌的情况。
  解决方案：

  1. 存储过程内部声明局部变量中转所有入参，查询逻辑统一使用局部变量，阻断优化器对入参的嗅探，例如在存储过程开头添加DECLARE @local_iSchoolId INT = @iSchoolId, @local_iYearId INT = @iYearId，后续所有查询条件替换为局部变量。
  2. 给查询语句添加OPTION(RECOMPILE)查询提示，或创建存储过程时添加WITH RECOMPILE选项，让存储过程每次执行都根据当前参数重新生成执行计划，适合执行频率不高的报表类查询。
  3. 手动清理该存储过程的缓存执行计划：通过系统视图sys.dm_exec_cached_plans、sys.dm_exec_sql_text定位该存储过程对应的缓存计划句柄，执行DBCC FREEPROCCACHE(对应计划句柄)清空旧计划，让存储过程用当前参数重新编译生成最优计划。

• 原因2：参数/变量拼写错误、类型不匹配导致逻辑异常或索引失效
  从提供的代码可发现两处明显拼写问题：一是SQL脚本中多处将@iYearId写为@iYEarId，二是存储过程调用语句中入参写为@iYearI（末尾为I而非d），如果存储过程定义的参数名和传入参数名不匹配，会导致参数取默认NULL值，触发全表扫描；此外如果参数类型和表字段类型不匹配，会发生隐式转换，导致索引无法命中。
  解决方案：逐行核对存储过程的入参定义、内部变量名拼写，确保调用时传入的参数名、类型和存储过程定义完全一致；检查所有查询条件两侧的数据类型，避免字段和比较值类型不匹配触发隐式转换。

• 原因3：会话SET选项不一致导致执行计划差异
  SQL Server的执行计划缓存和会话的SET选项（ANSI_NULLS、QUOTED_IDENTIFIER、ARITHABORT等）绑定，SSMS客户端默认的SET配置和C#应用连接池的默认配置存在差异，最常见的是SSMS默认开启ARITHABORT，而应用连接默认关闭该选项，会导致同参数下生成完全不同的执行计划，出现性能差异。
  解决方案：测试时在执行存储过程前先运行SET ARITHABORT ON，如果性能恢复即可确认是SET选项不匹配问题，调整存储过程创建时的SET选项，和高性能会话的配置保持一致即可。

• 原因4：执行权限不足导致优化器无法生成最优计划
  如果C#端连接数据库使用的账号、存储过程执行账号权限低于你在SSMS中使用的账号，低权限账号无法访问表的索引统计信息、元数据，会导致查询优化器估算行数错误，生成次优执行计划。
  解决方案：给存储过程的执行账号授予涉及查询表的必要读权限，或在存储过程定义中添加EXECUTE AS OWNER子句，用存储过程所有者的权限执行查询逻辑。

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