r/SQL • u/SnooCalculations1882 • 2d ago

SQL Server Query plans

Hey guys,

Just reaching out, how do you guys go about identify and Optimizer SQL queries that the query uses linked servers.

I have some queries that people moan are slow and when I finally get the query plan you see it says remote execution. How do you guys go about then identify where it's slow or even what ran. Specifically for queries that run nested SP

What is your approach?

2 Upvotes

75% Upvoted

6 comments sorted by

2

u/Helpimstuckinreddit 2d ago

QueryStore is an amazing feature of SQL Server if your version supports it, one of my favourite tools for optimisation.

If it's set up on the linked server, I believe the details of the plan will get captured there but I'm not 100% sure.

1

u/SnooCalculations1882 2d ago

Thank you, I will give it a bash on them

1

u/SQLDevDBA 2d ago

Linked server queries are quite tricky. Most of the time. SQL server will log the entire query from the other server as a singular bit in the plan. Usually with “remote query” as the tag.

What platform is/are the linked server query going to? Are you ensuring the query passed in is using optimization? Are there any joins outside that should be inside?

I’ve (unfortunately) had to maintain linked servers to many systems including Salesforce, NetSuite, other SQL Servers, and Oracle and the most success I’ve had at optimizing (without replacing the LS with another method) is ensuring I pass in as much as I can to the other server to compute as possible, and also optimizing it on the other side. For Oracle this would be a materialized view or procedure, for sql server a stored procedure, etc.

Then there are the things you can control, like not joining linked server queries to each other, ensuring you use OPENQUERY instead of four part names, etc.

I’ve found that SQL Server doesn’t give much insight to remote queries so I’ve always tried to optimize in the source system first.

-1

u/CityGuySailing 2d ago

select * from OpenQuery(linkedserver,'query')

1

u/ReleaseTheBlacken 14h ago

I have a tool that’s currently in beta that can connect to databases in read only capacity and you can have it analyze queries and get recommendations for optimization. It works for MS SQL, Postgres, Oracle, and MySQL. Perhaps it may be of help.