January 11, 2006 at 9:46 am
Server1: Windows 2003 with SQL Server 2000 SP3
Server2: Windows 2000 with SQL Server 2000 SP3
Using Query Analyzer on server1, I can execute a SELECT statement that returns results in 3 seconds. I execute the same SELECT from server2 using a linked server and it takes almost 3 minutes for the results to be returned. Can some one explain to me why it takes 3 minutes when using a linked server?
The SELECT is selecting from a view, which uses a table join and the SELECT has a simple where clause (ie: column1 = a value, column2 = a value and column3 = a value).
January 11, 2006 at 12:26 pm
What is the query plan of the query on server02?
Are both server collation compatible? (is the collation compatible option on the linked server switched on or is there a specific collation specified?) Otherwise server2 might request the whole view and apply the where-clause afterwards.
Any specific reason not to apply SP4?
January 16, 2006 at 2:00 pm
We've had this problem too and struggled with it for quite some time. And though it could be, for us, it was not related to collation.
What are the security permissions set up for the remote (linked) user? If the remote user does not have ddladmin, and is therefore unable to execute dbcc show_statistics, you may get a bad execution plan.
What we did to mitigate the security issue of the remote user having ddl admin:
Created a distinct remote user just for your linked server.
Map the local login to the remote login in the linked server security properties.
Be sure to clear the procedure cache on both servers when testing (dbcc freeproccache).
January 17, 2006 at 10:26 am
The issue has been resolved.
The link was on a over utilized development server. Once I moved the link to another development server, it worked perfectly with a 3 seconds response.
Thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply