Within a request on an ApiController, I'm tracking the duration of awaiting the Sql Connection to open.
await t.TrackDependencyAsync(async() => { await sqlConnection.OpenAsync(); return true; }, "WaitingSqlConnection");
If my request is not called for at least 5 minutes, then any new call will see the duration of OpenAsync be huge (c. 3s) instead of immediate.
I'd like to understand the reason to eradicate that crazy slowness.
UPDATE
I created an endpoint just to open the SqlConnection. If I wait more than 5 minutes then call that OpenConnection endpoint then call any another request, the OpenConnection will incur the waiting cost mentioned above but the request will not.
Hence, I've scheduled a job on Azure to run every minute and call the OpenConnection endpoint. However, when I make requests from my http client, I incur the waiting time. As if opened the SqlConnection was somehow linked to the http client ip...
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.
UPDATE 2
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?).
Here is a table summarizing what I observe:
UPDATE 3
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 SqlConnection to open.
UPDATE 4
Actually I think both two waiting times have the same reason.
The server needs to "keep alive" its connection to the database and the client needs to "keep alive" its connection to the server.
UPDATE 5
I had a job running every 4 minutes to open the SqlConnection 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).
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).
These two times strangely correlates with those of Azure Load Balancer Idle Timeout.