SQL Server使用链接服务器的5个性能杀手

Love The Way You Lie 2022-08-23 05:41 203阅读 0赞

*2014-06-25* SQLSERVER走起 SQLSERVER走起

**SQLSERVER走起** ![Image 1][]

微信号 SQLSERVERZOUQI

功能介绍 介绍SQL Server的一些相关资讯，让众多SQL Server爱好者一起交流SQL Server，分享SQL Server的经验

![0_wxfrom_5][]

当使用链接服务器（Linked Servers）时，最昂贵的代价就是网络带宽间大量数据的传输。在正确的服务器书写正确的代码是非常重要的，因为每一个错误都会导致在网络带宽上付出非常昂贵的代价。 下面是使用链接服务器（Linked Servers）时的几个常见错误：

**1：使用推送方式而不是拉方式取数**

出人意料之外的是，使用链接服务器推送数据比拉取数据慢得多。Linchi Shea写了一篇很好的博客讨论这个。

Linchi Shea 使用openquery来说明两者间的差异，但是这个也会发生在使用链接服务器的SQL语句中（这里不好翻译，其实就是查询中使用Linked Server需要用到 LinkServer.DatabaseName.dbo.TableName）

**2： 使用JOIN**

跨服务器查询时，为了在两台服务器之间的数据集之间执行JOIN操作，SQL Server需要将数据从一台服务器传送到另外一台服务器。如果传送的数据是一个非常大的表，这个过程可能会非常痛苦。通常来说，数据会从远程服务器传送到本地服务器。为了防止大量数据在服务器之间大传送，你可以通过在查询条件中过滤数据，通过一个远程存储过程只取回相关数据来达到目的，万一你需要使用INNER JOIN关联两个不同服务器之间的数据集，而且本地表的数据量远小于远程服务器的那个表。你可以使用REMOTE JOIN HINT， 这样就会将数据从本地服务器将数据传送到远程服务器，从而提高性能

**3：使用UNION**

正如JOIN操作，UNIION不同服务器之间的两个数据集必定导致从远程服务器传送数据到本地服务器。即使你执行远程查询合并（UNION）同一个远程服务器的两个数据集，还是会先将两个数据集传送到本地服务器，然后UNION两个数据集，可以通过远程存储过程，函数或视图先UNION数据库来阻止这个

**4：书写太复杂的查询语句**

优化器不能总是能明白你需要做什么，尤其是你的SQL语句中使用了链接服务器（Linked Server）时，例如， 我遇到过一个类似如下SQL语句，执行了10分钟

1: SELECT *

2: FROM LocalTable

3: WHERE SomeColumn <

4: (SELECT COUNT(*)

5:  FROM RemoteServer.SomeDB.dbo.SomeTable

6:  WHERE SomeColumn > 100)

`我像这样修改了查询语句`

1: DECLARE @Count INT

2: SELECT @Count = COUNT(*)

3: FROM RemoteServer.SomeDB.dbo.SomeTable

4: WHERE SomeColumn > 100

6: SELECT *

7: FROM LocalTable

8: WHERE SomeColumn < @Count

`这样重写SQL后，查询语句只跑了一秒就查询出结果了，保持SQL脚本简单。`

**5：当数据库位于同一个实例时使用链接服务器（Linked Server）**

这种场景的性能损耗可能不像其它场景那样明显，但是这种方式比使用数据库前缀（Database.dbo.TableName)要慢

如果你想区别这两种情形，可以在测试数据库测试、对比这两种方法的性能，然后决定性能的提升是否值得在生产环境修改代码。在某些情况下，它是会提升性能的。

\---------------------------------------**自己的体会、理解**\----------------------------------------------

关于SQL SERVER的链接服务器（Linked Servers）这项功能，跨数据库/跨服务器查询时非常有用(比如分布式数据库系统中)，开发人员尤其喜欢使用它连接到远程数据源查询数据，甚至都到了滥用的地步。正所谓很多东西都具有两面性，链接服务器(Linked Servers)给跨服务器查询、分布式查询带来方便、简单化的同时，也带来了性能、安全等一系列问题。

**1：性能问题**

在复杂环境下（大数据时代更是如此），可能需要在多个不同服务器之间的数据库进行数据交互。由于数据可以无处不在，开发人员自然要编写一个查询联接尽可能多的数据可以不考虑它是本地的还是远程的。于是链接服务器的大量使用应运而生，但是链接服务器的滥用和不合理使用可能会导致数据库出现很多ASYNC\_NETWORK\_IO等待事件。另外，书写不好的SQL有可能导致严重的性能问题。

解决方法：你可以通过发布-订阅或者作业将数据集（表）数据先同步到本地服务器，然后将SQL脚本中的链接服务器去掉，这样对SQL查询性能有非常大的提升，尤其是查询比较频繁或数据量大的SQL语句。但是这样随之而来了其它问题： 同步数据的及时性（作业同步数据）、额外的精力去管理、监控数据同步（发布-订阅）。

SQL里面使用了Linked Servers导致性能低下，一方面是由于网络数据传送的延时，另外一方面则是优化器不能很好的生成最佳的执行计划. 解释:由于权限问题，使用了链接服务器（Linked Servers）的SQL导致SQL SERVER优化器不能利用远程服务器这些表的统计信息，从而不能生成最优的执行计划。如果SQL SERVER优化器可以利用到远程服务器相关表的统计信息，则链接服务器使用的账号必须拥有sysadmin、 db\_owner, db\_ddladmin这样的角色，但是很多时候处于安全考虑，创建链接服务器时使用的账号往往没有这么大的权限。在SQL SERVER 2012 SP1中这个问题已经解决了，只需要拥有SELECT权限就可以使用远程服务器相关表的统计信息。

下面这段摘自TOP 3 PERFORMANCE KILLERS FOR LINKED SERVER QUERIES

\----------------------------------------------------------------------------------------------------------------

##### 1. INSUFFICIENT PERMISSIONS #####

Without a doubt this is the number one reason for why linked server query performance suffers. Historically in order for SQL Server to take advantage of using statistics on the remote server then the login used to make the connection on the remote servers needed sufficient rights. The role needed would have been one of the following:

*  sysadmin
 *  db\_owner
 *  db\_ddladmin

If you don’t have sufficient permissions then you aren’t able to use stats, and this is killing your performance across that linked server connections. So for everyone that has been assigning the db\_datareader role to remote logins you are sacrificing performance for security. While that may be an acceptable tradeoff in your shop, I am willing to wager that most admins have no idea about this silent performance killer.

A good example of identifying these symptoms are contained in this article: http://www.sql-server-performance.com/2006/api-server-cursors/

In SQL 2012 SP1 the permissions to view the statistics on an object have been modified so that a user with SELECT permission would be able to use the stats on the remote tables. Check this link for more details in the ‘Permissions’ section towards the bottom.

\---------------------------------------------------------------------------------------------------

**2：安全问题**

滥用链接服务器会导致一个数据库实例跟N个数据库实例之间建立Linked Server，导致数据库管理、监控的变得越来越复杂，管理问题是一个，另外一个则是数据库的安全问题。这个最是头痛。

**参考资料：**

http://www.dbnewsfeed.com/2012/09/08/5-performance-killers-when-working-with-linked-servers/

http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/

[Image 1]: 
[0_wxfrom_5]: /images/20220721/d7e699b64088475b8f224c014794641f.png