patternMinor
Is Synonyms to tables cost effective within SQL Server 2016
Viewed 0 times
tablessynonymssqlwithinservercosteffective2016
Problem
For one of our new projects we are thinking of using lots of Synonyms to tables which refer to another Database and we are fully aware on what it does and how we are going to use them. But what we don't know is, are there any hidden overheads on overall SQL server performance? If not at all.
Solution
According to this post -
As a synonym is an abstraction/alternative name for an already
existing database object, in the case of a table, index behaviour is
identical to that of the underlying object i.e. when execution plans
are generated, the same plan is generated irrespective of using the
table name or corresponsing synonym.
You can verify the execution plans when using both the regular object and the synonym to see if they generate the same plan.
This post has a similar question:
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.
The answer given was
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 see 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.
I'm sure Paul White (or another expert will provide a definitive response).
As a synonym is an abstraction/alternative name for an already
existing database object, in the case of a table, index behaviour is
identical to that of the underlying object i.e. when execution plans
are generated, the same plan is generated irrespective of using the
table name or corresponsing synonym.
You can verify the execution plans when using both the regular object and the synonym to see if they generate the same plan.
This post has a similar question:
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.
The answer given was
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 see 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.
I'm sure Paul White (or another expert will provide a definitive response).
Context
StackExchange Database Administrators Q#194364, answer score: 4
Revisions (0)
No revisions yet.