4

I have a requirement for some reporting and different environments, and the SQL Synonym would make a lot of sense with making our solution work.

http://technet.microsoft.com/en-us/library/ms177544.aspx

However, the operational support department doesn't like the idea, due to 'performace implications'. I can't see why there would be any performance degradation. But, has anyone had experience with SYNONYMs and medium sized databases (100gb) and performance issues?

Should I be worried, or have Microsoft covered this, and whether you use a SYNONYM, or the fully qualified [Database].[schema].[tablename] in a query - is there any difference - performance wise?

Craig
  • 18,074
  • 38
  • 147
  • 248
  • 1
    Your operational support department is confused. A synonym is just an alias (or you could say a pointer); not at all sure what performance implication there could possibly be, except that maybe it's transparent to devs that this two-part synonym actually references a table over a linked server connected by can and string... Or if you use explicit index hints (!) *and* are on an ancient unpatched version of SQL Server. – Aaron Bertrand Feb 14 '14 at 05:34
  • duplicate of [SQL Server - Synonyms Tips & Tricks?](https://stackoverflow.com/questions/796791/sql-server-synonyms-tips-tricks) – underscore_d Dec 27 '17 at 14:41

2 Answers2

2

Here is a good SO thread on synonyms. Performance shouldn't be an issue on same server. I'm not sure cross server.

https://stackoverflow.com/questions/796791/sql-server-synonyms-tips-tricks

Dave C
  • 7,272
  • 1
  • 19
  • 30
1

Anytime you use linked servers or cross-database queries you introduce potential and well documented performance issues. For example, the query typically becomes a distributed query. Also, the Optimizer has limited or no access to remote statistics so the query plan is unlikely to be 'optimal'. Using a synonym just hides the complexity under an alias that usually looks like a local reference and tempts developers to do things across the linked server that they should not such as nested sub-queries and complex, multi-table joins.

That said, I am seriously considering synonyms but I will emphasize their special nature by a naming convention (e.g. syn_theTable) and coding rules :)

Ray
  • 11
  • 1