0

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

Grant Miller
  • 163
  • 2
  • 3
  • 12
H.79
  • 313
  • 2
  • 10
  • Have you tried refreshing the view using sp_refreshview viewname ? – Kin Shah Nov 08 '17 at 19:57
  • 1
    Also, see if For Linked Server helps. – Kin Shah Nov 08 '17 at 20:07
  • Kin, I've all ready refreshed the views. – H.79 Nov 08 '17 at 20:29
  • Kin, we can't use openquery. We aren't allowing the devs to do that. – H.79 Nov 08 '17 at 20:34
  • 1
    @H.79, the plans show one is using a variable and the other is using a literal value. If the variable is a local variable and not an input parameter, it's going to use the density vector, whereas the literal value will use the statistics histogram. Do the same test on both. – Tara Kizer Nov 08 '17 at 21:29
  • Note that, if OPENQUERY is not allowed, and the query runs slower because it is run through a linked server, the solution may be for the devs to bite the bullet and update the code to refer to the actual database, instead of going through the linked server. That said, it's not clear if the original issue has now expanded to include the need for code run on several different servers to all pretend the same remote database exists locally, in which case losing the linked server part isn't an option, and changing the references won't help. – RDFozz Nov 08 '17 at 22:14
  • @TaraKizer - Thank you for helping me understand what is going on! :) I will be going back to the devs to tell them that they are going to have to crack open their code if they want to improve performance. – H.79 Nov 08 '17 at 22:43
  • @RDFozz - Agreed! Thanks for the advice.Next time I will edit my post, and put the URL of the execution plans there. This situation is not an ideal one, and I wasn't 100% sure what was going to happen when we made the cut over to the new server(s). All internally built applications and reporting databases were silo'ed onto their respective "data mart" servers to alleviate stress on our OLTP box. However, with the move both development and reporting still rely on this one database, and because it has no primary keys we can't replicate it. – H.79 Nov 08 '17 at 22:54

2 Answers2

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.

KumarHarsh
  • 1,648
  • 11
  • 10
  • The linked server configurations are using a particular account that is granted db_datareader to all the databases on the remote server. I'm the DBA supporting my dev team's efforts for this project. I was able to improve performance of the queries by replacing literals with an input parameters recommended by @TaraKizer. However, if we call the synonym performance is sub second, but if we call the view using the synonym it goes from sub second to 7-10 seconds. stumped...... – H.79 Nov 17 '17 at 19:35
0

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.

Nadine Anderle
  • 1
  • 1