Linked Server resources

Question

Linked Server resources

andersg98

SSCertifiable

Points: 5180
More actions
March 3, 2004 at 9:55 am

#101897

Our developers are increasingly relying on Linked Servers instead of DTS. Somewhere I got the idea that Linked servers (especially to Oracle) can consume a lot of memory and cpu resources. I have done some searching and cannot find any information in this area.
Can anyone speak to this? Does running openquery against multiple linked servers represent any risk to the system performance?

Thanks,

Glen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

There is definitely an impact on performance. The issue is where the query is actually run. I believe OPENQUERY runs the query on the remote server so the overhead is mainly returning the resultset to the host over the network. We have found that calling sp's against linked servers, this is sql to sql, improves performance tremendously.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

andersg98 SSCertifiable Points: 5180 More actions · Answer 2

Thanks for the response.

So, you would say that since the query is run on the remote server the impact to the server that the link is configured/invoked from has minimal impact to performance?

There seems to be a distinct difference in how SQL to SQL linked servers work and linked servers of another RDMS. I am especially concerned about openquery to oracle. It seems like SQL reserves resources for the query while it waits for the response back from the Oracle server.

Glen

noeld SSC Guru Points: 96590 More actions · Answer 3

This topic is a little tricky and is heaviliy dependent on the queries runned and on the amount of data transferred across the network.

FROM BOL:

------------------------------------------------------------

Remote Query Execution

SQL Server attempts to delegate as much of the evaluation of a distributed query to the SQL Command Provider as possible. An SQL query that accesses only the remote tables stored in the provider's data source is extracted from the original distributed query and executed against the provider. This reduces the number of rows returned from the provider and allows the provider to use its indexes in evaluating the query.

.....

------------------------------------------------------------------------

There are also other conditions to determine if it is going to be local or remote like column types etc.

The only thing that is granted is that with OPENQUERY you force the delegation and with four part names it may not happen

there is definitively NETWORK overhead no matter what you use but you should be very careful with the linked server configured parameters

for more goto Optimizing Distributed Queries on BOL

On some queries of type

SELEC Tbl1.fld1,Tbl2.fld1

From Tbl1 inner join (LINKED SERVER Result) Tbl2

You may gain performance specifying the "REMOTE" join hint

* Noel