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