What is SNIReadSyncOverAsync and why would it take a long time to complete?

Question

On our ASP.Net website, we've had some requests timeout. AppDynamics shows that the SQL procedure calls are returning in a matter of seconds, but we're spending 100+ seconds in SNIReadSyncOverAsync.

Does anyone know what this method is / does and why it would be taking that much time? We're not using EF which is referenced in every question / post I've been able to find about it.

Thanks in advance

Update

It's been a while and while we never came to a resolution as to why all of the time was being spent in SNIReadSyncOverAsync, I have a few thoughts.

I think that in this case, it may have been the way that specific version of AppDynamics was reporting the time spent on the SQL calls, but I have no real data to back that up, just my guess from what I observed. We eventually stopped seeing the time reported as being spent in SNIReadSyncOverAsync and it shifted to the queries themselves timing out.

That still didn't make a lot of sense because the same queries would run instantly in SSMS on the same database.

The ultimate answer ended up being related to ARITHABORT causing our application and SSMS to use two different execution plans (see https://dba.stackexchange.com/a/9841), explaining why we couldn't reproduce the timeouts with SSMS.

Once we resolved that, we were able to identify a few portions of the procedure that needed tuning and we haven't run into the unexplained timeouts or SNIReadSyncOverAsync since.

Answer 1

Not sure if you already have resolved this, but: SNI is SQL Server Network Interface, and the mentioned method exists in most ADO.NET full call stacks that wait for data from SQL Server. This is regardless of whether the higher-level implementation is EF, raw ADO.NET or whatever.

I'm not sure which metric or signal AppDynamics uses to capture the completion of a stored procedure execution, but you could be seeing this kind of behavior if your stored procedure completes relatively fast, but transmitting the query result from the server to your client takes a while.

Without knowing more about your infrastructure, it is very hard to help further. If the problem still persists, I would recommend running the same query in SQL Server Management studio with SET STATISTICS TIME ON and "Include Client Statistics" switched to on. Perhaps those numbers would give you an idea on whether data transfer is actually the problem.

Answer 2

In my case it was indeed, as Jouni mentions, very slow transmitting of the query results. I use Automapper to prepare data for sending to client. So, it's unclear what exact property caused the load but to be sure I've cut all compound ones I don't need to show on client side. (I originally needed a collection to show in grid on client side.) The execution became very fast.