Speed up EF Core after idle by configuring SQL connection pool

by

Summary

After an ASP.NET Core application sits idle, EF Core queries against SQL Server become dramatically slower. The culprit is almost always connection pooling combined with SQL Server’s connection idle timeout and login timer delays. The keep-alive hosted service workaround works, but it masks a deeper connection configuration problem that should be solved at the connection string or pool settings level.

Root Cause

  • SQL Server 2017 (and later) tracks inactive connections in the pool. When a connection has been idle beyond loginTimeout or the pool’s idle timeout, SQL Server can reset the connection’s session state (temp tables, plan cache, etc.).
  • EF Core’s DbContext relies on DbConnection pooling. When a pooled connection is reused after sitting idle, the underlying TDS handshake can degrade.
  • The connection string likely lacks explicit pool settings like Max Pool Size, Min Pool Size, and Connection Lifetime.
  • Login acknowledgement timeouts on the SQL Server side can cause each reconnection to add 1–5 seconds of latency before the query even starts.
  • SQL Server may discard execution plans for the connection when it has been idle, causing recompilation on the first query after idle.

Why This Happens in Real Systems

  • Serverless or containerized environments spin down idle processes, so the first request after a cold period always hits a fresh pool.
  • PaaS platforms like Azure App Service or AWS Elastic Beanstalk recycle app pools after inactivity, triggering the same pattern.
  • Connection pooling is designed for throughput, not for indefinite idle hold. The pool will eventually let connections expire.
  • SQL Server login security checking (Active Directory authentication, Always On listener routing) adds latency on reconnection.

Real-World Impact

  • First user request after idle takes 5–30 seconds instead of the normal 50–200ms.
  • Entire site appears down during the cold window.
  • Support tickets spike because monitoring shows normal response times but users report timeouts.
  • Database CPU spikes from plan recompilation and login overhead cascade into other tenants.

Example or Code

// Connection string that fixes the idle cold-start issue
// Add these settings to your appsettings.json connection string
builder.Services.AddDbContext(options =>
    options.UseSqlServer(
        "Server=myserver;Database=mydb;Integrated Security=true;" +
        "Min Pool Size=5;" +
        "Max Pool Size=100;" +
        "Connection Lifetime=300;" +
        "Pooling=true;" +
        "MultipleActiveResultSets=true;" +
        "Command Timeout=30",
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 3,
                maxRetryDelay: TimeSpan.FromSeconds(5),
                errorNumbersToAdd: null);
        }));
// HostedService Keep-Alive (acceptable as a safety net, not as the primary fix)
public class ConnectionKeepAliveService : BackgroundService
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger _logger;

    public ConnectionKeepAliveService(
        ApplicationDbContext context,
        ILogger logger)
    {
        _context = context;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                await _context.Database.ExecuteSqlRawAsync("SELECT 1");
                _logger.LogDebug("Keep-alive ping successful");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Keep-alive ping failed");
            }
            await Task.Delay(TimeSpan.FromSeconds(30), stoppingToken);
        }
    }
}
// Register the keep-alive service
builder.Services.AddHostedService();

How Senior Engineers Fix It

  • Set Min Pool Size to keep a minimum number of warm connections available at all times. This is the single most effective change.
  • Set Connection Lifetime to a value lower than SQL Server’s idle timeout so connections rotate before they go stale.
  • Pin the SQL Server client version to match the server version to avoid TDS protocol mismatches.
  • Use EnableRetryOnFailure on the DbContext options so transient reconnection issues auto-recover.
  • Verify SQL Server remote login timeout and login timeout are set appropriately on the server side.
  • Add Application Name to the connection string for easier correlation in SQL Server logs.
  • Keep the hosted service as a last-resort safety net, not the primary solution. Use it only when you cannot control the connection string (e.g., third-party SaaS platform).
  • Monitor connection pool metrics via SELECT * FROM sys.dm_exec_connections and Application Insights to confirm the fix.

Why Juniors Miss It

  • They focus on query-level optimizations (split queries, compiled queries) instead of connection lifecycle issues.
  • They assume connection pooling is magic and never question what happens when connections sit idle.
  • They copy connection strings from tutorials that omit pool settings entirely.
  • They blame EF Core or the ORM when the real issue is network and SQL Server handshake latency.
  • They implement the keep-alive hosted service and stop investigating, treating a workaround as a fix.

Leave a Comment