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??
**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.
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.
answered Mar 12 '10 at 11:22 AM
Grant Fritchey ♦♦