• SW21_Bob (3/6/2013)


    How does the use of synonyms effect performance?

    More specifically, are synonyms replaced with fully qualified names in execution plans? It would seem to me that any synonym has to be resolved to the actual object so any query or procedure that doesn't have a cached plan is going to have an extra step in mapping the synonym to an actual object. I'm thinking there would be a very small performance hit in this. Perhaps a slightly larger hit when the synonym references an object on a different database and slightly larger still when on a different server.

    They are swapped out in the Bind phase of query execution same as a view would be expanded. This happens before the Optimization phase where the execution plan is generated so while you'll still se a reference to the synonym in the query text you will only see references to the objects the synonyms point to in the execution plan's operators.

    Any performance hit, if you want to even call it that, that might be associated with using synonym is not at all worth being concerned about.

    Do synonyms effect SARGability in any way?

    Not at all. SARGs are what you'll find in a predicate of a JOIN, WHERE clause or HAVING clause and synonyms do not participate in a query in those spots. They are simply substitutes for object names.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato