链接的服务器查询缓慢，统计信息需要很长时间才能返回
我们在SQL Server 2016上有一个1.5TB群集的列店表，900个分区。
我们在2012盒子上创建了一个链接服务器，指向该服务器。
从servera.databasea.dbo.tablea中进行简单的“选择fielda，fieldb，fieldc，其中partition_field = b and otherfield = c”需要63秒。
我在两者上都有sysadmin，所以我有权获得统计数据，但是当我运行Query sp_whoisactive时，它正在运行dbcc show_statistics（@qtbl，@statname），stat_header加入dense_vector可以看到）正好在更改查询之前60秒钟，此时它将在3秒内完成。
因此，每个链接的服务器查询至少需要60秒。我已经尝试使用SQL Server的“ SQL Server”和Microsoft Ole提供商构建链接服务器，并且都做同样的事情。有什么方法可以解决此行为？是的，我们将在接下来的几个月中搬出2012年，但是在此之前我们有一些紧迫的数据需求。我们的后备是复制所需的数据范围，但这可能会变得丑陋。
链接服务器的原因（与OpenQuery相反）是要最大程度地减少需要进行的代码更改的数量 - 如果我们只能将视图指向该表，则无需更改其他代码。
Collation兼容设置为True，数据访问将设置为true，RPC＆amp; RPC OUT设置为true。
谢谢。
We have a 1.5tb clustered columnstore table, 900 partitions, on SQL Server 2016.
We created a linked server on a 2012 box, pointing at this server.
Doing a simple "select fielda, fieldb, fieldc from servera.databasea.dbo.tablea where partition_field = b and otherfield = c" takes 63 seconds.
I have sysadmin on both so I have the right to stats, but when I run a query sp_whoisactive shows that it's running dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector for (as far as we can see) exactly 60 seconds before it changes to doing a query, at which point it finishes in 3 seconds.
So every linked server query takes at least 60 seconds. I've tried building the linked server using "SQL Server" and the Microsoft OLE Provider for SQL Server, and both do the same thing. Is there any way to fix this behavior? Yes, we're moving off of 2012 in the next couple of months, but we have some pressing data needs before then. Our fallback is to copy the data ranges we need, but that could get ugly.
The reason for the linked server (as opposed to openquery) is to minimize the amount of code changes we need to make - if we can just point a view at that table, then no other code needs to be changed.
Collation Compatible is set to true, Data Access set to true, RPC & RPC Out are set to true.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以简单地将OpenQuery版本放入视图中。
OpenQuery（）实质上是在做您要做的事情，如果您：servername.databasename.sys.ssys.sp_executesql @sql @sql。 （我知道对您来说不是可行的选择）我不相信您会看到DBCC命令完全运行。
You can simply put the OPENQUERY version inside a view.
OPENQUERY()is essentially doing what you'd be doing if you:servername.databasename.sys.sp_executesql @sqlIn those cases (which I know aren't viable options for you) I don't believe you'd be seeing the DBCC command running at all.