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

  • Have you tried refreshing the view using sp_refreshview viewname ? – Kin Shah Nov 8 '17 at 19:57
  • 1
    Also, see if For Linked Server helps. – Kin Shah Nov 8 '17 at 20:07
  • Kin, I've all ready refreshed the views. – H.79 Nov 8 '17 at 20:29
  • Kin, we can't use openquery. We aren't allowing the devs to do that. – H.79 Nov 8 '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 8 '17 at 21:29
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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.