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
?