SQL Server Linked Servers: Performance Traps, Security Gaps, and the Queries That Will Cost You

Every few months, a client sends me a profile with a distributed query running slow. Not slow in the way that bad indexes make things slow — slow in the way that looks like a network problem, or a remote server problem, until you pull the actual execution plan and find something you did not expect: a four-table join being pushed entirely to the remote server because someone wrote FROM LocalTable LT INNER JOIN RemoteServer...RemoteDB.dbo.RemoteTable RT ON LT.id = RT.id and SQL Server decided the cheapest path was to pull every row from the remote table across the network before applying the join predicate.

That is the linked server problem in a sentence. The feature works. The defaults will burn you.

After two decades of debugging other people's linked server configurations, here is the unfiltered version of what I have seen go wrong, how to fix it, and the security posture you should have in place regardless of whether performance is your current problem.

Section 1: How Linked Server Queries Actually Execute

The OLE DB Provider and Remote Query Processing

When you run a query referencing a linked server, SQL Server's query optimizer decides how much work to push to the remote server versus pulling data locally. This decision is controlled by the DATA SOURCE, PROVIDER, and COLLATION COMPATIBLE properties of the linked server definition, and by query statistics on both sides of the join.

The two execution modes are:

• Remote query execution — SQL Server pushes the entire query (or as much as possible) to the remote provider. The remote server executes the predicate and returns only the rows that satisfy the WHERE or JOIN conditions. This is fast, but only works when the remote provider can handle the query syntax SQL Server generates for it.
• Remote scan — SQL Server pulls all rows from the remote table and executes the join or filter locally. This happens when the optimizer cannot push predicates, when the provider does not support the required operations, or when statistics are missing on the remote side. On a table with 50 million rows, this will hurt.

The execution plan is your first diagnostic tool. Look for Remote Scan operators — they tell you the optimizer decided not to push predicates to the remote server. When you see a Remote Scan on a large table, investigate the linked server properties and the query structure before assuming the remote server is the problem.

Checking Execution Plans for Linked Server Queries

-- Run this against the local instance to see how the remote query is processed
-- The key operator to look for: Remote Scan (means rows pulled locally)
-- Remote Query (means predicates pushed to remote) is what you want

-- Clear cache for this test (dev only — never in production)
DBCC FREEPROCCACHE;

-- Run your distributed query
SELECT
    lt.id,
    lt.customer_name,
    rs.order_total,
    rs.order_date
FROM dbo.LocalCustomers lt
INNER JOIN RemoteServer.RemoteDB.dbo.Orders rs
    ON lt.id = rs.customer_id
WHERE lt.region = 'Northeast'
    AND rs.order_date >= '2026-01-01';

-- View the actual execution plan — look for:
--   Remote Query (good) — predicates sent to remote
--   Remote Scan (bad) — all rows pulled, filtered locally

-- Also check estimated vs actual rows — large gaps here indicate
-- stale or missing statistics on the remote side
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Diagnosing Remote Scan with DMVs

-- Find recently executed remote queries and their operator type
-- Run on the local instance
SELECT
    qs.execution_count,
    qs.total_elapsed_time / 1000 AS total_elapsed_sec,
    qs.total_logical_reads,
    qs.total_physical_reads,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2 + 1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%RemoteServer%'
    OR qt.text LIKE '%OPENQUERY%'
ORDER BY qs.total_elapsed_time DESC;

-- Check what the remote provider supports (affects what can be pushed)
-- Look for provider capabilities in the error log or use:
SELECT
    name,
    provider,
    data_source,
    collate Compatibility AS is_collate_compatible,
    use_remote_collation,
    query_timeout
FROM sys.servers
WHERE is_linked = 1;

Section 2: Performance Configuration — The Settings That Matter

COLLATE COMPATIBLE

The most impactful linked server setting for performance is COLLATE COMPATIBLE. When set to True, SQL Server assumes the remote server has compatible collation and can evaluate join predicates remotely. When False (the default), every join condition is evaluated locally — Remote Scan. The trap: setting it True when collations genuinely differ causes incorrect results. Use COLLATE DATABASE_DEFAULT in join predicates to normalize comparison behavior without changing server-level settings.

