We have several OData API's using Entity Framework and AutoMapper. These connect to an on-premise SQL database through a VNet. The GET requests of this API are not asynchronous per example found here. The scaling is set to S2. We have enabled always on.
Sometimes the requests complete in 500 ms. Sometimes the very same requests take 40 seconds. We have tried scaling out but this offers no tangible benefit. We have tried making the GET function on the controllers async. We have tried disabling authentication. We have tried looking at the application insights call stack in the profiler but sometimes the code hangs on one call, while other times on another. We even found a 39 second call to String.Replace(). We've tried Kudu but can't seem to get any knowledge from it.
On top of this I alone succeed in bringing the server to its knees simply by spamming F5 on a relatively simple request, locking the CPU at 100%. S2 seems pretty high already, and we are stunned that the server apparently cannot handle it. And it's also not always the case that low CPU usage on the server equals fast requests. Sometimes these requests also take an extraordinary amount of time.
We have tried looking at the application insights data but grow even more confused as some data suggests one thing is at fault while other data suggests it is not.
CPU usage on the app service plan is high.
CPU usage in the live metrics usually remains low.
This suggests that SQL is at fault. But we have almost ruled that out since if we spam an API on one app service plan and send the same single request to another app service plan we get the result immediately.
This suggests that the code or server is at fault.
How can we diagnose this issue and find the bottleneck?
