Question 87481 in ASP.NET Question posted in
The official documentation can be found here.

How to find the cause of occasional slowness in .NET WebApi – Asp.net

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.

A screenshot of Miniprofiler

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):

A screenshot of Extended Events

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?

Tags:

2

Answers


  1. 0

    I don’t think you have all the information required to diagnose whether SQL Server is slow based on the RPC Completed Event. That duration does not reflect the duration of the entire RPC process, just how long it took for the wrap up event. There is another event called RPC Starting that you need to monitor, then calculation the duration between those two yourself.

    Login or Signup to reply.
  2. 0

    Same Query with same paramter are sometime slow and sometime fast

    1. Query is Recompiling very frequently.Somehow "cache plan" are getting clear after sometime so query is recompiling .

    2. Parameter Sniffing :

    3. May be same query and same paramter retun diffrent no. of rows and thus query is not optimized

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search