4

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.

5
  • Can you give a little more insight into how you plan to use the synonyms? We have a large implementation of them in our production DW environment, but depending on how you plan to use them I might be able to provide some real world experience as to what we've seen.
    – H.79
    Commented Jan 2, 2018 at 19:00
  • Basically we are developing a front end real time solution for our clients and this tool will sit on top of this particular database, now this particular database doesn't have all the tables to support our front end requirements, hence we are thinking of using synonyms to all the missing tables, which exist in another database and we want avoid any duplication of data and want our front end to add / amend records as well. Commented Jan 3, 2018 at 13:28
  • Synonyms are objects within SQL Server, so there is some tiny overhead in terms of larger internal system tables that SQL Server will need to maintain, keep in memory, etc. For any reasonable # of synonyms, no big deal. But just in case "lots of synonyms" could mean hundreds of thousands or more, this question might be relevant: dba.stackexchange.com/q/135201/11635 Commented Jan 3, 2018 at 19:35
  • @PallavHathi - Got it. I will tell you this. If you plan to query the synonyms directly you will be fine. However, if you plan to build views using the synonyms you will run into performance issues, especially if you where clauses in your views. We made this mistake, if you want to call it a mistake, but we've run into a performance hit if the source of the synonym is a large data set (million records or higher) and we are making the call via a view. Hard to explain in a small comment.
    – H.79
    Commented Jan 4, 2018 at 19:54
  • Thanks everyone for your input. We have implemented synonyms as per planned and will share my experiences here. Commented Jan 8, 2018 at 17:40

2 Answers 2

4

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).

0

When you say "another database", do you mean within the same instance, i.e. database2.schema.tablename? If so, you should be fine; overhead is negligible. Watch out for cross-database permissions issues.

Or do you mean another instance, i.e. instance2.database2.schema.tablename? If so, this can be terribly bad, particular for multiple joins of four part named objects/synonyms. See https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/ for a little more detail, but it boils down to use OPENQUERY or similar (EXECUTE AT works find too) to ship entire SQL statements over.

1
  • It's within same instance. Commented Jan 8, 2018 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.