间歇性“由于打开服务器连接的延迟而无法完成登录过程。” [英] Intermittent "Unable to complete login process due to delay in opening server connection."

问题描述

我有一些查询从远程链接服务器获取数据，并将该数据与我自己数据库中的数据相结合。 有时查询运行非常慢（1-4分钟），而它们通常运行几秒钟。 但有时我在SSMS中收到此错误消息
：  

I have some queries that get data from a remote linked server and combine that data with data in my own database.  Sometimes the queries run VERY slow (1-4 minutes) when they typically run a few seconds.  But sometimes I get this error message in SSMS:  

有没有办法确定导致问题的原因？ 如果是这样，我如何监控这个问题，以便在我收到同事抱怨这个问题的几封电子邮件之前我可以修复它？

Is there a way to determine what is causing the issue?  If so, how do I monitor this issue so I can fix it before I get several emails from coworkers complaining of the issue?

我不想只是增加超时设置。 我想知道为什么会出现这种延迟。

I do not want to just increase the timeout settings.  I want to understand why this delay is happening.

Ryan

推荐答案

通过简单的方法排除故障。

By means not simple to troubleshoot.

发生这种情况时，通常难以登录远程服务器吗？在某些情况下，当SQL Server处于负载状态时，它可能对登录请求的响应速度不高。它也可以用完workerthread，在这种情况下，您的登录尝试会在目标服务器上显示
up THREADPOOL等待。

When this happens, is it difficult in general to log in on the remote server? In some situations, when SQL Server is under load, it may not be very responsive to login requests. It can also run out of workerthread, in which case your login attempt shows up as a THREADPOOL wait on the target server.

如果问题仅在连接到远程时出现问题从你的本地服务器服务器，我会更倾向于认为存在某种网络问题。连接到AD或类似的东西也可能有问题。

If the problem is only when connecting to the remote server from your local server, I would be more inclined to think that there network issues of some sort. There could also be problem in connecting to the AD, or things like that.

我认为自映射是有效的，因此您使用相同的凭据连接到远程服务器你在本地登录时使用？ （您可以运行此查询以查明您是否不知道）：

I assume that self-mapping is in force, so that you connect to the remote server with the same credentials that you use when you login locally? (You can run this query to find out if you don't know):

SELECT * FROM OPENQUERY(SQL2014, 'SELECT SYSTEM_USER AS usrname)

这篇关于间歇性“由于打开服务器连接的延迟而无法完成登录过程。”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！