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?
Ernest CookPrincipalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 CookPrincipalAuthor Commented:
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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 DevakuSr DB ArchitectCommented:
Hi,

I think the below links will work:

Understanding how SQL Server executes a query:
http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

Microsoft SQL Server Query Processor Internals and Architecture:
https://msdn.microsoft.com/en-us/library/aa226174(v=sql.70).aspx

Transact-SQL Distributed Transactions:
https://technet.microsoft.com/en-us/library/ms191156(v=sql.105).aspx

Thanks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.