什么是SNIReadSyncOverAsync?为什么要花很长时间才能完成? [英] What is SNIReadSyncOverAsync and why would it take a long time to complete?

问题描述

在我们的ASP.Net网站上，我们有一些请求超时.AppDynamics显示，SQL过程调用将在几秒钟内返回，但是我们在SNIReadSyncOverAsync中花费了100多秒钟.

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.

有人知道这种方法是什么/为什么做以及为什么要花费这么多时间?我们没有使用EF，而在我能够找到的每个问题/帖子中都引用了EF.

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.

预先感谢

更新

一段时间以来，我们一直没有解决为什么所有时间都花在SNIReadSyncOverAsync上的问题，我有一些想法.

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.

我认为在这种情况下，可能是特定版本的AppDynamics报告在SQL调用上花费的时间的方式，但是我没有真实的数据来支持这一点，仅是根据我观察到的猜测.我们最终不再看到报告的时间花在了SNIReadSyncOverAsync上，而是转移到了查询本身的超时上.

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.

这样做并没有多大用处，因为相同的查询将在同一数据库中的SSMS中立即运行.

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

最终答案最终与ARITHABORT有关，导致我们的应用程序和SSMS使用两个不同的执行计划(请参见 https://dba.stackexchange.com/a/9841 )，解释了为什么我们无法使用SSMS重现超时.

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.

一旦我们解决了这一问题，便能够确定该过程中需要调整的部分内容，并且自此以后就再也没有遇到无法解释的超时或SNIReadSyncOverAsync了.

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.

推荐答案

不确定您是否已解决此问题，但是:SNI是SQL Server网络接口，并且所提到的方法存在于等待的大多数ADO.NET完整调用堆栈中用于从SQL Server获取数据.不管高层实现是EF，原始ADO.NET还是其他任何东西.

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.

我不确定AppDynamics使用哪种度量标准或信号来捕获存储过程执行的完成，但是如果您的存储过程完成得相对较快，但是您将查询结果从服务器传输到了服务器，则您可能会看到这种行为.您的客户需要一些时间.

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.

在不了解您的基础架构的情况下，很难进一步提供帮助.如果问题仍然存在，我建议在SET STATISTICS TIME ON并将"Include Client Statistics"设置为ON的情况下，在SQL Server Management Studio中运行相同的查询.也许这些数字可以让您了解数据传输是否确实是问题所在.

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.

这篇关于什么是SNIReadSyncOverAsync?为什么要花很长时间才能完成?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！