Synonyms affect exeuction plan | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Synonyms affect exeuction plan

I have a situation where replacing a full server name with a synonym in an update statement causes a HUGE performance hit. Say I have a synonym: symTbl => PRODSERVER1.MYDB.DBO.TBL And these two queries: UPDATE symTbl SET intField = 1 WHERE charField = ‘Y’
UPDATE PRODSERVER1.MYDB.DBO.TBL SET intField = 1 WHERE charField = ‘Y’ The first query runs MUCH, MUCH slower than the second. If you check the execution plan, you will see that the first query (using synonyms) issues a Remote Query that pulls the entire remote table over the network. The second query (using full server name) avoids this step entirely. Is there any way I can rewrite my query to use synonyms but get the performance of using full server names? Or is this just a limitation of the way synonyms are implemented?
How about indexes on that table? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
In my testing, even if the WHERE clause hits the clustered index, the synonym performance is still very poor compared to using the 4-part name.
I don’t think there anything you can change by using synonym but you try creating a view using PRODSERVER1.MYDB.DBO.TBL table. Mohammed U.