Sql-server – Slow SQL Server query using a DB2 linked server in virtual environment

Several possible reasons for this.

Lack of data distribution statistics. It looks like you have this one covered in your post, but i'll leave it for completeness.

If the linked server login does not have sufficient privileges to obtain all useful statistics on CRM, a suboptimal plan will be used. *If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.* http://msdn.microsoft.com/en-us/library/ms175537%28v=sql.105%29.aspx

Mike Walsh also has a writeup on this: http://www.straightpathsql.com/archives/2010/07/linked-server-query-running-slow/

Query is too complex to perform well across the linked servers.

1. Replication: Replicate the minimum necessary columns from one server to the other. Use the local replicated data for joins.
2. Use temp tables: Insert the minimum necessary columns from one server to a table on the other. Use the local data for joins.

For both of these scenarios, all data is on the same machine. This enables the optimizer to have complete data distribution statistics. You can even index columns for further optimization. Always identify the minimum columns required to complete the necessary tasks. Sometimes it's sufficient just to replicate or insert 1 or 2 columns.

OPENQUERY is also an option, but I prefer all of the data on the same machine when possible.

If the cross server joins are INNER JOINS, you might also benefit from the use of the REMOTE hint. I've seen this work very well when the remote table has significantly more records than the local table.

The performance problem actually had to do with the LEFT OUTER JOIN tables. If I changed them to INNER JOIN, or if I excluded their data from the SELECT columns, the query ran fine.

What I ended up doing was creating a View on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds table.

I didn't think this would work since I thought joining everything and pulling it down to the second server before filtering was the same as what I was doing now, and would lead to the same performance problem, but surprisingly that doesn't appear to be the case.

CREATE VIEW MyView 
AS

SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id

GO

and

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

All the joined columns were correctly indexed, however according to this answer

Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

And this one

Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

so I am guessing that the query plan used for the query was not using the Indices defined, and SQL Server was generating a poor query plan for the LEFT OUTER JOIN tables.