SQL Server 5 performance killers using linked servers

When using a linked server (linked Servers), the most expensive price is the transfer of large amounts of data between network bandwidth. It is important to write the right code on the right server, because each error can result in a very expensive cost to network bandwidth. The following are some common errors when using a linked server (linked Servers):

1: Use the push method instead of pulling the number

Surprisingly, the use of linked server push data to fetch data is much slower. Linchi Shea wrote a very good blog to discuss this.

Linchi Shea uses OpenQuery to illustrate the difference between the two, but this will also happen in SQL statements that use linked servers (this is not a good translation, but it is the use of linked server in queries that require LinkServer.DatabaseName.dbo.TableName)

2: Use Join

When querying across servers, SQL Server needs to transfer data from one server to another in order to perform a join operation between the datasets between the two servers. If the transmitted data is a very large table, the 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 do this by filtering the data in the query criteria and retrieving the relevant data through a remote stored procedure, in case you need to use the inner join to correlate data sets between two different servers, and the amount of data on this surface is much smaller than the table on the remote server. You can use remote JOIN HINT, which will improve performance by transferring data from the local server to the remote server

3: Use Union

As with the join operation, uniion two datasets between different servers must result in the transfer of data from the remote server to the local server. Even if you perform a remote query merge (UNION) of two datasets on the same remote server, you will first transfer two datasets to the local server, then union two datasets, and you can block this by using a remote stored procedure, function, or view to 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: (SELECT COUNT (*)

5:from RemoteServer.SomeDB.dbo.SomeTable

6:where somecolumn > 100)

I modified the query like this

1:declare @Count INT

2:select @Count = Count (*)

3:from RemoteServer.SomeDB.dbo.SomeTable

4:where somecolumn > 100

5:

6:select *

7:from localtable

8:where Somecolumn < @Count

After you rewrite SQL, the query statement only runs 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 approach is slower than using the database prefix (Database.dbo.TableName)

If you want to distinguish between these two scenarios, you can test the database, compare the performance of both methods, and then determine whether performance improvements are worth modifying the code in the production environment. In some cases, it can improve performance.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

The feature of SQL Server's linked server (linked Servers) is useful across database/cross server queries (such as in distributed Database systems), which developers particularly like to use to connect to remote data source query data, even to the point of abuse. Is the so-called many things have two sides, linked server (linked Servers) to cross-server query, distributed query to bring convenience, simplicity, but also brought a series of problems such as performance, security.

1: Performance issues

In a complex environment (and more so in the larger data age), data interaction may be required across databases between multiple different servers. Since data can be ubiquitous, developers naturally have to write a query to join as many data as possible regardless of whether it is local or remote. As a result, the extensive use of linked servers came into being, but the misuse and unreasonable use of linked servers 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 synchronize the DataSet (table) data to the local server by posting-subscribe or job, and then remove the linked server from the SQL script, which will greatly improve the performance of SQL queries, especially if the query is more frequent or the data volume is large SQL statements. But then there are other problems: the timeliness of synchronizing data (Job synchronization data), the extra effort to manage, and monitoring data Synchronization (publish-subscribe).

The use of linked servers in SQL results in low performance, on the one hand due to the delay of network data transmission, on the other hand, the optimizer is not good at generating the best execution plan. Explanation: SQL Server optimizer cannot take advantage of the statistics of these tables on the remote server because of permissions issues, using a linked server (linked Servers), which does not generate an optimal execution plan. If the SQL Server optimizer can take advantage of statistics on the remote server-related tables, the accounts used by the linked server must have roles such as sysadmin, db_owner, db_ddladmin, but often in security considerations, When you create a linked server, the account you use often does not have such a large number of permissions. This problem has been resolved in SQL Server SP1, where you can use statistics for remote server related tables only with SELECT permissions.

The following section is excerpted from top 3 performance killers for linked SERVER QUERIES

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

Without a doubt this are the number one reason for why linked server query performance. 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 have been one of the following:

Sysadmin

db_owner

db_ddladmin

If you don ' t have sufficient permissions then your aren ' t able to-use stats, and this is killing your-performance th At linked server connections. So, for everyone, has been assigning the db_datareader role to remote logins you are sacrificing performance for Securi Ty. While it is acceptable tradeoff in your, I am willing to wager that most admins have no idea about this Sile NT Performance Killer.

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

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

2: Security Issues

Misuse of a linked server can result in the establishment of a linked server between a database instance and N database instances, resulting in more complex database management and monitoring, a management problem and a database security problem. This is the most headache.

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