[Translate]--SQL server 5 performance killers using linked servers

Foreword: This article is to blog http://www.dbnewsfeed.com/2012/09/08/5-performance-killers-when-working-with-linked-servers/ Translation, if the translation is not good or bad place, please point out, we study progress together. Respect the original and translated labor results, please indicate the source when reproduced. Thank you!

When using a linked server (Linked Servers), the most expensive cost is the transmission of large amounts of data between network bandwidth. It is important to write the correct code on the correct server, because each error can cause a very expensive cost to the network bandwidth. Here are a few common errors when using a linked server (Linked Servers):

1: Use push method instead of pull method to fetch number

Surprisingly, pushing data using a linked server is much slower than fetching data. Linchi Shea wrote a very good blog to discuss this.

Linchi Shea uses OpenQuery to illustrate the difference between the two, but this also happens in the SQL statement using the linked server (this is not a good translation, in fact, the use of linked server in the query needs to use LinkServer.DatabaseName.dbo.TableName)

2: Using Join

When you cross-server queries, SQL Server needs to transfer data from one server to another in order to perform a join operation between the data sets between the two servers. If the transmitted data is a very large table, this process can be very painful. Typically, data is transferred from the remote server to the local server. To prevent large amounts of data from being transferred between servers, you can use a remote stored procedure to retrieve only the relevant data by filtering the data in the query condition, in case you need to associate a data set between two different servers using the inner join, and the data volume on the local surface is much smaller than that of the remote server. You can use remote JOIN HINT to improve performance by transferring data from the local server to the remote server

3: Using union

As with join operations, uniion two datasets between different servers must cause data to be transferred from a remote server to the local server. Even if you perform a remote query merge (UNION) with two datasets from the same remote server, the two datasets will be delivered to the local server first, and then the Union two datasets can be stopped by a remote stored procedure, function or view, by first union the database.

4: Writing too complex query statements

The optimizer cannot always understand what you need to do, especially if you use a linked server (Linked server) in your SQL statement, for example, I have encountered a SQL statement similar to the following, executed for 10 minutes

   1:SELECT *

   2:from localtable

   3:WHERE Somecolumn <

   4: (SELECTCOUNT(*)

   5:   from RemoteServer.SomeDB.dbo.SomeTable

   6:  WHERE Somecolumn > 100)

我像这样修改了查询语句

   1:DECLARE @CountINT

   2:SELECT @countCount(*)

   3:from RemoteServer.SomeDB.dbo.SomeTable

   4:WHERE somecolumn > 100

   5:  

   6:SELECT *

   7:from localtable

   8:WHERE Somecolumn < @Count




After the SQL is rewritten, the query statement runs for only one second to query the results, keeping the SQL script simple.

5: Use a linked server when the database is in the same instance (Linked server)

The performance loss of this scenario may not be as obvious as in other scenarios, but this is slower than using the database prefix (Database.dbo.TableName)

If you want to differentiate between these two scenarios, you can test the performance of both methods in the test database, and then decide whether the performance boost is worth modifying the code in the production environment. In some cases, it will improve performance.

--------------------------------------- own experience, understanding ----------------------------------------------

About SQL Server's linked server (Linked Servers), which is useful for cross-database/cross-server queries (such as in a distributed database system), developers especially like to use it to connect to remote data sources to query data, even to the point of abuse. So-called many things have two sides, the link server (Linked Servers) to cross-server query, distributed query brings convenience, simplification, but also brings a series of performance, security and other issues.

1: Performance issues

In a complex environment (especially in the big Data age), it may be necessary to have data interactions between multiple databases in different servers. Since data can be ubiquitous, developers naturally want to write a query to join as much data as possible regardless of whether it is local or remote. As a result, the use of the linked server has come into being, but the misuse and unreasonable use of the linked server may result in many async_network_io waiting events in the database. In addition, poorly written SQL can cause serious performance problems.

WORKAROUND: You can use a publish-subscribe or job to synchronize the data set (table) data to the local server and then remove the linked server from the SQL script, which greatly improves the performance of SQL queries, especially SQL statements that query more frequently or with large amounts of data. But then there are other problems: the timeliness of synchronizing data (Job synchronization data), additional effort to manage, and monitoring of data synchronization (publish-subscribe).

SQL inside uses the linked servers causes the performance to be low, on the one hand is because the network data transmits the delay, on the other hand is the optimizer cannot produce the best execution plan very well. Explanation: Due to permissions issues, SQL using a linked server (Linked Servers) caused the SQL Server optimizer to not take advantage of the statistics of these tables on the remote server, thus generating an optimal execution plan. If the SQL Server optimizer can take advantage of the statistics on the remote server-related tables, the account used by the linked server must have a role such as sysadmin, db_owner, db_ddladmin, but in many cases security considerations, The account used to create the linked server often does not have such a large permission. In SQL Server SP1, this issue has been resolved, and only the SELECT permission is required to use the statistics of the remote server-related tables.

The following excerpt from top 3 performance killers for LINKED SERVER QUERIES

--------------------------------------------------------------------------------------------------------------- -1. Insufficient PERMISSIONS

Without a doubt 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 MAK E The connection on the remote servers needed sufficient rights. The role needed would has 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 the This is killing your performance across th At linked server connections. So for everyone, have been assigning the db_datareader role to remote logins you is sacrificing performance for Securi Ty. While the May is a acceptable tradeoff in your shop, I am willing to wager the most admins has no idea about this Sile NT Performance Killer.

A Good example of identifying these symptoms is contained in this ARTICLE:HTTP://WWW.SQL-SERVER-PERFORMANCE.COM/2006/API -server-cursors/

In SQL SP1 the permissions to view the statistics on an object has been modified so this a user with SELECT Permissi On would is able to use the stats on the remote tables. Check this link for more details on the ' Permissions ' section towards the bottom.

---------------------------------------------------------------------------------------------------

2: Security Issues

Abusing a linked server causes a linked server to be established between a DB instance and N db instances, resulting in more complex database management and monitoring, one for management, and one for database security issues. This is the most headache.

Resources:

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/