Help Troubleshooting Linked Server Performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help Troubleshooting Linked Server Performance

I have a SQL 2000 Server(S1) and I’ve setup a Linked Server to another SQL 2000 Server(S2). On S1 I have a view that issues a query against S2. It takes 20 seconds to return the result set. However if I setup the same Linked Server on a third SQL 2000 Server(S3), and create the same view, the query returns the result set in 1 second. I’ve tried running profiles and checked Client/Network Utility settings and server settings, and all appear to be identical. I’m at a loss to this point on how to further troubleshoot this problem. Additionally, one strange item that I’ve noticed is when I SET STATISTICS PROFILE ON before I run the query to query the view I get different results on S1 as I do on S3. It appears that S1 is doing more work than S3. I feel like there is a setting somewhere that I just can’t find. Any help or suggestions on trouble shooting this issue would be greatly appreciated. Frank

Can you confirm the MDAC level between these linked servers. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for the response Satya. We are running MDAC version 2.7 on Both.