SQL连接超时
我们的一个网站一直面临奇怪的连接超时问题。
我们的环境由 IIS 7 Web 服务器（在 Windows Server 2008 R2 标准版上运行）和 SQL Server 2008 数据库服务器组成。
在调试引发超时的网站功能时，我们注意到连接本身需要几毫秒才能完成，但是调用数据库上的存储过程的 SqlCommand 在执行过程中挂起几分钟，然后引发超时异常。
另一方面，当我们直接在数据库上运行存储过程时，只需 2 秒即可正确完成执行。
我们已经尝试了以下操作：
- 修改了网站代码上的
SqlCommand超时 - 修改了
web.config文件 - 上的执行超时 修改了
sessionState超时>web.config 文件 - 修改
web.config文件上的授权 cookie 超时 修改 - IIS 上网站属性的连接超时 修改 IIS
- 上应用程序池关闭时间限制
- 检查应用程序池空闲暂停在 IIS 上
- 检查了 SQL Server 属性上的执行超时（设置为 0，无限制）
- 使用其他参数直接在数据库上测试了存储过程
我们感谢任何帮助。
尼拉夫
We have been facing weird connection timeouts on one of our websites.
Our environment is composed of an IIS 7 web server (running on Windows Server 2008 R2 Standard Edition) and an SQL Server 2008 database server.
When debugging the website functionality that provokes the timeout, we notice that the connection itself takes milliseconds to complete, but the SqlCommand, which invokes a stored procedure on the database, hangs for several minutes during execution, then raises the timeout exception.
On the other hand, when we run the stored procedure directly on the database, it takes only 2 seconds to correctly finish execution.
We already tried the following:
- Modified
SqlCommandtimeout on the website code - Modified execution timeout on the
web.configfile - Modified
sessionStatetimeout on theweb.configfile - Modified authorization cookie timeout on the
web.configfile - Modified the connection timeout on the website properties on IIS
- Modified the application pool shutdown time limit on IIS
- Checked the application pool idle timeout on IIS
- Checked the execution timeout on the SQL Server properties (it's set to 0, unlimited)
- Tested the stored procedure directly on the database with other parameters
We appreciate any help.
Nirav
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我在存储过程中遇到了同样的问题，该存储过程是用户的搜索功能。我尝试了一切，包括 ARTIHABORT 等。SP 加入了许多表，因为用户可以搜索任何内容。 SP 的许多参数都是可选的，这意味着它们在 SP 中具有默认值 NULL。什么都没起作用。
我通过确保我的 ADO.NET 代码仅在用户选择值的位置添加参数来“修复”该问题。 SP 的执行时间从几分钟缩短到几秒。我假设当仅将具有实际值的参数传递给 SP 时，SQL Server 可以更好地处理执行计划。
请注意，这是针对 SQL Server 2000 的。
I've had this same issue with a stored procedure that was a search feature for the users. I tried everything, include ARTIHABORT etc. The SP joined many tables, as the users could search on anything. Many of the parameters for the SP were optional, meaning they had a default value of NULL in the SP. Nothing worked.
I "fixed" it by making sure my ADO.NET code only added parameters where the user selected a value. The SP went from many minutes to seconds in execution time. I'm assuming that SQL Server handled the execution plan better when only parameters with actual values were passed to the SP.
Note that this was for SQL Server 2000.
几年前，我在将应用程序从 SQL2000 迁移到 SQL2008 时遇到了类似的问题。
我将
OPTION (RECOMPILE)添加到数据库中出现问题的所有存储过程的末尾。就我而言，它与对存储过程的调用之间非常不同的参数有关。强制 proc 重新编译将强制 SQL 提出一个新的执行计划，而不是尝试使用对于新参数来说可能不是最佳的缓存版本。如果您还没有这样做，请检查您的索引。没有什么比缺乏急需的索引更能降低数据库性能了。这是一个很好的链接（http://sqlfool .com/2009/04/a-look-at-missing-indexes/）在将显示缺失索引的查询上。
A few years ago I had a similar problem when migrating an app from SQL2000 to SQL2008.
I added
OPTION (RECOMPILE)to the end of all the stored procs in the database that was having problems. In my case it had to do with parameters that were very different between calls to the stored proc. Forcing the proc to recompile will force SQL to come up with a new execution plan instead of trying to use a cached version that may be sub-optimal for the new params.And in case you haven't done it already, check your indexes. Nothing can kill db performance like lack of a badly needed index. Here is a good link (http://sqlfool.com/2009/04/a-look-at-missing-indexes/) on a query that will display missing indexes.
超级超级迟到的建议，但可能对其他人来说很方便：我看到的一个典型问题并且适用于 Java 如下：
您有一个以字符串作为参数的查询。该字符串是数据库中 varchar(N) 列的搜索条件。但是，您在查询中以 Unicode (nvarchar(N)) 形式提交字符串参数。这将导致全表扫描并将每个字段值转换为 Unicode 以便进行正确比较，以避免潜在的数据丢失（如果 SQL Server 将输入参数转换为非 Unicode，则可能会丢失信息）。
简单测试：运行查询两次（为了简单起见，我假设它是一个 SP）：
看看它们的行为如何。另外，您可能需要查看 SSMS 中活动监视器上的“最近的昂贵查询”部分，并询问执行计划，以澄清情况。
干杯，
埃里克
Super-super late suggestion, but might come handy for others: A typical issue I saw and rather applicable to Java is the following:
You have a query which takes a string as a parameter. That string is search criterion on a varchar(N) column in the database. however, you submit the string param in the query as Unicode (nvarchar(N)). This will result in a full-table scan and conversion of every single field values to Unicode for proper comparison, to avoid potential data loss (if SQL Server converted the input param to non-Unicode, it may lose information).
Simple test: run the query twice (for the sake of simplicity, I'm assuming it's an SP):
See how they behave. Also, you may want to take a look at the Recent Expensive Queries section on the Activity Monitor in SSMS and ask for the execution plan, to clarify the situation.
Cheers,
Erik