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

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 :

• 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

• Jon Seigel: +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.