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!

@AaronBertrand, what would it change? Would the indexes be used? Would I be able to avoid specifying the whole fully qualified name of the linked server inside my stored procedure? — François-Xavier, Mar 11 '13 at 06:24
Yes and yes. And you could create a synonym for the stored procedure name instead of the view. — Aaron Bertrand, Mar 11 '13 at 11:00
I agree with Aaron that using a synonymed stored procedure would prevent SQL server from attempting to split the query into its component steps and executing differently - however I am puzzled as to why SQL Server would product the effect you are seeing. AFAIK a synonym is just a naming syntax sugar - it doesnt affect execution plans - but you've proved my assumption is wrong. I'll add a bounty to see if anyone can explain this. — PhillipH, Jul 24 '14 at 08:30
@AaronBertrand, I understand your suggestion to move to remote procedure but I'm still a bit unclear on why the OP's two queries netted different results. Did optimizer just get confused and resorted to its common denominator of "pull everything"? — sam yi, Dec 29 '14 at 17:25

score 2 · Answer 1 · answered Jul 24 '14 at 19:26

2

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.

answered Jul 24 '14 at 19:26

Dbloch

2,326
1
13
15

score 1 · Answer 2 · edited May 09 '18 at 12:53

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.

I can see how performance of a query onto a linked server could sufffer from lots of issues, but why the difference between the synonym query and the linked table; ultimately the synonym gives the same connection, unless you are suggesting there might be a different access right set on the synonym itself ? — PhillipH, Jul 24 '14 at 08:50
@PhillipH: Clearly *something* is different for the synonym; this explanation seems sufficient to cause the observed performance degradation, so is worth checking on the remote server. — Pieter Geerkens, Jul 24 '14 at 09:29

Performance effect of Synonyms over a linked server in SQL Server

2 Answers2