SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Optimizing Linked Server Queries

Optimizing Linked Server Queries

Steve Jones
Steve Jones
SSC Guru
SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)

Group: Administrators
Points: 368301 Visits: 20274
Perhaps a few things, but if someone could show some real LinkedServer queries and then a few ways to make them run faster.

Potentially you have queries that move data to the linked server, maybe join with data from a linked server.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37813 Visits: 13325
I have had some very unpleasant experience with linked server queries recently. Unfortunately, I have not discovered any silver bullet.

When troubleshooting linked server performance, always study the execution plans in detail. Look at the operators that do the remote work. Open their properties and study the query that is pushed to the other server. (Yes, it will be an awful task to understand because all column names are aliased with meaningless numbers and the generated query looks terrible, but you'll still have to).

In a normal query, the local execution plan is the only possible source of issues. In a remote query, there are three potential sources of misery:
1. The local execution plan - this is the part that most people that already have some tuning experience will get fairly easily by studying the execution plan
2. The remote execution plan(s) - you do not see this, so you will have to look at the properties of remote operators, find the query, copy/paste it into a query window that connects to the remote server and then look at its execution plan
3. Network overload. I have seen execution plans where all rows of a one-million-row table are pushed over the network, and then a filter throws away all except the one it needs. More on that below.

You will have to look at each of these in isolation and combined. E.g., a remote query that takes 0.2 seconds to execute on the remote server is not a problem if it is executed once, but if it is sitting in the inner input of a nested loops join and the actual execution count shows that it was invoked a miillion times, then you have just found an explanation for one million * 0.2 seconds = 55.5 hours execution time.

re: point 3 - the case where I found a full table being pushed over the network to retrieve a single row was caused by collation differences. Because the remote server had a different collation than the local server, the optimizer decided that it could not trust the remote server to filter on a string column - so instead of sending the query with the WHERE condition to the other server (where an index seek would have been used), it decided to pull all rows over the network and filter locally (without the benefit of an index, because only the data is transported over the network).
I was able to fix this for a SELECT query by fiddling with the linked server properties. For an UPDATE statement, though, I did not find the properties required to change this; in the end I had to ask the developers to revise that particular bit of logic. (Which they did by changing SET AuditColumn = USER_NAME() to SET AuditColumn = 'xxx' - because they knew that this logic would only ever be executed in the security context of xxx.)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum