Linked Server Information 1

[DavidLemire]

Hi Guys,

I have 2 SQL servers linked by a VPN. On one server, I need to run a SP every one minute on the other server. To do that, we decided a linked server would be best.

We want to know which one is most optimized between:

1. At run-time, create a linked server, open it, run query, close linked server, destroy definition.
2. Create linked server only ONCE. Then, at run-time, open linked server, run query, close linked server.

Put this way, I guess the second is obvious. However, my question is more on what is the most resource-consuming (every resource on target server counts!); is it having a linked server definition always on target server, or is it to create-destroy every time we need to run the stored procedure?

Thanks for info!

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[mrdenny]

Create the linked server and leave it there. Having the linked server there will expend no additional resources, however dropping and recreating it over and over will add unneeded load to the system.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DavidLemire]

Thanks mrdenny!

Do you have any advice on optimization for this linked server? Minimum impact must be met for target server, maximum processing should be induced to the calling server.

Right now, I heard you can specify that the two servers use same collation, so the target doesn't have to re-process the query.

Any other helpful tips?

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[mrdenny]

Return as little data as possible accross the link. When at all possible run stored procedures on the linked server and return the results into a temp table, then use the local temp table.

Make sure that unauthorised people don't have access.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com