Linked Server Performance in SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server Performance in SQL 2000

Author

Topic

Limbo
Starting Member

2 Posts

Posted - 2009-02-05 : 18:48:01

Hi

I have searched everywhere for some information on how a linked server handles queries and what can be causing in to run slowly, but have not found a satisfactory answer.

The setup is a local SQL 2000 server with two databases. I wish to connect directly to one and via a linked server to the 2nd (don't ask why, just accept that this is the setup, and focus on the performance :))

I run this query:
SELECT [Account Name] as description FROM [LINKSERVER].[DATABASE].[dbo].[Account] WHERE [Account No] = '1072';
Execution plan looks like this:
SELECT cost 0% - Remote Query cost 100%
This returns 16 records in about 1 sec.

I then run this query:
SELECT [Account Name] as description FROM [DATABASE].[dbo].[Account] WHERE [Account No] = '1072';
Execution plan looks like this:
SELECT cost 0% - Bookmark Lookup cost 0% - Index scan cost 100%
This returns the 16 records in 0 sec, the way it should be.

I have then made a stored procedure for testing, that runs this query. I access it like this:
EXEC [LINKSERVER].[DATABASE].[dbo].[sp_lmi_test]
Execution plan looks like this:
Execute cost 0%
Returns the 16 recrods in 0 sec, even though I am using the linked server.

So the question is what is happening? How come the direct query on the linked server is so slow?

Is it because it does not use any indexes? In which case, can I set it up so that it DOES use the indexes?

With the first query, is the entire table copied to a temporary table and then gone through line by line?

And is this behaviour any different on SQL 2005 or 2008?

Hope to get a reply

Thanks in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 19:46:18

Linked Server doesn't use index and result in remote scan. It won't use even you force it with Index hints.

Limbo
Starting Member

2 Posts

Posted - 2009-02-06 : 02:58:17

Thanks for the quick answer.

So I need to use stored procedures to hit the indexes?

Is there any good solution for joining across a linked server?

And does your answer fit for SQL 2005 and SQL 2008 too?

What solution do you suggest?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 09:15:14

quote:
Originally posted by Limbo

Thanks for the quick answer.

So I need to use stored procedures to hit the indexes?

It doesn't matter .Will result in same remote scan

Is there any good solution for joining across a linked server?

Joining tables in Linke Server is bad for performances.You should avoid as much as possible

And does your answer fit for SQL 2005 and SQL 2008 too?

I am sure in SQL 2005.I haven't tested in SQL 2008.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources