Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ?

I google it for several minutes and I find nothing about it.

up vote 3 down vote accepted

Basically Loopback linked servers are mainly useful when testing an application that uses distributed queries on a single server network.

Note that they cannot be used in a distributed transactions.


In terms of performance, there will be performance impact when you do selects or joins using LOOPBACK server as all the data has to go across wire.

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

Bottom line is linked servers are evil in terms of performance. Unless you really need them, dont / avoid using them.

For e.g -- Consider a basic example of selecting some data from a table

-- taken from : Aaron's article : "Best approaches for running totals – updated for SQL Server 2012"
-- http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
CREATE TABLE dbo.SpeedingTickets
(
    [Date]      DATE NOT NULL,
    TicketCount INT
);
GO

ALTER TABLE dbo.SpeedingTickets ADD CONSTRAINT pk PRIMARY KEY CLUSTERED ([Date]);
GO

;WITH x(d,h) AS
(
    SELECT TOP (250)
        ROW_NUMBER() OVER (ORDER BY [object_id]),
        CONVERT(INT, RIGHT([object_id], 2))
    FROM sys.all_objects
    ORDER BY [object_id]
)
INSERT dbo.SpeedingTickets([Date], TicketCount)
SELECT TOP (10000)
    d = DATEADD(DAY, x2.d + ((x.d-1)*250), '19831231'),
    x2.h
FROM x CROSS JOIN x AS x2
ORDER BY d;
GO

SELECT [Date], TicketCount
    FROM dbo.SpeedingTickets
    ORDER BY [Date];
GO

--- select data as normally you would do

select [Date], TicketCount from test_kin.dbo.SpeedingTickets

Execution plan will have a clustered index scan

enter image description here

-- select data using loopback server (highly inefficient). Look at the CPU & Operator cost (though this is a simple example, but test out on a good data set with multiple joins, etc - you will clearly understand the difference - as to how expensive it is to use Linked (in this case loopback server))

enter image description here

Read up on :

  • +1 I put in place an architecture where our OLTP requests data via a linked server which points at a DB either in the same instance, or elsewhere (all it cares about is the linked sever name, and not where the DB actually is). Using it in all cases simplifies configuration and querying significantly. Having said that, we call stored procedures in the target, not query the tables directly, and thus avoid perf issues related to query plans (note: we don't pull back huge amounts of data). The cost if the DB is local is a bit of extra roundtrip overhead, which is relatively insignificant. – Jon Seigel Apr 5 '14 at 15:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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