SQL Server linked server performance tuning

Ernest Cook
Ernest Cook used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Advisor
Top Expert 2015

Commented:
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 CookPrincipal

Author

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 Advisor
Top Expert 2015

Commented:
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.
Sr DB Architect
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial