EF Core 3.1.14 Recurring Cold Start

Question

We have deployed a very simple .NET CORE 3 Web API application to Azure Cloud. The application is a web api and talks to a very simple SQL server database hosted in Azure as well. There are two main performance problems we are noticing

All API calls go to DB for either read or write operations. The tables only contain 4 and 5 rows and the queries are just basic select and insert queries with no joins.

1. The first call to the API is very slow (30 seconds to query 1 record in a table of size 10) and we added the timer and noticed it is the DB call that is taking 99.99% of the time. So I used the Azure Data Studio Profiler and realized that the query reached SQL Server after like 29.90 seconds. So the issue is not the query itself. Also, the second, third query etc. are super fast and return within < 30 milli-seconds. So the issue is not the internet connectivity between the Web App and the Azure SQL Database.
2. The bigger problem is that, if you stop calling the API for say 2-3 minutes and then do another call, then again the first query takes 30 seconds. But the subsequent queries are faster.

If this was only happening when w3wp.exe starts then I wouldn't be concerned but if the requests to the API stop coming for 2-3 minutes then again it is down. This is of concern.

We have Always ON set to Yes.

I tried collecting .NET Trace in Azure for the web app but this is giving me this weird error.

Here are the Nuget package versions installed in the VS solution related to EF.

Is there any other way to collect trace for Azure Web APP. I really need to see the call stack of the code for those 30 seconds to move forward. I have access to KUDU etc.

Thanks.

Answer 1

Ok so posting an answer to my question. It turned out that there was no issue with web application, app service plan, sql server or entity framework. I took a network trace of my application and 1 other application which doesn't have any issue and opened it with network monitor. We noted that they are taking different paths. After looking into the IP address we realized that the other application had a virtual network setup. You can see that by going to your app service plan and then click on Networking option in the left menu bar. And then choose the first one for vNet. Once we configured vNet, then all responses were within < 1 second.

There was one another oversight by me. The Auth0 calls were also taking 14 seconds sometimes. And when I tried running tcpping google.com from KUDU that timed out sometimes as well. But was working fine for other web applications.