Slow performance when using a synonym that is configured to use a linked server

Question

Situation:
I’m not sure if I should be embarrassed that we did this, or if we should be applauded. Anyways, we have a shell of a database that has synonyms pointing to another server via a linked server, and then views named identical to the tables that used to be in the database that utilize the synonyms. We did this because of server migrations and reorganization of dev teams. Anyways, the different teams had baked in code that references this particular database, and we couldn’t get them to change their code. My team was told that the effort was a huge undertaking, and they didn’t have time to do it. So, we came up with this plan for better or worse, and ended up placing this shell database on each of their SQL Servers. We can’t replicate the database because the vendor of the application doesn’t believe in primary keys (or at least that’s what we tell ourselves…. yeah ponder on that one for a second, and it will make your brain hurt even more when I tell you they are a large billion software dollar company).

Question If we do a select against the view (select top 100 * from viewname) the data results come back in about 3-4 seconds. Remember, the view is using a synonym that uses a linked server reference. If we do a select against the linked server (select top 100 * from ls.db.schema.object) the data results come back sub-second. The full path of the linked server reference is the same for the synonym above. Why the difference In times? Is it because engine has to take an extra moment to build the query plan? The dev teams are starting to get restless because their apps are now running slow.

If I’ve completely confused you I’m sorry. I did the best I could to describe the environment.

Query plan using the synonym: brentozar.com/pastetheplan/?id=Sy9b0yZJM

Query plan using the linked server: brentozar.com/pastetheplan/?id=r17oAy-kG

Answer 1

Fact is both are same.

CREATE SYNONYM [Testsyn]
FOR [RemoteServer].[DBName].[dbo]
GO

SELECT *
FROM [Testsyn].EmpTable
GO

SELECT *
FROM [RemoteServer].[DBName].[dbo].EmpTable
GO

Optimizer just decode synonym to actual object,there after both will have identical query plan."[Testsyn]" will be converted to "[RemoteServer].[DBName].[dbo]"

Poor performance for synonym could be :

i) Because permission issue to synonym on remote server.You should check with DBA of both team.

Answer 2

I had the same problem and I was able to solve it by updating the SQL Server version to 2019 on both servers and on the remote server I changed the collation to Latin1_General_CI_AS, it is important to change the collation of the instance, without this it does not solve.

My databases were already using Latin1_General_CI_AS.

I hope it helped you.