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
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