Loopback Linked Server

Question

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.

Answer 1

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

-- 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))

Read up on :

• Which one is more efficient: select from linked server or insert into linked server?
• How to create an autonomous transaction in SQL Server 2008
• CSS article - If you use linked server queries, you need to read this….
• Bad database practices: abusing linked servers