Linked Servers

Question

Post reply

Linked Servers

Scott Arendt

SSCrazy Eights

Points: 8218
More actions
April 10, 2006 at 1:22 pm

#152901

Hello-
I have an interesting problem. I have two SQL Server 2000 SP3 running on Windows 2003 Server.
From each of the two servers, we have a legacy SQL Server 7 box set up as a linked server.
There is a stored procedure that we have created to do some data checking. From a server physically located in the same building, this stored procedure takes 48 minutes to run. From the other server, physically located 1000 miles away, the stored procedure only takes about 1 minute to run.
I performed some ping tests, and there was no noticeable difference from each of the servers to the linked servers.
I have not found any helpful information in my searched and have run out of ideas to try.
Thanks,
Scott

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

There are few other options that you can try to figure out the problem.

1. Instead of using the same SP try simple t-sql using linked server from

both 2000 server, something like Select * from pubs..authors

If you get same perform issue then atleast this will rule out problem with your SP.

2. Check and compare the configuration of both sql2000 server.

3. Create another linked server in your sql2k [samebldg] to fetch data

from sql server2k [1000 miles] and loop the result.

sql7 to --1min------> 1000 miles ---?-----> same bldg server2000

||----------------------------------------------------|||

if this works fine in acceptable time then you can think of problem in your connection in same bldg to sql 7. In that case remove that linked server and reconnect it.

Hope this helps.

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 2

Sameer-

Thanks for your suggestions.

Yesterday, I did try a select * from ... on both servers. This time "same building" performed slightly faster than "1000 miles". Although both were fast and maybe the query wasn't a good test.

I'll try your other suggestions and see if they help identify the issue.

Thanks,

Scott

skeane SSC Eights! Points: 877 More actions · Answer 3

You might try tracing the execution plan from each source to check for differences.

Also, what is the timing of the two executions? If the long runner runs first, then try switching the execution order and see if the problem moves. It could be that whicever runs first caches the data for the other to use. We once had a performance issue with a proc that looped through each of several vendors; the first vendor would have a problem with timing out, after that each of the others flew. As a crude fix I created a dummy call to the proc for the first vendor 5 minutes before the actual run. My dummy would do the work of the physical reads so the data was cached when the real thing came along, and then all vendors processed fine.

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 4

Finally getting back to this, but not sure if anyone is still following the thread.

I have eliminated the timing of the two executions by runnning the sproc twice so that caching is not a fact.

I have found that the execution plan for the same query on the two different servers is entirely different. "Same Building" has a remote query, then applies a filter, another remote query and finally a hash match.

"1000 miles away" has a single remote query replacing all of the above.

Same query, same results, one taking 20+ times longer to execute.

Any ideas?

Thanks,

Scott