none
Disadvantage of self-referencing Linked Server RRS feed

  • Question

  • We have consolidated our env, however quite a few sp's & other code are still using 4 part naming convention and accessing the linked server which is self-refencing now (points back to itself), I have been looking online to see if there is a performance impact of it and haven't found much other than a few CPU cycles which are used for it, and was wondering if there is a doc/ info out there which entails the disadvantages so that I can show it to the app so that they can fix the code faster, imo we should be doing it as it is to keep the env simple and because the few cpu cycles can add up , Are there any other issues of it? Thanks.


    D

    Wednesday, July 29, 2020 10:08 PM

Answers

  • So is it 'distributed queries' even if its the same server? 

    Well, it depends.

    Let's say that you are on the server MYSERVER, and you say

    SELECT * FROM MYSERVER.db.dbo.btl

    SQL Server will recognise that you are referring to the local server, and handle this as

    SELECT * FROM db.dbo.btl

    However, I took it that you had done somthing like this:

    EXEC sp_addlinkedserver 'MYLINKEDSERVER', '', 'SQLNCLI', @datasrc=@@servername

    This sets up a linked server that is a loopback, and SQL Server does not realise that this is a loopback, but handles it as any other linked server. This can be great for demos and quick testing where you don't really want to involve a second instance. But since I get all the overhead of distributed queries, I would not use this in production as a long-term solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by SQLRocker Sunday, August 2, 2020 3:55 AM
    Thursday, July 30, 2020 9:06 PM

All replies

  • Hi SQLRocker,

    Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network. Loopback linked servers are intended for testing and are not supported for many operations, such as distributed transactions.More details please check the Linked Servers (Database Engine).

    Also some interesting posts:
    Loopback Linked Server

    SQL Server How to add a linked server to the same instance without performance impact

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 30, 2020 7:23 AM
  • So what you have now is not likely to be that much worse than you had before. But your consolidation is not going to pay off if you keep the four-part notation.

    Linked servers often mean hassle, and this includes loopback servers. Optimization of distributed queries is always a challenge for the optimizer, and you are more prone to get plans with poor performance.

    If someone feels that they want to keep the four-part notation just in case you would revert on the consolidation, this is a case for introducing synonyms. (Although the synonyms have to be per object, not per server/database.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, July 30, 2020 9:10 AM
  • Thanks for replying Erland.

    So is it 'distributed queries' even if its the same server? 


    D

    Thursday, July 30, 2020 2:48 PM
  • So is it 'distributed queries' even if its the same server? 

    Well, it depends.

    Let's say that you are on the server MYSERVER, and you say

    SELECT * FROM MYSERVER.db.dbo.btl

    SQL Server will recognise that you are referring to the local server, and handle this as

    SELECT * FROM db.dbo.btl

    However, I took it that you had done somthing like this:

    EXEC sp_addlinkedserver 'MYLINKEDSERVER', '', 'SQLNCLI', @datasrc=@@servername

    This sets up a linked server that is a loopback, and SQL Server does not realise that this is a loopback, but handles it as any other linked server. This can be great for demos and quick testing where you don't really want to involve a second instance. But since I get all the overhead of distributed queries, I would not use this in production as a long-term solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by SQLRocker Sunday, August 2, 2020 3:55 AM
    Thursday, July 30, 2020 9:06 PM
  • Thanks Erland!

    D

    Sunday, August 2, 2020 3:56 AM