0

I have 2 databases that reside on the same server. I am trying to read static data from one database in another database using synonym. And this caused no issues since we are not even using link server or anything. But DBA seems to think this will cause huge performance issue since synonyms use tempdb to create object reference to the other database. I am not sure how this works? Can someone explain if there is really a serious impact and if really tempdb is used? I thought synonym just creates an object that refers to the database object directly with no tempdb involved since they are both on same server.

migrated from stackoverflow.com Feb 4 '17 at 11:15

This question came from our site for professional and enthusiast programmers.

  • 5
    There should be no performance impact since a synonym is just like a variable pointing to the destination. The same execution plan is generated. If you use sql-server 2005, there was a bug which is already fixed. Related: stackoverflow.com/questions/796791/… – Tim Schmelter Feb 3 '17 at 15:46
  • 4
    Where did the DBA "learn" that synonyms cause anything to be created in tempdb? This is simply not true, and you should correct their knowledge. This is true whether the synonym points to the same database, a different database, or a database on a different server. Tempdb may be used for certain types of queries but this would happen with or without synonyms. – Aaron Bertrand Feb 4 '17 at 12:33
  • Beware that Imperative Programming has quite different rules and behavior than Declarative Programming. The answer above is clearly given by experts in Imperative Programming and they should be very, very, very cautious before extending their knowledge to Declarative Programming. These being said I'll go forward looking for a better answer. – Cristinel Boboc Dec 17 '18 at 8:49
3

Synonyms will not cause the use of tempdb. There, however, may be a misunderstanding by your DBA as to the location of the database behind the synonym.

Queries across databases can use a 3 part name to reference a database object: database.schema.objectname. Queries across linked servers require an additional part, the linked server name. In my experience, because of this, almost all references to external servers use a synonym. It saves a bunch of typing (note: that is not the reason to use synonyms). However, it is a lot more common to see not use a synonym where referencing a local database.

I speculate this may have lead the DBA to assume that, because you are using a synonym, you are referencing an external server.

Results of queries against an external server do impact tempdb, as this is where it stores the data. So, if the DBA misunderstood the actual relation of the two database, being on the same server, they could have given you incorrect information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy