Linked Server on SQL 2008 is slow

Question

Hi,

I have 2 MS SQL servers one with SQL 2008(machine A) and another with MS SQL 2005 (machine B)

I created a linked server from Machine A to Machine B. Executed a simple query

Select * from [Machine B].Database.Xtable

It took nearly 30 seconds to bring back 496 rows of data.

Similarly created a linked server from machine B to machine A.Executed the simple query

Select * from [Machine A].Database.Xtable

It took less than a second to display the same 496 rows.

Is there any specific setting to be done on MS SQL 2008 to improve the performance.

Any help is appreciated.

Thanks

Hari

Answer 1

Can you check if there is any different in query plan?

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 2

Since your query has no WHERE clause and no ORDER BY it does not seem likely that a query plan could be very different.  Are these the same type of computer?  Does this happen every time you used the linked server to  Machine B or does performance improve for the second query?

Check to make sure that both machines are provisioned the same (memory, CPU, both x86 or x64, network connectivity, etc.) and are running equivalent sets of processes.  (True for physical and virtual machines.)  Make sure neither machine 'goes to sleep' at any time.  

If this is running on a VM, make sure that the physical machine hosting the VMs are not being heavily overused by another VM, either CPU or heavy disk I/O. 

RLF

Answer 3

Hari

 

Does this worse performance consistently exist on your boxes? you always get over 30 seconds when query B from A?

---

If you think my suggestion is useful, please rate it as helpful.
If it has helped you to resolve the problem, please Mark it as Answer.

http://twitter.com/7Kn1ghts

Answer 4

Can you check the query execution plan? I belive that depends on what database tasks are running on the server at the time you run this query.

Any SQL tasks are running on the Machine B when you run the query from Machine A? are they machines same configuration interms of hardware and network setups

---

http://uk.linkedin.com/in/ramjaddu

Answer 5

Hi,

Was this problem ever solved ?,

I have a similair issue.

From a SQL Server 2008 R2 it is extremely slow quering any sql server 2000 or sql server 2005. It does not matter if I use linked server or set up a data connection inside SSIS.

We talk 20 times slower from SQL server 2008 compared to SQL server 2005.

Any help is appreciated.

Thomas Hansen

---

TIH

Answer 6

Hi,

Was this problem ever solved ?,

I have a similair issue.

From a SQL Server 2008 R2 it is extremely slow quering any sql server 2000 or sql server 2005. It does not matter if I use linked server or set up a data connection inside SSIS.

We talk 20 times slower from SQL server 2008 compared to SQL server 2005.

Any help is appreciated.

Thomas Hansen

---

TIH

check http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/7f1d3eda-d2ae-411b-85f6-4326e5d58fdf/

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 7

Thanks for the response.

Changing these parameters on the linked server data provider does not change anything.

To illustrate the situation - this very simple query. Cn-lm0700 is an sql server 2000.

select

* from [cn-lm0700].axapta.dbo.custsettlement

 

takes 11 seconds to execute from an sql-server 2005 and 3 minutes 11 sec when executen from an sql server 2008.

I also tried using open query. The result is ther same.

We run windows 2008R2 and SQL server 2008R2 enterprise.

We have sspend quite some time investigating this issue - hope you have more ideas!

Thomas Hansen

---

TIH

Answer 8

Thanks for the response.

Changing these parameters on the linked server data provider does not change anything.

To illustrate the situation - this very simple query. Cn-lm0700 is an sql server 2000.

select

* from [cn-lm0700].axapta.dbo.custsettlement

 

 

I also tried using open query. The result is ther same.

We run windows 2008R2 and SQL server 2008R2 enterprise.

We have sspend quite some time investigating this issue - hope you have more ideas!

Thomas Hansen

---

TIH

takes 11 seconds to execute from an sql-server 2005 and 3 minutes 11 sec when executen from an sql server 2008.

Sorry.. clicked "marked as answer" by mistake.

Can you share the query plan? There is no filter condition in the query?

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 9

No - to eliminate "other issues" there is no where clause.

The execution plan is

---

TIH

Answer 10

Hello,

I 've got the same kind of problem with linked servers between SQL 2008R2 et SQL 2005 Express (SP3), when we insert data from a linked server, in one way the result is immediat, in the other way it takes 3 minutes to retrieve 3000 rows from a table.

if we are connected on machineB, and execute :

INSERT INTO [machineA].db1.dbo.articles SELECT * FROM [machineB].db2.dbo.articles

it takes 3 minutes

if we are connected on machineA and execute the same, it takes less than 2secondes !!!

Other tips, I created a new instance of SQL server2008R2 on the same machine, and try to execute the same code. The result is that it takes also 3 minutes !

I try to analyse ethernet dataflow, and we can see that for both test data are not presented the same way, for the "slow" case, data are sended row by row with column name for each records, for the "speedy" case data are sended with 1 line with columns names and then a block of row.

Can any one help on that subject ?

David