question

Leo avatar image
Leo asked

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

sql-server-2008sqllinked-server
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Solid suggestions
0 Likes 0 ·
Vinicio Aizpurua avatar image
Vinicio Aizpurua answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.