Performance effect of Synonyms over a linked server in SQL Server

Question

On localserver (a SQL Server 2008 R2), I have a synonym called syn_view1 pointing to the linked server remoteserver.remotedb.dbo.view1

This SLOW query takes 20 seconds to run.

select e.column1, e.column2
from syn_view1 e
where e.column3 = 'xxx'
  and e.column4 = 'yyy'
order by e.column1

This FAST query takes 1 second to run.

select e.column1, e.column2
from remoteserver.remotedb.dbo.view1 e
where e.column3 = 'xxx'
  and e.column4 = 'yyy'
order by e.column1

The only difference in the two queries is really the presence of the synonym. Obviously, the synonym has an impact on the performance of the query.

The execution plan for the SLOW query is :

Plan                Cost %  Subtree cost
4 SELECT
I/O cost: 0.000000  CPU cost: 0.000000  Executes: 0  
Cost: 0.000000                  0.00    3.3521
    3 Filter
    I/O cost: 0.000000  CPU cost: 0.008800  Executes: 1  
    Cost: 0.008800              0.26    3.3521
        2 Compute Scalar
        I/O cost: 0.000000  CPU cost: 3.343333  Executes: 1  
        Cost: 0.000000          0.00    3.3433
            1 Remote Query
            I/O cost: 0.000000  CPU cost: 3.343333  Executes: 1  
            Cost: 3.343333      99.74   3.3433

And for the FAST query:

Plan            Cost %  Subtree cost
3 SELECT
I/O cost: 0.000000  CPU cost: 0.000000  Executes: 0  
Cost: 0.000000              0.00    0.1974
    2 Compute Scalar
    I/O cost: 0.000000  CPU cost: 0.197447  Executes: 1  
    Cost: 0.000000          0.00    0.1974
        1 Remote Query
        I/O cost: 0.000000  CPU cost: 0.197447  Executes: 1  
        Cost: 0.197447      100.00  0.1974

My understanding is that in the SLOW query, the server fetches all the data from the remote server, then applies the filter (though without index) whereas in the FAST query the server fetches the filtered data from the remote server, thus using the remote indexes.

Is there any way to use the synonym while being fast? Maybe a setup of the linked server ? the local database server?

Thanks for the help!

Answer 1

I would dump the data without the order by into a temp table on local server. Then I would select from the temp table with the order by. Order by is almost always the killer.

Answer 2

The accepted answer for this post on dba.stackexchange.com notes that performance gotcha's may occur in queries over linked servers due to limited access rights on the linked server, restricting the visibility of the table statistics to the local server. This can affect query plan, and thus performance.

Excerpt:

And this is why I got different results. When running as sysadmin I got the full distribution statistics which indicated that there are no rows with order ID > 20000, and the estimate was one row. (Recall that the optimizer never assumes zero rows from statistics.) But when running as the plain user, DBCC SHOW_STATISTICS failed with a permission error. This error was not propagated, but instead the optimizer accepted that there were no statistics and used default assumptions. Since it did get cardinality information, it learnt that the remote table has 830 rows, whence the estimate of 249 rows.