Avatar of Ernest Cook
Ernest Cook
Flag for United States of America asked on

SQL Server linked server performance tuning

I have an implementation where a lot of base data comes from a Cache database and is REFRESHED daily into matching SQL tables and I would like to be able to optimize the processes from time to time as things evolve. The thing I am trying to learn is more information about the internals of how a query is processed. i.e. Does SQL cache  the remote query results in tempdb, memory?
Microsoft SQL Server

Avatar of undefined
Last Comment
Jagdish Devaku

8/22/2022 - Mon
Scott Pletcher

The query plan should tell you.  

I don't see how SQL could that for a query against a remote db source.  I would expect it to re-read the remote data every time.

Within the same query execution, yes. Otherwise no. MSSQL has no means to reliably determine whether content has changed on the other side, so caching would be very, very difficult.
If you need to access the (unchanged) data very frequently, it is a good idea to build a local copy of the table(s) once a day.
Ernest Cook

Thanks for the responses but perhaps I shouldn't have suggested my guesses since I have no idea.  My premise is that I have a non-trivial amount of data flowing from a ODBC connection via linked server thru  SQL linked server (somehow? and I want to have a deeper understanding of how SQL is managing it. Armed with that knowledge, I am hoping to be able to   is to understand Let me restate the base question:

What are the internals of how a linked server query is processed?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

The query optimizer tries to get some meta data first (like data types), and tries to determine if it can send some filter data over to the ODBC data source. In most cases it can't, so has to read all data, and then process them locally. For that, tempdb and in-memory DB is used.
For that reason it is always a good idea to use constructs like
select one, two, three
from localtable lt
join (select a, b, c from remotesrv.remotedb..remotetable where bookdate between '20150101' and '20150201') rt
on lt.key1=rt.a

Open in new window

In general, the correct assumption is "remote query cannot be optimized", so main target should be to restrict the amount of data selected remotely in the first place.
Jagdish Devaku

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.