ASP.NET Core Web API（EF Core）Azure SQL执行超时错误排查求助

问题背景 #

• 项目：ASP.NET Core Web API，使用EF Core处理数据库操作
• 数据库：Azure托管的SQL Server，采用SQL Server认证访问

触发场景与错误信息 #

调用接口GET:api/GetCommonPageUser时，偶尔出现超时错误：

Execution Timeout Expired.The timeout period elapsed prior to completion of the operation or the server is not responding.

对应的堆栈跟踪：

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()     
at System.Threading.Tasks.Task.<>c.<.cctor>b__281_0(Object obj)     
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)  --- End of stack trace from previous location ---     
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)     
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)  --- End of stack trace from previous location ---     
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)     at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)     at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)     
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)     
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()     
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)     
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken) 

at Project.Repository.Repositories.AccountRepository.GetAccountUserDetailsById(Int32 user_id, Int32 account_id, String identity_id) in D:\a\1\s\Project.Repository\Repositories\AccountRepository.cs:line 2123     
at Project.Api.Controllers.AccountController.GetAccountUserDetailsById(Int32 user_id, Int32 account_id) in D:\a\1\s\Project.Api\Controllers\AccountController.cs:line 685     

at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)     
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

问题定位 #

超时问题出现在GetAccountUserDetailsById方法，该方法操作包含2,509,978行数据的CommonPageUser表，对应的Linq查询如下：

await context.CommonPageUsers
             .Where(x => x.email == accountUserDetails.email 
                         && x.account_id == account_id 
                         && x.is_user_created && !x.is_deleted)
             .OrderByDescending(o => o.last_updated_datetime_local)
             .Select(s => new 
                          { 
                              s.move_in_date, s.move_out_date, 
                              s.lease_start_date, s.lease_end_date 
                          })
             .AsNoTracking().FirstOrDefaultAsync();

额外现象 #

在SQL Server Management Studio中执行以下查询：

SELECT * 
FROM CommonPageUsers 
WHERE email = 'john.doe@xyz.com' AND account_id = 381;

• 第一次执行耗时约3分钟
• 后续执行耗时几乎为0

疑问 #

• 是否是数据量过大导致的超时？
• 接口有时正常有时超时，问题出在后端代码还是SQL Server连接过多？

排查方向与解决方案 #

1. 优先修复索引问题（核心原因） #

从SSMS执行现象来看，第一次慢、后续快是典型的缓存命中差异，说明查询缺少合适的索引，第一次执行需要全表扫描，后续结果被SQL缓存。

• 为CommonPageUser表创建覆盖复合索引，包含过滤条件、排序字段和返回字段，避免回表或全表扫描：

  CREATE NONCLUSTERED INDEX IX_CommonPageUser_Email_AccountId 
  ON CommonPageUser (email, account_id, is_user_created, is_deleted, last_updated_datetime_local DESC)
  INCLUDE (move_in_date, move_out_date, lease_start_date, lease_end_date);
  

2. 验证EF Core生成的SQL #

使用EF Core日志或SQL Server Profiler查看Linq生成的实际SQL，确认：

• 是否存在参数类型不匹配（比如email是NVARCHAR但传入VARCHAR）导致隐式转换，无法使用索引
• 是否生成了不必要的查询逻辑（比如多余的排序或过滤）

3. 临时调整超时配置（验证用） #

如果索引优化前需要临时验证，可调整查询超时时间，但这不能替代索引优化：

await context.CommonPageUsers
             .Where(...)
             .OrderByDescending(...)
             .Select(...)
             .AsNoTracking()
             .FirstOrDefaultAsync(cancellationToken: new CancellationTokenSource(TimeSpan.FromMinutes(2)).Token);

4. 排查连接池与资源情况 #

• 登录Azure Portal查看SQL Server实例的CPU、内存、IO使用率，确认超时发生时是否资源耗尽
• 检查ASP.NET Core连接池配置（appsettings.json中ConnectionStrings的Max Pool Size参数，默认100），确保无连接泄漏（依赖注入的DbContext默认Scoped生命周期，会自动释放）

5. 数据量优化 #

如果is_user_created和!x.is_deleted是固定过滤条件：

• 按account_id或last_updated_datetime_local创建分区表，缩小扫描范围
• 归档历史数据，将不活跃用户迁移到归档表，减少主表数据量

结论 #

目前来看，缺少合适的覆盖索引是导致超时的主要原因，数据量过大是诱因但不是根本问题，连接过多的可能性较低（除非连接池配置不合理或有泄漏）。优先创建覆盖索引，再验证查询性能。

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