SQL 如何避免Sql查询超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/853676/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid Sql Query Timeout
提问by R.D
I have RO access on a SQL View. This query below times out. How to avoid this?
我对 SQL 视图具有 RO 访问权限。下面的这个查询超时。如何避免这种情况？
select
count(distinct Status)
from
[MyTable] with (NOLOCK)
where
MemberType=6
The error message I get is:
我得到的错误信息是：
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
消息 121，级别 20，状态 0，第 0 行
从服务器接收结果时发生传输级错误（提供者：TCP 提供者，错误：0 - 信号量超时期限已过期。）
采纳答案by Cade Roux
Although there is clearly some kind of network instability or something interfering with your connection (15 minutes is possible that you could be crossing a NAT boundary or something in your network is dropping the session), I would think you want such a simple?) query to return well within any anticipated timeoue (like 1s).
尽管显然存在某种网络不稳定或某些干扰您的连接（您可能跨越 NAT 边界 15 分钟或网络中的某些内容正在丢弃会话），但我认为您想要这样一个简单的？）查询在任何预期的时间（如 1 秒）内返回良好。
I would talk to your DBA and get an index created on the underlying tables on MemberType, Status. If there isn't a single underlying table or these are more complex and created by the view or UDF, and you are running SQL Server 2005 or above, have him consider indexing the view (basically materializing the view in an indexed fashion).
我会与您的 DBA 交谈并获取在 MemberType、Status 的基础表上创建的索引。如果没有单个基础表或者这些表更复杂并且由视图或 UDF 创建，并且您运行的是 SQL Server 2005 或更高版本，请让他考虑索引视图（基本上以索引方式具体化视图）。
回答by codeulike
Your query is probably fine. "The semaphore timeout period has expired" is a Network error, not a SQL Server timeout.
您的查询可能没问题。“信号量超时期限已过期”是网络错误，而不是 SQL Server 超时。
e.g. see http://support.microsoft.com/kb/325487
例如见http://support.microsoft.com/kb/325487
There is apparently some sort of network problem between you and the SQL Server.
您和 SQL Server 之间显然存在某种网络问题。
edit: However, apparently the query runs for 15-20 min before giving the network error. That is a very long time, so perhaps the network error could be related to the long execution time. Optimization of the underlying View might help.
编辑：但是，显然查询在给出网络错误之前运行了 15-20 分钟。这是一个很长的时间，所以网络错误可能与执行时间长有关。底层视图的优化可能会有所帮助。
If [MyTable] in your example is a View, can you post the View Definition so that we can have a go at optimizing it?
如果您的示例中的 [MyTable] 是一个视图，您能否发布视图定义以便我们对其进行优化？
回答by Johnno Nolan
You could put an index on MemberType.
您可以在 MemberType 上放置一个索引。
回答by MicSim
Please check your Windows system event log for any errors specifically for the "Event Source: Dhcp". It's very likely a networking error related to DHCP. Address lease time expired or so. It shouldn't be a problem related to the SQL Server or the query itself.
请检查您的 Windows 系统事件日志是否有任何专门针对“事件源：Dhcp”的错误。很可能是与 DHCP 相关的网络错误。地址租用时间到期左右。它不应该是与 SQL Server 或查询本身相关的问题。
Just search the internet for "The semaphore timeout period has expired" and you'll get plenty of suggestions what might be a solution for your problem. Unfortunately there doesn't seem to be thesolution for this problem.
只需在互联网上搜索“信号量超时期限已过期”，您就会得到大量建议，这些建议可能是您问题的解决方案。不幸的是，似乎没有要在这个问题的解决方案。
回答by great_llama
Do you have an index defined over the Status column and MemberType column?
您是否在 Status 列和 MemberType 列上定义了索引？
回答by DForck42
how many records do you have? are there any indexes on the table? try this:
你有多少记录？表上有索引吗？尝试这个：
;with a as (
select distinct Status
from MyTable
where MemberType=6
)
select count(Status)
from a
回答by user2462054
My team were experiencing these issues intermittently with long running SSIS packages. This has been happening since Windows server patching.
我的团队在长时间运行的 SSIS 包中间歇性地遇到这些问题。自 Windows 服务器修补以来，这种情况一直在发生。
Our SSIS and SQL servers are on separate VM servers.
我们的 SSIS 和 SQL 服务器位于不同的 VM 服务器上。
Working with our Wintel Servers team we rebooted both servers and for the moment, the problem appears to have gone away.
与我们的 Wintel 服务器团队合作，我们重新启动了两台服务器，目前，问题似乎已经消失。
The engineer has said that they're unsure if the issue is the patches or new VMTools that they updated at the same time. We'll monitor for now and if the timeout problems recur, they'll try rolling back the VMXNET3 driver, first, then if that doesn't work, take off the June Rollup patches.
工程师表示他们不确定问题是否出在他们同时更新的补丁或新 VMTools 上。我们现在将进行监控，如果超时问题再次出现，他们将首先尝试回滚 VMXNET3 驱动程序，然后如果这不起作用，则取消 June Rollup 补丁。
So for us the issue is nothing to do with our SQL Queries (we're loading billions of new rows so it has to be long running).
所以对我们来说，这个问题与我们的 SQL 查询无关（我们正在加载数十亿的新行，所以它必须长时间运行）。
回答by Himanshu N Tatariya
This is happen because another instance of sql server is running. So you need to kill first then you can able to login to SQL Server.
这是因为另一个 sql server 实例正在运行。所以你需要先杀死然后你才能登录到SQL Server。
For that go to Task Manager and Kill or End Task the SQL Server service then go to Services.msc and start the SQL Server service.
为此，请转到任务管理器并终止或结束任务 SQL Server 服务，然后转到 Services.msc 并启动 SQL Server 服务。
回答by AcePL
While I would be tempted to blame my issues - I'm getting the same error with my query, which is much, much bigger and involves a lot of loops - on the network, I think this is not the case.
虽然我很想归咎于我的问题——我的查询出现了同样的错误，这个错误要大得多并且涉及很多循环——在网络上，我认为情况并非如此。
Unfortunately it's not that simple. Query runs for 3+ hours before getting that error and apparently it crashes at the same time if it's just a query in SSMS and a job on SQL Server (did not look into details of that yet, so not sure if it's the same error; definitely same spot, though).
不幸的是，事情并没有那么简单。查询在出现该错误之前运行了 3 个多小时，如果它只是 SSMS 中的查询和 SQL Server 上的作业，它显然会同时崩溃（还没有研究详细信息，所以不确定它是否是相同的错误；不过，绝对是同一个地方）。
So just in case someone comes here with similar problem, this thread: https://www.sqlservercentral.com/Forums/569962/The-semaphore-timeout-period-has-expired
因此，以防万一有人遇到类似问题，此线程：https: //www.sqlservercentral.com/Forums/569962/The-semaphore-timeout-period-has-expired
suggest that it may equally well be a hardware issue or actual timeout.
建议它同样可能是硬件问题或实际超时。
My loops aren't even (they depend on sales level in given month) in terms of time required for each, so good month takes about 20 mins to calculate (query looks at 4 years).
我的循环不是均匀的（它们取决于给定月份的销售水平）就每个所需的时间而言，所以好的月份需要大约 20 分钟来计算（查询看起来是 4 年）。
That way it's entirely possible I need to optimise my query. I would even say it's likely, as some changes I did included new tables, which are heaps... So another round of indexing my data before tearing into VM config and hardware tests.
这样我完全有可能需要优化我的查询。我什至会说这很可能，因为我所做的一些更改包括新表，这些表是堆......所以在撕裂 VM 配置和硬件测试之前，另一轮索引我的数据。
Being aware that this is old question: I'm on SQL Server 2012 SE, SSMS is 2018 Beta and VM the SQL Server runs on has exclusive use of 132GB of RAM (30% total), 8 cores, and 2TB of SSD SAN.
请注意，这是一个老问题：我使用的是 SQL Server 2012 SE，SSMS 是 2018 Beta，并且运行 SQL Server 的 VM 独占使用 132GB 的 RAM（总共 30%）、8 个内核和 2TB 的 SSD SAN。
