I am investigating some occasional slowness in a aspnet Webapi method (.NET 4.8, not core). The symptoms are that most of the time the API responds in a few tens of milliseconds, but sometimes it takes a few seconds to respond.
Using MiniProfiler, it appears that most of the time is being spent executing a SQL query. However the API performs several queries and it is not always the same that takes long. Here is an abridged screenshot of a Miniprofiler trace for such request.
Seeing this I assumed that the database was sometimes slow to execute some queries, perhaps waiting for a lock, but I was able to capture the same query that can be seen on the profile using extended events, and it shows that the query actually executed very quickly (the "duration" column is in microseconds):
I know that it is the same query because the parameters are the same and the timestamp is consistent. All queries captured by extended events were quick anyway.
From this I conclude that the cause is not SQL Server. I thought maybe there is some resource exhaustion on the WebApi side so I checked performance counters that were related with threadpool, sql connection pool and garbage collection, but could not observe anything relevant when these slow response times occur.
I'm running out of ideas. Of course, this problem only happens in production and I was unable to reproduct it locally or in our test environment.
How can I identify the cause of these delays?