What a Linked Server Query Sends: Four-Part Names, OPENQUERY and EXEC AT
A report joins a small local table to a table on a linked server. In my test version of that report, 500 local customer IDs brought back 50,000 rows in 781 ms. The same join written with OPENQUERY took 165 ms. The local execution plan shows the remote part as one operator. It doesn’t show, at a glance, what text the remote server received or how many rows came back.
There are three common ways to write the remote part: a four-part name (server.database.schema.table), OPENQUERY, and EXEC ... AT. They send different things to the remote side. I built a small test to see the text each one sent, and to count the rows that came back.
The Test Setup
I used two instances on one workstation. The local side was SQL Server 2025 (17.0.5015.4, Enterprise Developer). The remote side was SQL Server 2019 (15.0.4500.2, Developer). The linked server used the Microsoft OLE DB Driver 19 for SQL Server and my own Windows login. The other options were left at their defaults, except rpc out, which EXEC ... AT needs.
This is test-instance setup, and it writes to the server. Run it only on an instance where you’re allowed to create linked servers. The provider string sets TrustServerCertificate=Yes because my test instances offer only a self-signed certificate. It skips certificate validation, which allows a man-in-the-middle attack,[1] so on a real server, install a certificate that both servers trust instead.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* Test-instance setup (writes): run on the local instance */ USE [master]; /* Test instances only: TrustServerCertificate skips certificate validation, which allows a man-in-the-middle attack. */ EXEC [master].[dbo].[sp_addlinkedserver] @server = N'sat_oq_loop' , @srvproduct = N'' , @provider = N'MSOLEDBSQL19' , @datasrc = N'.\SQL2019' , @catalog = N'sat_openquery' , @provstr = N'TrustServerCertificate=Yes'; EXEC [master].[dbo].[sp_serveroption] @server = N'sat_oq_loop' , @optname = N'rpc out' , @optvalue = N'true'; |
The remote database has one table, dbo.orders, with 100,000 rows and a nonclustered index on customer_id. There are 1,000 customers with 100 orders each. I created the remote database with a case-sensitive collation (Latin1_General_100_CS_AS). The local database uses SQL_Latin1_General_CP1_CI_AS, which is case-insensitive. One region value is stored as East with a capital E. The local database has a 20-row table of customer IDs and a scalar user-defined function (UDF) that labels an amount as high or low.
For each query I ran it once to warm up, then ran it again with SET STATISTICS XML ON to get the actual plan. An Extended Events session on the remote instance recorded what arrived.
Where to See the Remote Text
The actual plan on the local server. The Remote Query operator submits a query to a remote source, and the text it sends appears in the operator’s properties.[2] In the plan XML it’s the RemoteQuery attribute. OPENQUERY and EXEC ... AT produce a Remote Scan operator instead. For OPENQUERY, the RemoteObject property held my query string. For EXEC ... AT it held the word STREAM. The actual row count on that operator is the number of rows that came back from the remote server.
An Extended Events session on the remote server. This shows the text the remote server received, including the calls around it. The linked server connected with the program name Microsoft SQL Server, and because I set @catalog, it ran in the sat_openquery database, so I could filter on that. Creating the session is also test-instance setup that writes to the server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* Test-instance setup (writes): run on the remote instance */ CREATE EVENT SESSION [sat_oq_remote_text] ON SERVER ADD EVENT sqlserver.rpc_completed ( ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.session_id) WHERE ([sqlserver].[database_name] = N'sat_openquery') ), ADD EVENT sqlserver.sql_batch_completed ( ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.session_id) WHERE ([sqlserver].[database_name] = N'sat_openquery') ) ADD TARGET package0.ring_buffer (SET max_memory = 8192) WITH (MAX_DISPATCH_LATENCY = 1 SECONDS); ALTER EVENT SESSION [sat_oq_remote_text] ON SERVER STATE = START; |
After each test query, I read the ring buffer on the remote instance with this query, then read the statement, batch_text and row_count fields from each event in the XML:
|
1 2 3 4 5 6 7 8 9 10 11 |
/* Read-only: run on the remote instance */ SELECT CONVERT(nvarchar(max), [t].[target_data]) AS [target_data] FROM [sys].[dm_xe_session_targets] AS [t] INNER JOIN [sys].[dm_xe_sessions] AS [s] ON [s].[address] = [t].[event_session_address] WHERE [s].[name] = N'sat_oq_remote_text' AND [t].[target_name] = N'ring_buffer'; |
The request DMVs on the remote server, while the query runs. This needs a query that runs long enough to catch. I used a four-part self-join of the remote table that took about 10 seconds:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT [r].[session_id] , [s].[program_name] , [r].[wait_type] , [t].[text] FROM [sys].[dm_exec_requests] AS [r] INNER JOIN [sys].[dm_exec_sessions] AS [s] ON [s].[session_id] = [r].[session_id] CROSS APPLY [sys].[dm_exec_sql_text]([r].[sql_handle]) AS [t] WHERE [s].[program_name] = N'Microsoft SQL Server' AND [r].[session_id] <> @@SPID; |
|
1 |
60 Microsoft SQL Server CXPACKET SELECT COUNT_BIG(*) "Expr1004" FROM "sat_openquery"."dbo"."orders" "Tbl1003","sat_openquery"."dbo"."orders" "Tbl1001" WHERE "Tbl1003"."customer_id"%(50)="Tbl1001"."customer_id"%(50) AND "Tbl1001"."amount">"Tbl1003"."amount" |
The join, the filter and the COUNT_BIG all went to the remote server, and one row came back. When I checked the local server during the run, its session was waiting on OLEDB.
Four-Part Names: SQL Server Writes the Remote Query
With a four-part name, the local optimizer decides what to send and writes new text. A filter on a literal went across as written:
|
1 |
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1001"."order_id" "Col1003","Tbl1001"."amount" "Col1005" FROM "sat_openquery"."dbo"."orders" "Tbl1001" WHERE "Tbl1001"."customer_id"=(42)' |
Around that call, the XE session recorded SET XACT_ABORT OFF and sp_getschemalock before the query, then sp_unprepare, sp_releaseschemalock and sp_reset_connection after it. With a local variable instead of a literal, the value went as a parameter. The plan’s RemoteQuery text shows a ? in that position, and the remote side receives @P1. Both returned 100 rows, and the Remote Query operator reported 100 actual rows.
A scalar UDF. On SQL Server 2025 at compatibility level 170, the local UDF was not evaluated locally. Scalar UDF inlining replaces a UDF call with an expression that the optimizer can work with.[3] The remote text contained the function’s body as a CASE expression:
|
1 |
WHERE CONVERT(varchar(10),CASE WHEN "Tbl1001"."amount">=(400.00) THEN N'high' ELSE N'low' END,0)=N'high' |
That query brought back 20,000 rows, the same number it returned to the client. Then I created the same function WITH INLINE = OFF, which the same article documents as the way to turn inlining off for one function. The remote text lost its WHERE clause, all 100,000 rows came back, and a local Filter operator applied the UDF. The plan’s elapsed time was 692 ms, with 398 ms of it in the UDF. The inlined version took 77 ms. Both are single runs. When I added customer_id = 42 next to the non-inlined UDF, the customer_id filter still went to the remote server and 100 rows came back, and the UDF filter ran locally on those 100 rows.
A collation difference. WHERE [o].[region] = 'east' went to the remote server as "region"=N'east' and returned a count of 0. The remote column is case-sensitive, so East did not match. Adding COLLATE SQL_Latin1_General_CP1_CI_AS to the comparison gave the case-insensitive answer, 25,000, but the remote text had no WHERE, and all 100,000 region values came back to be filtered locally.
The sp_serveroption documentation says that when collation compatible is not set, SQL Server always evaluates comparisons on character columns locally.[4] My linked server had collation compatible off and use remote collation on, which is the default, and the comparison was still sent to the remote server. The same page says that when use remote collation is true, “the collation of remote columns is used for SQL Server data sources.” That may be why SQL Server sent the comparison: with the remote column’s collation in force, the remote server gives the same answer the local server would. It also means 0 is the correct answer for this configuration, since that column is case-sensitive. I tested only a SQL Server data source with this one configuration. Behaviour with other providers or option values may differ.
A join to a local table. With 20 local customer IDs, the plan used a Nested Loops join, and the Remote Query operator ran 20 times. The remote server received one sp_prepexec with WHERE @P1="Tbl1002"."customer_id", then 19 sp_execute calls, one per customer ID. Each returned 100 rows, 2,000 in total. With 500 local IDs the plan kept the same shape: 500 round trips, 50,000 rows, 781 ms, and about 500 ms of OLEDB wait. So only matching rows crossed, but I paid one call per local row.
OPENQUERY: You Write the Remote Query
OPENQUERY runs a pass-through query on the linked server, and it doesn’t accept variables for its arguments.[5] The remote server received my string as a batch, character for character. With the WHERE inside the string, 100 rows came back.
A WHERE outside the string wasn’t pushed into it:
|
1 2 3 4 5 6 7 |
SELECT [q].[order_id] , [q].[amount] FROM OPENQUERY([sat_oq_loop], 'SELECT [order_id], [customer_id], [amount] FROM [dbo].[orders];') AS [q] WHERE [q].[customer_id] = 42; |
The Remote Scan operator returned 100,000 rows, and a local Filter reduced them to 100. A join between the 20-row local table and the same OPENQUERY also pulled all 100,000 rows, then used a Hash Match join. That took 101 ms (one run), compared with 16 ms for the four-part version. The Remote Scan operator estimated 10,000 rows in every OPENQUERY test, whatever the string contained. For the single-table four-part queries shown above, the Remote Query estimates were within a factor of 1.5 of the actual counts. That doesn’t hold for every query. One I left out, with the inlined UDF and customer_id = 42 together, estimated 55 rows and returned 0.
EXEC … AT: A Parameterized Pass-Through
EXEC ... AT sends a command string to a linked server, and a ? in the string marks a parameter.[6] The remote server received it as sp_executesql with a typed parameter:
|
1 |
exec sp_executesql N'SELECT [order_id], [amount] FROM [dbo].[orders] WHERE [customer_id] = @P1;',N'@P1 int',42 |
It returned 100 rows, and INSERT ... EXEC ... AT into a temporary table also brought back 100. The XE session showed no schema lock calls, only the sp_executesql call and sp_reset_connection. With rpc out turned off, a plain EXEC (N'SELECT 1 AS [one];') AT [sat_oq_loop] failed with Msg 7411, Server 'sat_oq_loop' is not configured for RPC. EXEC isn’t a table source, so I can’t join to its result directly. I capture it into a table first.
Rows Returned From the Remote Server
Each figure is the actual row count on the Remote Query or Remote Scan operator, confirmed by the row_count in the remote XE session.
| Query | Rows sent back |
|---|---|
Four-part, WHERE customer_id = 42 |
100 |
Four-part, inlined UDF in WHERE |
20,000 |
Four-part, UDF with INLINE = OFF |
100,000 |
Four-part, region = 'east' (count) |
1 |
Four-part, region = 'east' COLLATE ... (count) |
100,000 |
| Four-part, join to 20 local rows | 2,000 (20 calls) |
| Four-part, join to 500 local rows | 50,000 (500 calls) |
OPENQUERY, WHERE inside the string |
100 |
OPENQUERY, WHERE outside the string |
100,000 |
OPENQUERY, join to 20 local rows |
100,000 |
OPENQUERY, join to 500 local rows |
100,000 |
EXEC ... AT with a parameter |
100 |
What I’d Check First
When a linked server query is slow, I’d get the actual plan and read the RemoteQuery property before changing anything. If its text has no WHERE, find the predicate that stayed local. In my tests, that was a non-inlined UDF, an explicit COLLATE, or a filter written outside an OPENQUERY string. If a Remote Query operator shows a high execution count, the time may be going into round trips, and pulling the remote rows in one call may do better. In my tests the crossover showed up between my two local table sizes. With 20 local rows, the four-part join took 16 ms and the OPENQUERY scan of all 100,000 rows took 101 ms. With 500 local rows, the four-part join made 500 round trips and took 781 ms, and the same OPENQUERY join took 165 ms. Where that crossover falls depends on how many rows each side has, so test both with your own data.
Writes across a linked server have their own problems, and Slow inserts across a linked server covers the case where a remote insert runs row by row.
Some limits on all of the above:
- I tested one linked server, on one workstation, from SQL Server 2025 to SQL Server 2019, with the MSOLEDBSQL19 provider. Other providers, and non-SQL Server data sources, may send different text.
- The timings are single runs on an idle machine. I’d trust the row counts. Treat the times as rough.
- The UDF result depends on scalar UDF inlining, so it may differ at a lower compatibility level, on an older version, or for a UDF that can’t be inlined.
- I didn’t test
collation compatibleset to true,lazy schema validation, or theREMOTEjoin hint.
Related: Auditing Linked Server Security: Who Connects as Whom.
If you’ve traced linked server traffic and seen something different, I’d like to hear about it. Find me on Bluesky or LinkedIn.
References
- TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩
- Showplan Logical and Physical Operators Reference - Microsoft Learn. States that Remote Query submits a query to a remote source and shows its text in the Properties pane, and that Remote Scan scans a remote object. ↩
- Scalar UDF Inlining - Microsoft Learn. Describes how scalar UDFs are transformed into scalar expressions or subqueries at compatibility level 150 and higher, and how
WITH INLINE = OFFturns this off for one function. ↩ - sys.sp_serveroption (Transact-SQL) - Microsoft Learn. States that when
collation compatibleis not set, comparisons on character columns are always evaluated locally, and thatuse remote collationdefaults to true, in which case the collation of remote columns is used for SQL Server data sources. ↩ - OPENQUERY (Transact-SQL) - Microsoft Learn. States that OPENQUERY executes a pass-through query on the linked server and does not accept variables for its arguments. ↩
- EXECUTE (Transact-SQL) - Microsoft Learn. Documents
EXECUTE ... AT linked_server_namefor pass-through commands and the?parameter placeholder. ↩