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

linked-serversql server

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

Best Answer

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.

Related Question