从ASP.NET调用存储过程比从SQL Mgmt Admin调用慢

Question

我们正在尝试诊断一个复杂存储过程的缓慢问题（其中包含几个巨大的查询语句）。

当我们从ASP.NET调用该存储过程时需要5秒。

但是，当我们从SQL Management Studio中调用它（只使用EXEC），仅需要0.05秒。

我们已经以许多不同的方式和情况始终测试了这种行为。

这是在C#.NET环境下进行的。数据库是MS SQL Server 2012。

这个问题与Web应用有关，但我们编写了一个小的控制台应用程序作为测试工具，结果显示相同。

1) 我们在C#.NET控制台应用程序中计算经过时间的方法如下：

stopwatch.Start();
rdr = cmd.ExecuteReader();
stopwatch.Stop();

2）我们通过在查询之前和之后调用GETDATE()来计算SQL存储过程内的经过时间，然后将这些时间存储在一个小表中。我们可以在SQL Mgmt Studio中查询该表，以查看SP中的查询花费了多长时间。

这样我们就可以看到在整个过程中有多少时间花费在SQL上，而其中99%的时间都花费在SQL上。

但是如果在SQL Mgmt Studio中没有相同的缓慢现象，那么调试和改进会很困难。

所以我的问题是，为什么会有差别？可能是SQL Mgmt Studio与控制台应用程序锁定方式不同吗？

- royappa

---

可能是https://dev59.com/t-o6XIcBkEYKwwoYTzNK?rq=1的重复问题。 - Rowland Shaw

首先要做的是比较执行计划。 - RBarryYoung

你是否在消耗这些记录？当你只是执行读取器而不获取记录时，SQL Server 将等待“接收确认”。循环到读取器的末尾并检查之后仍然存在此问题。 - souplex

请注意，这与锁定有关的可能性非常小。 - RBarryYoung

@RBarryYoung和所有回答的人，结果发现参数嗅探是问题所在。我们重写了存储过程，将参数分配到本地变量中，并在查询中使用这些变量。性能立即变得与SMSS相同（而且非常快）。我不能在这里接受答案，因为它不是直接的解决方案，但肯定会点赞以及个别评论。非常感谢大家的帮助！ - royappa

Answer 1

这种行为常常源于ADO.NET和SSMS得到不同的执行计划。这是因为执行计划必须考虑到SQL本身以及ANSI_NULLS，ARITHABORT和其他设置等上下文环境。所以，如果这些设置不一致，一个环境下的执行计划就不能在另一个环境中使用。

在默认设置下，除了ARITHABORT外，SSMS和ADO.NET中所有内容都相同。ADO.NET设置为OFF，SSMS设置为ON。因此，为了获得与应用程序相同的缓存查询计划，您需要将SSMS中的ARITHABORT设置为OFF。现在，您应该在SSMS中看到与应用程序调用中相同的性能。在这篇好文章中了解更多背景信息：http://www.sommarskog.se/query-plan-mysteries.html

如果您的查询返回大量数据，则还有另一个因素，因为默认情况下，SSMS在完成并显示总查询时间之前读取并显示所有数据。它读取数据的速度取决于您在哪里执行SSMS，本地执行还是远程执行。在远程执行的情况下，数据必须通过网络传输，通常比本地调用慢。通常，测量传输时间是可以接受的，因为您的应用程序也会执行相同的操作。但是，SSMS还会显示数据，这可能比实际加载数据要慢得多。为了防止这种情况，您可以通过“工具->选项->查询结果->SQL Server->结果到网格->执行后丢弃结果”来禁用SSMS中的数据显示。

如果仍然出现不同的行为，请使用SQL Profiler捕获执行计划并进行比较。

- TToni

---

我会查看这个设置。如果没有其他问题，那么肯定可以将其排除在外，所以谢谢。至于“返回的数据”问题，我们不是在关注SSMS传输/显示数据所花费的时间。我们在存储过程本身中插入了前后时间戳，夹在巨大的SELECT查询之间，并将其记录到一个单独的表中。这就是我们关注的时间，而不是SSMS报告的时间。 - royappa

我会接受这个答案，因为比较执行计划最终会导致我们找到答案，并且这对未来的情况也很有用。谢谢！ - royappa

Answer 2

去年我遇到了类似的问题。尝试在您的存储过程中启用arithabort: SET ARITHABORT ON

摘自msdn

SQL Server Management Studio的默认ARITHABORT设置为ON。将ARITHABORT设置为OFF的客户端应用程序可能会收到不同的查询计划，从而使得难以排查性能较差的查询。也就是说，在管理工具中相同的查询可以快速执行，但在应用程序中却很慢。 使用管理工具调试查询时，始终要匹配客户端的ARITHABORT设置。

- Bruno