问跨链接服务器的远程查询的SQL性能问题

EN

您可以打开跟踪标志7300，这可能会给您提供更详细的错误消息

一个有代表性的查询返回多少行？这两台服务器之间的网络连接速度/可靠性如何？

大数据集可能要花费太长时间来传输(在实际查询时间之上)。您可以提高超时值。

您可以尝试按以下方式重新配置超时设置：

将远程登录超时设置为300秒：

sp_configure 'remote login timeout', 300
go 
reconfigure with override 
go 

将远程查询超时设置为0(无限等待)：

sp_configure 'remote query timeout', 0 
go 
reconfigure with override 
go 

更新: Server 2012 SP1 :具有SELECT权限的用户将能够访问DBCC SHOW_STATISTICS，这将提高链接服务器上的只读性能。参考文献：https://msdn.microsoft.com/en-us/library/ms174384(v=sql.110).aspx

更新:你说的不是数据大小或连接速度，这是正确的。它在我朦胧的记忆中敲响了一个铃铛，我想起了我在哪里看到过它：应用程序慢，在SSMS中快？(链接服务器的问题)。这不是参数嗅探，而是统计信息本身丢失(由于权限原因)，导致使用了糟糕的查询计划：

你可以看到，估计是不同的。当我作为sysadmin运行时，估计值是1行，这是一个正确的数字，因为在Northwind中没有订单ID超过20000。但当我以普通用户的身份运行时，估计为249行。我们认识到这个特定的数字是830个订单中的30 %，或者是当优化器没有信息时对一个不等式操作的估计。以前，这是由于一个未知的变量值，但在这种情况下，没有一个变量可以是未知的。不，缺少的是统计数字本身。只要查询只访问本地服务器中的表，优化器就可以始终访问查询中所有表的统计信息；没有额外的权限检查。但这与链接服务器上的表不同。当server访问链接服务器时，不存在仅用于服务器间通信的秘密协议。不是，相反，Server为链接服务器使用标准的OLE DB接口，是其他Server实例、Oracle、文本文件或您自己酿造的数据源，并与任何其他用户连接。准确地检索统计信息取决于数据源和所讨论的OLE DB提供程序。在这种情况下，提供程序是Server本机客户端，它分两个步骤检索统计信息。(您可以通过在远程服务器上运行Profiler来看到这一点)。首先，提供程序运行过程sp_table_statistics2_rowset，该过程返回关于有哪些列统计信息以及它们的基数和密度信息的信息。在第二步中，提供程序运行DBCC SHOW_STATISTICS，这是一个返回完整分布统计信息的命令。(在本文的后面，我们将更深入地研究这个命令。)下面是一个问题:要运行DBCC SHOW_STATISTICS，您必须是服务器角色sysadmin或任何数据库角色db_owner或db_ddladmin的成员。这就是我得到不同结果的原因。在作为sysadmin运行时，我得到了完整的分布统计信息，它表明没有订单ID > 20000的行，估计值是一行。(回想一下，优化器从不从统计数据中假定零行。)但是，当以普通用户的身份运行时，DBCC SHOW_STATISTICS由于权限错误而失败。这个错误没有传播，但是优化器接受没有统计信息，并使用默认假设。由于它确实获得了基数信息，它了解到远程表有830行，其中估计为249行。每当您遇到性能问题时，如果包含对链接服务器的访问的查询在应用程序中运行缓慢，但在从SSMS测试该查询时运行速度较快，则应始终研究远程数据库上权限不足是否可能是造成此问题的原因。(请记住，对链接服务器的访问在查询中可能不是公开的，而是可能隐藏在视图中。)如果确定远程数据库的权限是问题所在，可以采取哪些操作？

• 您可以将用户添加到角色db_ddladmin中，但是由于这给了他们添加和删除表的权限，所以这是不推荐的。
• 默认情况下，当用户连接到远程服务器时，他们以自己的身份进行连接，但您可以使用sp_addlinkedsrvlogin设置登录映射，以便用户映射到具有db_ddladmin成员资格的代理帐户。请注意，此代理帐户必须是SQL登录名，因此如果远程服务器没有启用SQL身份验证，则这不是选项。从安全的角度来看，这个解决方案也有点可疑，尽管它比以前的建议更好。
• 在某些情况下，您可以使用OPENQUERY重写查询，以强制在远程服务器上进行计算。如果查询包括多个远程表，这可能特别有用。(但它也可能适得其反，因为优化器现在从远程服务器获得的统计信息甚至更少。)
• 当然，您可以使用完整的提示和计划指南来获得您想要的计划。
• 最后，您应该问自己是否需要链接服务器访问。也许数据库可以在同一台服务器上？可以复制数据吗？其他解决方案？

我有两个可能有帮助的想法。我还要告诉您，在针对链接服务器运行查询的性能方面，我的运气好坏参半。所以我的第一个建议是，如果可以的话，尽量避免。

我的第一个想法是将存储过程安装到server 2000框中，让它引用本地服务器。然后可以远程执行存储过程。

exec linkedserver.database.dbo.Get_Accounts

如果你能走这条路，它应该能极大地提高性能。

我的第二个想法是在运行存储过程时获得估计的查询计划。它让你明白了为什么要花这么多时间吗？一个潜在的问题是，您在链接服务器上使用的帐户可能没有足够的权限获取表统计信息(链接服务器比本地服务器需要更多的权限)。这会使质疑变得令人难以置信的缓慢。您可以阅读更多关于这个特定问题的这里。

当您尝试此操作时会发生什么(即显式地指示应该在远程服务器上运行什么)：

select [fields]
into dbo.current_accounts
from OPENQUERY(linkedserver, 'SELECT [fields] FROM database.dbo.accounts where date = ''1/20/2012''');

在您的例子中，我怀疑Server只是从远程服务器中提取整个表，然后在本地运行查询(我在过去见过多次这样的情况)。我更喜欢显式(使用OPENQUERY或在远程服务器上创建SP )，这样就不会有混淆的可能。