Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Synonyms and performance Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 2:05 PM
Points: 308, Visits: 55
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.

Do synonyms effect SARGability in any way?



Bob
SuccessWare Software
Post #1427405
Posted Thursday, March 7, 2013 12:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
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
Post #1427803
Posted Thursday, March 7, 2013 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 2:05 PM
Points: 308, Visits: 55
Thanks! That clears it up for me.


Bob
SuccessWare Software
Post #1427952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse