x

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.

Thanks

more ▼

asked Mar 12 '10 at 10:57 AM in Default

Leo gravatar image

Leo
1.6k 51 56 58

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

2 answers: sort newest
more ▼

answered May 25 '10 at 10:04 PM

Vinicio Aizpurua gravatar image

Vinicio Aizpurua
2 1 1 1

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

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 '10 at 11:22 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

Solid suggestions
Mar 12 '10 at 03:46 PM CirqueDeSQLeil
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x672
x101

asked: Mar 12 '10 at 10:57 AM

Seen: 3495 times

Last Updated: Mar 12 '10 at 03:47 PM