Remote Queries Slow From 2005 -> 2000

Question

Post reply

Remote Queries Slow From 2005 -> 2000

Viewing 14 posts - 1 through 15 (of 15 total)

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

Jason Crider SSCertifiable Points: 6019 More actions · Answer 1

*bump*

Surely someone has dealt with this sort of thing.

I've found a few places that talk about OPENQUERY but they are mainly dealing with SQL 2000.

A profile on the remote server is showing a ton of sp_cursorfetch but I can't determine why.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 2

It is hard to tell what the issue is from just looking at the plan right here, but in the past when I have had issues with performance on linked servers (and much like yours here it was a linked server within a join to some other tables) the thing that has invariably helped the most for me was to bring the data from the Linked Server into a temp table or table variable in a pre-select and then join that table to the remainder, such that the final query is totally local.

IMO, the optimizer struggles when it comes to linked servers and by doing this you essentially help it out. It is worth a shot, I would say unless someone else has other options out there.

ravikanth. Hall of Fame Points: 3502 More actions · Answer 3

ravikanth.

Hall of Fame

Points: 3502

January 26, 2009 at 3:59 pm

#932879

Run DBCC FREEPROCCACHE to clear out the cache.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 4

I tried that and it still took a long time.

I didn't run profiler at the time to see if it still chose to do the cursor fetch but I will be looking into that.

I just restored the same 3 db's onto another linked server that was 2005 so it's not a 2000/2005 issue from what I can tell.

It may just be that this stored proc (and application as a whole) won't perform very well across linked servers due to the programming style.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Jason Crider SSCertifiable Points: 6019 More actions · Answer 5

yea, I tried that as well so that I could get clean stats. I believe I mentioned that in the first post.

I still can't figure out why it's doing a cursor (and a slow one at that).

update: For clarification, this is a 64 bit SQL Server hitting 32 bit. I placed a wrapper as detailed HERE.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

richardbDBA SSC Rookie Points: 27 More actions · Answer 6

Did you ever get this figured out ... I am having the same issue...tons of sp_cursorfetch that didn't happen with the same code when my linked server setup was from 2000 to 2000 ... but now that it's 2005 to 2000 I'm getting this issue.

Let me know, please.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 7

We scaled back the schedule for moving that specific database, but I did read where one of the Hotfixes or CU's in SP2 or SP3 seemed to have addressed possible issues related to this.

How far off are you from SP3 and the latest Cumulative Update?

I'll see if I can find the info that I saw that could be a fix, but then again it could break something else.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

I have had this issue and there isn't really a "fix" there is workarounds. The best is to pull copies of the data local, using something like SSIS because it can do Fast Bulk Load.. Another option is a stored procedure on the remote side that does as much pre-processing as possible and then returns a sub-set back that is held in a temp table, similar to the first option. Another option is to re-architect the process to make only discrete calls remotely and only work with remote data when it ABSOLUTELY needs to. My last thought is join criteria, it *seems* that when the joins are primarily on numeric (int, numeric, etc.) columns the optimizer does a better job and it performs better. I can't substantiate that but that has been my annecdotal experience..

CEWII

richardbDBA SSC Rookie Points: 27 More actions · Answer 9

Thanks for getting back to me...we are running SP3 9.00.4035.00SP3Enterprise Edition (64-bit). I am really trying to avoid having to rewrite this proc...to use openquery or move the update to the destination server...both of which I have read and will most likely resolve things...

Let me know if you can find something...I haven't looked for a CU released after sp3...I'll do that now.

Thanks again.