CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkasync-await
CthuluHoop
CthuluHoop

Reputation: 136

Entity Framework ToListAsync sometimes hangs, sometimes resolves

I've been working on converting an ASP.NET MVC dashboard page to use asynchronous entity framework methods, and have run into an issue where sometimes a call will either hang indefinitely with no errors or hang for a period of time > 1 minute and resolve. This behavior is sporadic and I haven't found a way to replicate it yet. Here is one of my EF calls, sanitized of course.

Note: The sql and parameters objects are defined above. sql is a query in the form of a string.

using (var context = new BaseDbContext())
{
    return await context.Database.SqlQuery<MyViewModel>(sql, parameters.ToArray()).ToListAsync();
}

This is not the only query that will hang, however. Occasionally other queries will hang, some written in linq to sql others using actual sql queries like the one above.

Important Notes

EDIT Andres suggestion from the comments has potentially pointed me in the right direction to look at the very least. I managed to catch the program when it hung up after several hours of no issues. SQL Profiler found several RPC:Completed events that took upwards of 15-65 seconds for very simple selects (that take fractions of a second to complete when run in SSMS). All of these were fairly close together.

I've sanitized and put the SQL being run below, with start/end times for added context.

exec sp_executesql N'SELECT * FROM Reports.dbo.Customers WHERE Date = @Par1',N'@Par1 datetime',@Par1='2020-07-20 00:00:00'

StartTime: 14:22:08.597

EndTime: 14:22:23.197

exec sp_executesql N'SELECT * FROM Reports.dbo.Customers WHERE Date = @Par1',N'@Par1 datetime',@Par1='2019-07-20 00:00:00'

StartTime: 14:22:23.267

EndTime: 14:22:37.357

exec sp_executesql N'SELECT * FROM Reports.dbo.Policies WHERE Date = @Par1 OR Date = @Par2',N'@Par1 datetime,@Par2 datetime',@Par1='2020-07-20 00:00:00',@Par2='2020-06-20 00:00:00'

Start Time: 14:22:38.200

End Time: 14:23:42.333

exec sp_executesql N'SELECT * FROM Reports.dbo.Policies WHERE Date = @Par1',N'@Par1 datetime',@Par1='2020-07-20 00:00:00'

Start Time: 14:23:46.863

End Time: 14:23:58.773

There was also a more difficult to sanitize Entity Framework query that took ~6 seconds, along with a suspicious looking Audit Logout event in the middle of everything. That event listed its Start Time at 14:18:25.753 and End Time at 14:23:25.770.

While I don't have a ton of knowledge in interpreting these results, it seems to me that the issue might be multiple queries hitting the same DB tables at similar times due to everything being async/await in the MVC app? Prior to converting the dashboard to async/await, I don't believe this ever actually hung up.

Edit 2 Based off Andres answer and what I've learned after adding additional information to my trace in SQL Profiler (see my comment beneath that answer below) it does appear that table locking is the culprit--however I'm struggling to find information on how to resolve this. I'm guessing we will likely have to roll back to using synchronous db calls for everything rather than async?

Upvotes: 1

Views: 1297

Answers (1)

Andres
Andres

Reputation: 2899

Im answering to add some pictures, I cant do it in the comments:

As I said, use SQL Server Profiler to trace the query. This may be related to some sort of blocking or issue at Database level.

I recommend you to add some events to have more information of what is happening:

Add these events:

Transactions:

:enter image description here

Locks: enter image description here

With these events you can check when a Transaction begins, and when does it ends. You should see RCP calls in the middle. Also, with the Locks events, you should be able to see if there is any blocking transation that is locking your query.

If your RCP duration (see column) is high, then the issue is in SQL Server. Probably due to table locking.

Check these events first. Just be careful if you are running the profiler with these events in a Production Environment, it consumes system resources.

Upvotes: 1

Related Questions