2

We have a single WebAPI instance that performs CRUD operations on its AzureSQL using EF6.

We have very low traffic (around 20 req/sec) and normally it takes around 15-150ms for each request. We consistently see that every 45 - 60 minutes that some requests take around 13 seconds each.

The DB is very small – around 6mb. On the SQL logs we see that the queries take around 15ms to run.

Can anyone can help me what it’s the problem?

In both examples, the stopwatch shows 13+ seconds while the DB logs show 15ms

Stopwatch watch = Stopwatch.StartNew();
var result = await Repository.EntitiesSet
                             .Where(entity => entity.PrimaryKey == PrimaryKey); }
                             .ToListAsync();
Logger.LogDbAccess(callData(), watch.ElapsedMilliseconds);
return result;

And

Stopwatch watch = Stopwatch.StartNew();
var result = await Repository.EntitiesSet
                             .FirstOrDefaultAsync(t => t.secondaryId.Equals(secondaryId)); 
Logger.LogDbAccess(callData(), watch.ElapsedMilliseconds);
return result;
Gil Matzov
  • 213
  • 2
  • 12
  • What we don't know and can't assume is what's happening on the wire. Is this a local db instance or remote instance? If you run a communications trace, what is the elapsed time from DB of first send to end. We also can't see the connection being made in example above.... Nor can we see it being closed, not sure if that's an issue. One tip is that application layers are not able to get on the wire until a DNS resolve is made, could that be the issue? – JWP Jun 07 '16 at 12:14
  • 2
    Sounds like your applications going to sleep mode if its been idle for some time. Try to configure the IIS appPool to always running https://www.simple-talk.com/blogs/2013/03/05/speeding-up-your-application-with-the-iis-auto-start-feature/ – Marcus Höglund Jun 07 '16 at 12:25
  • What is the lifetime of `Repository`? Is it created for each request or do you reuse the same `DbContext` for all requests? You should create a new context for each request. Another thing you can check if garbage collection. if you load a lot of entities you might get delays because the GC needs to run. – daramasala Jun 07 '16 at 12:55
  • This is a remote DB in azure as my service is. This code is in the middle of an API call so it's already run. The `Repository` was just created and the `DbContext` as well. In the longer calls I have Additionally 3 more calls to the DB. – Gil Matzov Jun 07 '16 at 14:51
  • If you already on Azure, turn on NewRelic for your app. It stores every request that takes longer then 2 secons with very detail tracing. Maybe you can find something in this trace information. – Pavel Kutakov Jun 08 '16 at 11:59

0 Answers0