When querying over a linked server connection across a WAN, execution location determines performance. The practices below ensure filtering and logic happen on the remote server, so only the results you need cross the wire.

Query Construction

1. Use OPENQUERY instead of four-part naming (highest impact)

Four-part queries (LinkedServer.DB.Schema.Table) allow SQL Server to pull the entire remote table locally before filtering. OPENQUERY forces the predicate to execute on the remote server — only matching rows return.

-- Avoid: full remote table crosses the wire, filtered locally
SELECT * FROM AeriesLinked.AeriesDB.dbo.STU WHERE SC = 100;

-- Preferred: filter executes remotely, only results return
SELECT * FROM OPENQUERY(AeriesLinked,
  'SELECT * FROM DST26000AeriesDB.dbo.STU WHERE SC = 100');

2. Select only the columns you need

Avoid SELECT *. Every unneeded column multiplies data transferred by row count. Name only what your query uses — especially important on wide tables.

-- Avoid
SELECT * FROM OPENQUERY(AeriesLinked,
  'SELECT * FROM DST26000AeriesDB.dbo.STU ...')

-- Preferred
SELECT * FROM OPENQUERY(AeriesLinked,
  'SELECT ID, SC, SN, FN, GR FROM DST26000AeriesDB.dbo.STU ...')

3. Place all filters inside the OPENQUERY string

Any WHERE clause placed outside the OPENQUERY string is evaluated locally — after all rows have already crossed the wire. Keep predicates inside.

-- Avoid: WHERE applied locally after full remote fetch
SELECT * FROM OPENQUERY(AeriesLinked,
  'SELECT ID, GR FROM DST26000AeriesDB.dbo.STU')
WHERE GR = 11;

-- Preferred: predicate executes on the remote server
SELECT * FROM OPENQUERY(AeriesLinked,
  'SELECT ID, GR FROM DST26000AeriesDB.dbo.STU WHERE GR = 11');

4. Pre-stage remote data before joining to local tables

Joining a remote table directly to a local table can force the remote side to be fully materialized locally first. Pull the filtered remote result into a temp table, then join.

-- Avoid: may trigger a full remote table scan mid-join
SELECT l.*, r.GR
FROM LocalRoster l
JOIN AeriesLinked.DST26000AeriesDB.dbo.STU r ON l.StudentID = r.ID;

-- Preferred: fetch only what's needed, then join locally
SELECT * INTO #RemoteStudents
FROM OPENQUERY(AeriesLinked,
  'SELECT ID, GR FROM DST26000AeriesDB.dbo.STU WHERE SC = 100');

SELECT l.*, r.GR
FROM LocalRoster l
JOIN #RemoteStudents r ON l.StudentID = r.ID;

5. Parameterize OPENQUERY using dynamic SQL

OPENQUERY does not accept variables directly. Use dynamic SQL via sp_executesql to pass runtime values into the remote query string.

DECLARE @SC INT = 100;
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM OPENQUERY(AeriesLinked, ''
  SELECT ID, GR FROM DST26000AeriesDB.dbo.STU
  WHERE SC = ' + CAST(@SC AS NVARCHAR(10)) + N''')';
EXEC sp_executesql @sql;

Linked Server Configuration

6. Configure linked server options for WAN-optimized behavior

These options are set once on your local SQL Server instance and affect how the optimizer handles the remote connection. Run as sysadmin on your local server.

-- Allows the optimizer to push predicates without collation conversion overhead
EXEC sp_serveroption 'AeriesLinked', 'collation compatible', 'true';

-- Required to enable remote procedure execution via EXEC ... AT
EXEC sp_serveroption 'AeriesLinked', 'rpc', 'true';
EXEC sp_serveroption 'AeriesLinked', 'rpc out', 'true';

When Using a Remote Stored Procedure

7. Execute remote procedures using EXEC ... AT

When a stored procedure is available on the remote server, use EXEC ... AT to run it there. All logic executes remotely; only the result set returns. Requires rpc and rpc out to be enabled on the linked server definition (see #6).

-- Executes on the remote server, returns only the result set
EXEC ('EXEC dbo.GetActiveStudentsBySchool @SC = 100') AT AeriesLinked;

-- With output captured locally
SELECT * INTO #Results FROM OPENQUERY(AeriesLinked,
  'EXEC dbo.GetActiveStudentsBySchool @SC = 100');

Contact your Aeries support team to request remote stored procedures for complex reporting or extraction scenarios not well served by direct table queries.