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.