查询从 Web 应用程序超时，但从管理工作室运行良好 [英] Query times out from web app but runs fine from management studio

问题描述

这是我在另一个论坛上提出的一个问题，得到了一些不错的答案，但我想看看这里是否有人有更多的见解.

This is a question I asked on another forum which received some decent answers, but I wanted to see if anyone here has more insight.

问题是您的 Web 应用程序中的一个页面在调用存储过程时超时，因此您使用 Sql Profiler 或您的应用程序跟踪日志来查找查询并将其粘贴到管理中工作室来弄清楚为什么它运行缓慢.但是你从那里运行它，它会一直燃烧，每次都在不到一秒的时间内返回.

The problem is that you have one of your pages in a web application timing out when it gets to a stored procedure call, so you use Sql Profiler, or your application trace logs, to find the query and you paste it into management studio to figure our why it's running slow. But you run it from there and it just blazes along, returning in less than a second each time.

我的特殊情况是使用 ASP.NET 2.0 和 Sql Server 2005，但我认为这个问题可能适用于任何 RDBMS 系统.

My particular case was using ASP.NET 2.0 and Sql Server 2005, but I think the problem could apply to any RDBMS system.

推荐答案

这是我目前从研究中学到的东西.

This is what I've learned so far from my research.

.NET 发送的连接设置与您登录管理工作室时获得的设置不同.如果您使用 Sql Profiler 嗅探连接，您会看到以下内容:

.NET sends in connection settings that are not the same as what you get when you log in to management studio. Here is what you see if you sniff the connection with Sql Profiler:

-- network protocol: TCP/IP  
set quoted_identifier off  
set arithabort off  
set numeric_roundabort off  
set ansi_warnings on  
set ansi_padding on  
set ansi_nulls off  
set concat_null_yields_null on  
set cursor_close_on_commit off  
set implicit_transactions off  
set language us_english  
set dateformat mdy  
set datefirst 7  
set transaction isolation level read committed  

我现在将这些设置粘贴到我登录到 sql server 时运行的每个查询的上方，以确保设置相同.

I am now pasting those setting in above every query that I run when logged in to sql server, to make sure the settings are the same.

对于这种情况，我在断开连接和重新连接后分别尝试了每个设置，发现将 arithabort 从 off 更改为 on 将问题查询从 90 秒减少到 1 秒.

For this case, I tried each setting individually, after disconnecting and reconnecting, and found that changing arithabort from off to on reduced the problem query from 90 seconds to 1 second.

最可能的解释与参数嗅探有关，这是 Sql Server 用来挑选它认为最有效的查询计划的一种技术.当您更改其中一个连接设置时，查询优化器可能会选择不同的计划，在这种情况下，它显然选择了错误的计划.

The most probable explanation is related to parameter sniffing, which is a technique Sql Server uses to pick what it thinks is the most effective query plan. When you change one of the connection settings, the query optimizer might choose a different plan, and in this case, it apparently chose a bad one.

但我并不完全相信这一点.更改此设置后，我尝试比较实际查询计划，但尚未看到差异显示任何更改.

But I'm not totally convinced of this. I have tried comparing the actual query plans after changing this setting and I have yet to see the diff show any changes.

arithabort 设置是否还有其他原因可能导致查询在某些情况下运行缓慢?

Is there something else about the arithabort setting that might cause a query to run slowly in some cases?

解决方案看起来很简单:只需将 set arithabort on 放到存储过程的顶部即可.但这可能会导致相反的问题:更改查询参数，突然它在关闭"时比打开"运行得更快.

The solution seemed simple: Just put set arithabort on into the top of the stored procedure. But this could lead to the opposite problem: change the query parameters and suddenly it runs faster with 'off' than 'on'.

目前我正在运行重新编译"程序以确保每次都重新生成计划.对于这个特定的报告来说没问题，因为重新编译可能需要一秒钟，而这在需要 1-10 秒返回的报告中并不太明显(这是一个怪物).

For the time being I am running the procedure 'with recompile' to make sure the plan gets regenerated each time. It's Ok for this particular report, since it takes maybe a second to recompile, and this isn't too noticeable on a report that takes 1-10 seconds to return (it's a monster).

但对于其他运行更频繁且需要在短短几毫秒内尽快返回的查询来说，它不是一种选择.

But it's not an option for other queries that run much more frequently and need to return as quickly as possible, in just a few milliseconds.

这篇关于查询从 Web 应用程序超时，但从管理工作室运行良好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！