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
http://www.SQL-Server-Performance.Com
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.

---

Interesting…
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.

---

]]>