Very Slow Response from Link Server

Hi Everyone,

I am setting up link servers from one of my production server which installed SQL 2008 and link to SQL 2000 Server (remote server). I have created the link-server as below

General => Using Microsoft OLE DB Provider for SQL Server Link Server Name - TEST Product Name - Remote_ServerName Data Source - Remote_ServerName

Security => Be Made using this security context user id - xxx password - xxx

Server Option => All False except Data Access, RPC, RPC OUT, Use Remote Collation are True.

I have already setup same settings on 4 other productions server and I have no performance problem.

Anybody can tell me why that is too slow on my New Production Server. Network Problem??

Any suggestions?

**I have already use profiler and it said Duration - 107016 to run the stored procedure and other servers are only 506 on SAME QUERY/SCRIPT.


more ▼

asked Mar 12, 2010 at 10:57 AM in Default

avatar image

1.6k 55 59 62

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Without more information, it's hard to know what the issue might be. But yeah, I'd assume something was up with the network. To validate it, gather wait statistics. Start with sys.dm_os_wait_stats. That will tell you what things are waiting on within the server and will help you begin to narrow down the problem.

I think, in this case, due to the linked server, it would be a secondary approach, but I'd also look at the execution plans from the servers with the good performance and the one from the server with the bad performance. It's unlikely to tell you anything, but it's possible.

more ▼

answered Mar 12, 2010 at 11:22 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Solid suggestions

Mar 12, 2010 at 03:46 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

Please read this article http://support.microsoft.com/kb/981355

more ▼

answered May 25, 2010 at 10:04 PM

avatar image

Vinicio Aizpurua
2 2 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 12, 2010 at 10:57 AM

Seen: 4652 times

Last Updated: Mar 12, 2010 at 03:47 PM

Copyright 2018 Redgate Software. Privacy Policy