Optimizing Linked Server Configuration

-- Modify linked server properties
-- Note: use sp_serveroption (not ALTER SERVER) for runtime changes

-- Set COLLATE COMPATIBLE to enable remote predicate pushdown
EXEC sp_serveroption
    @server = 'RemoteServer',
    @optname = 'collation compatible',
    @optvalue = 'true';

-- Enable remote procedure calls if you call remote SPs
EXEC sp_serveroption
    @server = 'RemoteServer',
    @optname = 'rpc out',
    @optvalue = 'true';

-- Set query timeout to prevent runaway queries
EXEC sp_serveroption
    @server = 'RemoteServer',
    @optname = 'query timeout',
    @optvalue = '30';

-- Disable DTC escalation on remote proc calls (reduces overhead)
EXEC sp_serveroption
    @server = 'RemoteServer',
    @optname = 'remote proc transaction promotion',
    @optvalue = 'false';

-- Verify current settings
SELECT
    name, provider, data_source,
    collation_name,
    is_collation_compatible,
    use_remote_collation,
    rpc, rpc_out,
    remote_proc_transaction_promotion
FROM sys.servers WHERE is_linked = 1;

Section 3: OPENQUERY — When and Why to Use It

OPENQUERY forces SQL Server to send a specific query string to the remote provider. This is a double-edged sword: it lets you force the query shape you want, but you cannot use local variables in the query string without dynamic SQL.

-- Without OPENQUERY — optimizer may pull the full Orders table
SELECT lt.*, rs.order_total
FROM dbo.LocalCustomers lt
INNER JOIN RemoteServer.RemoteDB.dbo.Orders rs ON lt.id = rs.customer_id;

-- With OPENQUERY — the WHERE clause is sent to the remote server
SELECT lt.*, rq.order_total
FROM dbo.LocalCustomers lt
INNER JOIN OPENQUERY(RemoteServer,
    'SELECT customer_id, order_total FROM Orders WHERE order_date >= ''20260101''') rq
    ON lt.id = rq.customer_id;

-- OPENQUERY with dynamic filtering
DECLARE @filterRegion NVARCHAR(50) = 'Northeast';
SELECT lt.id, lt.customer_name, rq.order_total
FROM dbo.LocalCustomers lt
INNER JOIN OPENQUERY(RemoteServer,
    'SELECT customer_id, order_total FROM Orders WHERE order_date >= ''20260101''') rq
    ON lt.id = rq.customer_id
WHERE lt.region = @filterRegion;

Section 4: Security — The Defaults Will Hurt You

Security Context and Credential Delegation

Linked servers authenticate via pass-through (caller's credential forwarded to remote) or a defined login mapping (fixed credential for all connections). Delegation is cleaner but requires Kerberos — if NTLM is used instead, authentication fails or falls back to the defined login. Defined login mappings create risk: if the mapped account has elevated privileges, every caller inherits them.

Auditing Linked Server Access

-- Audit mapped logins — look for high-privilege remote accounts
SELECT
    s.name AS linked_server,
    l.remote_name AS mapped_remote_login,
    l.uses_self_credential,
    s.is_collation_compatible,
    s.data_source
FROM sys.servers s
LEFT JOIN sys.linked_logins l ON s.server_id = l.server_id
WHERE s.is_linked = 1;

-- High-privilege remote mappings are a risk — audit quarterly
SELECT s.name, l.remote_name
FROM sys.servers s
JOIN sys.linked_logins l ON s.server_id = l.server_id
WHERE s.is_linked = 1
    AND l.remote_name IN ('sa', 'admin', 'db_owner');

-- Disable data access on RPC-only linked servers
EXEC sp_serveroption @server='RemoteServer', @optname='data access', @optvalue='false';

Section 5: A 20-Year Rule — When to Use Them and When Not To

Use linked servers for ad-hoc reporting from trusted remote servers with moderate data volumes. Use ETL or replication instead — SSIS, BCP, or transactional replication are the right tools for large data movement. Wrap linked server queries behind views or stored procedures so remote schema changes surface as compilation errors. Audit linked servers quarterly and remove any pointing to decommissioned instances.