title: Azure Web Api - Waiting Sql Connection every 4 minutes and 30 minutes
body: <p>Within a request on an ApiController, I'm tracking the duration of awaiting the Sql Connection to open.</p>
  
  <pre><code>await t.TrackDependencyAsync(async() =&gt; { await sqlConnection.OpenAsync(); return true; }, "WaitingSqlConnection");
  </code></pre>
  
  <p><br/>
  If my request is not called for at least 5 minutes, then any new call will see the duration of <code>OpenAsync</code> be huge (c. 3s) instead of immediate.<br/><br/></p>
  
  <p>I'd like to understand the reason to eradicate that crazy slowness.</p>
  
  <p><b>UPDATE</b><br/><br/></p>
  
  <p>I created an endpoint just to open the <code>SqlConnection</code>. If I wait more than 5 minutes then call that <code>OpenConnection</code> endpoint then call any another request, the <code>OpenConnection</code> will incur the waiting cost mentioned above but the request will not.<br/></p>
  
  <p>Hence, I've scheduled a job on Azure to run every minute and call the <code>OpenConnection</code> endpoint. However, when I make requests from my http client, I incur the waiting time. As if opened the <code>SqlConnection</code> was somehow linked to the http client ip...<br/><br/></p>
  
  <p>Also, that 5 minutes windows is typical of DNS TTL... However 3s for a DNS lookup of the Database endpoint is too long. It can't be that.</p>
  
  <p><b>UPDATE 2</b><br/><br/></p>
  
  <p>Time observed at the htt client level seems to be the result of both awaiting for the connection as well as some other latencies (dns lookup?).<br/><br/></p>
  
  <p>Here is a table summarizing what I observe:<br/></p>
  
  <p><a href="https://i.stack.imgur.com/fEKKz.png" rel="noreferrer"><img src="https://i.stack.imgur.com/fEKKz.png" alt="enter image description here"></a></p>
  
  <p><b>UPDATE 3</b><br/><br/></p>
  
  <p>The difference between row 3 and 4 of my table is time spent in TCP/IP Connect and HTTPS Handshake according to Fiddler. Let's not focus on it on that post but only on the time spent waiting for the <code>SqlConnection</code> to open.<br/><br/></p>
  
  <p><b>UPDATE 4</b><br/><br/></p>
  
  <p>Actually I think both two waiting times have the same reason.<br/>
  The server needs to "keep alive" its connection to the database and the client needs to "keep alive" its connection to the server.</p>
  
  <p><b>UPDATE 5</b></p>
  
  <p>I had a job running every 4 minutes to open the <code>SqlConnection</code> but once in a while it was incurring the waiting cost. So I think the inactivity time is 4 minutes not 5 (hence I updated this post title).<br/>
  So I updated my scheduled job to run every minute. then I realised it was still incurring the waiting cost but regularly every 30 minutes (hence I updated this post title).<br/>
  These two times strangely correlates with those of <a href="https://azure.microsoft.com/fr-fr/blog/new-configurable-idle-timeout-for-azure-load-balancer/" rel="noreferrer">Azure Load Balancer Idle Timeout</a>.</p>