SQL Server Performance

Synonyms affect exeuction plan

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by BrianArmstrong, Dec 11, 2006.

  1. BrianArmstrong New Member

    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?
  2. satya Moderator

    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.
  3. BrianArmstrong New Member

    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.
  4. MohammedU New Member

    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.

Share This Page