ALTERNATIVES TO LINKED SERVERS

Question

ALTERNATIVES TO LINKED SERVERS

Viewing 8 posts - 16 through 22 (of 22 total)

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

deepak.a SSCertifiable Points: 5330 More actions · Answer 1

Refer this Link http://msdn.microsoft.com/en-us/library/ms188332.aspx"> http://msdn.microsoft.com/en-us/library/ms188332.aspx

Regards -Deepak.A

parissa_bakhshi Ten Centuries Points: 1388 More actions · Answer 2

Many thanks, but this is nothing more than link server!

spaghettidba SSC Guru Points: 105712 More actions · Answer 3

TheSQLGuru (1/28/2011)
The BEST way to manage LINKED SERVERS is to never use them at all! They do LOTS of unfortunate things. The way to avoid them is to put databases on same server or use replication to keep data on local server. I have helped clients get orders of magnitude better throughput on their applications using replicated copies of remote data.

I partly disagree.

Linked server CAN do some unfortunate things. The way to avoid them is to learn how linked servers work and take all the countermeasures in setup and code.

Linked server does not only mean "SQL Server" linked server and replication is not always an option.

I work a lot with linked servers and I had to struggle with some oddities. Now I'm happy with my code and my setup and everything works like a charm.

-- Gianluca Sartori

deepak.a SSCertifiable Points: 5330 More actions · Answer 4

work a lot with linked servers and I had to struggle with some oddities. Now I'm happy with my code and my setup and everything works like a charm.

can you please share what are the steps you have followed to improve the linked server queries even i'mfacing performance issue by using four part name linked server

Regards - Deepak.A

spaghettidba SSC Guru Points: 105712 More actions · Answer 5

Where appropriate, you can use OPENQUERY to improve performance over 4 part name queries. This typically happens when you use predicates that cannot be pushed directly to the linked server (e.g. SQL Server scalar functions on some of the remote table's columns)

In other scenarios, OPENQUERY can get slower than four part names. This typically applies to UPDATE/DELETE statements, as they're usually implemented with bookmarks.

If you're not updating/deleting joined data, you can use EXEC...AT to speed it up.

Off top of my head these can be simple recommendations. If you have specific issues, you can go on an post them here.

-- Gianluca Sartori

deepak.a SSCertifiable Points: 5330 More actions · Answer 6

hi Gianluca,

Thanks for your reply

Yes i faced the issue in remote queries ,Generally, the remote statement should be exepcted to returning one or zero rows from the remote database.

When the statement becomes long running, it is because the execution plan changes and SQL Server estimates that 1 million plus rows will satisfy the query.

As a result the remote query is not properly parameterized, too many rows are requested and it takes a few minutes to retrieve all data.

I have had much difficulty in tracking down the cause of the issue and do not yet have the reason why the good plan is abandoned and a "bad" plan is chosen.

so what are best practice or best guidelines to follow to improve the remote query efficiency

Regards-Deepak.A

spaghettidba SSC Guru Points: 105712 More actions · Answer 7

I think the only "best practice" with linked server is trying to push as much as possible the predicates towards the remote tables, in order to retrieve the smallest possible row set. This is generally done forcing the remote query with OPENQUERY.

I know it could sound like an abrupt recommendation, but without seeing the code and the tables there's not much I can do from here.

-- Gianluca Sartori

Ralph Thomas Mr or Mrs. 500 Points: 522 More actions · Answer 8

I believe the 'trusted connection = yes' implies that the connection is be made using Windows authentication and is whatever account is used to logon.