How to get an actual execution plan from the linked server?

Question

There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:

Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 
    Jun 15 2019 07:56:34 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

There is a linked server defined on the main server. The linked server is:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Feb 20 2021 17:51:58 
    Copyright (C) 2019 Microsoft Corporation

I can't connect directly to that remote server via SSMS, but I can run all sorts of queries against it using OpenQuery. For example, I got its version by running this:

select * from OpenQuery([LinkedServerName], 'select @@version');

There are a number of complex legacy queries that are run on the remote linked server via OpenQuery.

I wanted to analyze them and see if there are any obvious/easy ways to improve their performance. To do that I wanted to get their execution plans. If I try to get an execution plan on a local server all I get is one Remote Scan operator that takes 35 minutes without any details.

I know that there is a SET STATISTICS XML ON statement that returns an execution plan of a query. Unfortunately, when I tried to put it into the OpenQuery, it didn't work.

Let me explain.

I can run the actual query:

select * from OpenQuery([LinkedServerName], 
'
--SET STATISTICS XML ON;
select TOP(10) * from bms.digitalbookinglinezone;
--SET STATISTICS XML OFF;
');

This returns me 10 rows as expected. When I uncomment the SET STATISTICS XML lines I get the following error message:

Msg 11527, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0] The metadata could not be determined because statement 'SET STATISTICS XML ON;' does not support metadata discovery.

At the same time I can run the following query just fine:

select * from OpenQuery([LinkedServerName], 
'
SELECT * FROM sys.dm_exec_describe_first_result_set
(N''select TOP(10) * from bms.digitalbookinglinezone'', null, 1);
')

And I'm getting valuable information about all columns in the remote table.

Is there any other "T-SQL" way of getting the execution plans? Something that I could use via the OpenQuery?

Answer 1

I don't believe it's possible to get the execution plan via OPENQUERY() remotely, and the only way would be to connect to the server directly and run the query there, unfortunately.

That being said, you should definitely see an instant performance improvement for all of your linked server queries, if you migrated the remote server data to the local server (e.g. via one of the data synchronization features such as Replication or AlwaysOn Availability Groups), assuming your local server can handle the additional query load. Just an idea. 🙂