Link to home
Start Free TrialLog in
Avatar of Ernest Cook
Ernest CookFlag 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?
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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.
Avatar of Ernest Cook

ASKER

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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Jagdish Devaku
Jagdish Devaku

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial