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.
Transact-SQL
/* 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';
12345678910111213141516 | /* 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:
Transact-SQL
/* 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;
12345678910111213141516 | /* Test-instance setup (writes): run on the remote instance */CREATE EVENT SESSION [sat_oq_remote_text] ON SERVERADD 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:
Transact-SQL
/* 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';
1234567891011 | /* 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:
Transact-SQL
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;
123456789101112131415 | 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;
---|---
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"
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:
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)'
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:
WHERE CONVERT(varchar(10),CASE WHEN "Tbl1001"."amount">=(400.00) THEN N'high' ELSE N'low' END,0)=N'high'
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:
Transact-SQL
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;
1234567 | 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:
exec sp_executesql N'SELECT [order_id], [amount] FROM [dbo].[orders] WHERE [customer_id] = @P1;',N'@P1 int',42
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 compatible` set to true, `lazy schema validation`, or the `REMOTE` join 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
1. 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. ↩
2. 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. ↩
3. 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 = OFF` turns this off for one function. ↩
4. sys.sp_serveroption (Transact-SQL) - Microsoft Learn. States that when `collation compatible` is not set, comparisons on character columns are always evaluated locally, and that `use remote collation` defaults to true, in which case the collation of remote columns is used for SQL Server data sources. ↩
5. 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. ↩
6. EXECUTE (Transact-SQL) - Microsoft Learn. Documents `EXECUTE ... AT linked_server_name` for pass-through commands and the `?` parameter placeholder. ↩
### Share this:
* * Share on Bluesky (Opens in new window) Bluesky
* Share on Mastodon (Opens in new window) Mastodon
* Email a link to a friend (Opens in new window) Email
* More
*
* Share on Threads (Opens in new window) Threads
* Share on Reddit (Opens in new window) Reddit
* * Share on WhatsApp (Opens in new window) WhatsApp
* Print (Opens in new window) Print
*
### _Related_