I'm trying to find a resource that will explain the sequence that occurs (in detail) when an ASP.NET application using Entity Framework performs a transaction in SQL Server 2016. Specifically what I'm looking for is what are all of the actions that occur (i.e. .NET events, EF events, packets sent, packets received, protocols used, SQL events, etc.) when a developer updates a record in a table for instance.
I'll explain the situation I'm trying to resolve. I have a large (2+TB) SQL 2016 database running on a virtualized Windows 2012 Server. When SQL host CPU load is high (70%+), we begin to see EF connection counts climb and eventually break the max connection pool limit. There begin to be a large number of sleeping connections, long locks, timeouts, deadlocks and if left long enough... connections break. They keep trying to reconnect, but it's as if the SQL Server refuses to accept any connections. At this point the connection count is above 750 on the SQL Server. When the host CPU load is manageable, everything seems to run fine and connections remain stable.
I found this article that seems to sum up the symptoms I'm seeing pretty well. I'm thinking with the combination of high CPU load and the virtualization layer between the SQL host and SQL Server, there's enough latency that RPC calls are being delayed enough that SQL is slowing enough to start causing transactions to start backing up and causing an ever increasing problem.
However, I'm looking for a good way to see the problem in action, which means I need to understand the entire sequence that is occurring. Are there counters I can watch? Do I need to sniff the network? Any help would be appreciated.