title: Async DB calls to SQL Server from .NET Core intermittently slow
body: <p>I am noticing intermittently slow DB calls from .NET Core when I use async code. I am using .NET Core 3.1 and I can reproduce the problem with different versions of SQL Server. I can reproduce the problem using System.Data.SqlClient and Microsoft.Data.SqlClient. Here is a typical function I use to access the database:</p>
  
  <pre><code>internal static async Task AddHeartbeatAsync(string connectionString, DataAccess.Objects.Heartbeat heartbeat)
          {
              string sql = @"INSERT INTO
                              [dbo].[Heartbeats]
                              ([ExecutingCommandId]
                            ,[StandardOutput]
                             ,[StandardError]
                             ,[InsertedDate])
                          OUTPUT
                              inserted.[HeartbeatId]
                          VALUES
                              (@executingCommandId
                              ,@standardOutput
                              ,@standardError
                              ,@insertedDate);";
  
              using (SqlConnection conn = new SqlConnection(connectionString))
              using (SqlCommand cmd = new SqlCommand(sql, conn))
              {
                  cmd.Parameters.AddWithValue("executingCommandId", heartbeat.ExecutingCommandId);
                  cmd.Parameters.AddWithValue("standardOutput", heartbeat.StandardOutput ?? String.Empty);
                  cmd.Parameters.AddWithValue("standardError", heartbeat.StandardError ?? String.Empty);
                  cmd.Parameters.AddWithValue("insertedDate", heartbeat.InsertedDate);
  
                  if (conn.State == System.Data.ConnectionState.Closed)
                      await conn.OpenAsync();
  
                  heartbeat.HeartbeatId = (long)(await cmd.ExecuteScalarAsync());
              }
          }
  </code></pre>
  
  <p>I have seen similar functions take up to 10 seconds on a table that has a few rows. If I switch to sync code, I cannot reproduce the problem. I would like to continue to use async code all the way down to the database but need help to figure out how to fix this issue.</p>
  
  <p>UPDATE: Database calls are originated from both BackgroundServices and Controllers. Both are async methods called by the ASP.NET Core Framework.</p>
  
  <p>I have 5 instances of the same background service created like this:</p>
  
  <pre><code>for (int i = 0; i &lt; workerCount; i++)
  {
      services.AddSingleton&lt;IHostedService, WorkerBackgroundService&gt;();
  }
  </code></pre>
  
  <p>The background service code looks like this:</p>
  
  <pre><code>protected override async Task ExecuteAsync(CancellationToken stoppingToken)
  {
      while (!stoppingToken.IsCancellationRequested)
      {
          try
          {
              if (!(await CommandRules.RunNextQueuedCommandAsync(...)))
              {
                  //No jobs in the queue - snooze
                  try
                  {
                      await Task.Delay(_configSettings.CheckQueueFrequencyInSeconds * 1000, stoppingToken);
                  }
                  catch { }
              }
          }
          catch (Exception e)
          {
              //logging
          }
      }
  }
  </code></pre>
  
  <p>The controller code looks like this:</p>
  
  <pre><code>[Route("GetAll")]
  [HttpGet]
  public async Task&lt;IActionResult&gt; GetAllAsync()
  {
      try
      {
          foreach (DataAccess.Objects.SomeObject da in await _someRepository.GetAsync())
          {
              //Convert to Data transfer objects
          }
  
          return Ok(results);
  
      }
      catch (Exception e)
      {
          _nlogger.Error(e);
          throw;
      }
  }
  </code></pre>