Remote Queries Slow From 2005 -> 2000

Question

Post reply

Remote Queries Slow From 2005 -> 2000

Jason Crider

SSCertifiable

Points: 6019
More actions
January 23, 2009 at 1:42 pm

#134074

Box A
SQL 2000 SP4 + 2040 hotfix, x64 OS, 32-bit SQL Server
Box B
SQL Server 2005, SP2, X64 OS, 32-bit SQL Server
Most production databases are located on Box A but we have a few that I've gotten moved over to Box B. Queries to from Box B to Box A have worked fine but they aren't really what I would consider large.
I've since begun to test this with our development version which works fine on SQL 2000 when it doesn't do remote queries.
A query that takes less than 3 seconds (most of the time instant) when run locally, takes upwards of 30 seconds when using four part names in the views that the stored proc calls.
After clearing the waits and running this guy I get the following:
LAZYWRITER_SLEEP30343 *
IO_COMPLETION687
SLEEP_TASK218
OLEDB8765
SQLTRACE_BUFFER_FLUSH32000 *
* I realize these two are usually ignored.
I will focus on the costliest part of the query since it's so long.
Here's the Execution plan:
|--Table Insert(OBJECT:(@PostTable), SET:([PI] = @FT.[PI] as [f].[PI],[CCode] = [Expr1010],[CreDate] = [BoxA].[ADHC].[dbo].[PA].[CreDate],[AI] = [BoxA].[ADHC].[dbo].[PA].[AI]))
|--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(varchar(2),[BoxA].[ADHC].[dbo].[PA].[CCode],0)))
|--Top(ROWCOUNT est 0)
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([f].[PI])=([BoxA].[ADHC].[dbo].[PA].[PI]), RESIDUAL:([BoxA].[ADHC].[dbo].[PA].[PI]=@FT.[PI] as [f].[PI]))
|--Sort(ORDER BY:([f].[PI] ASC))
| |--Table Scan(OBJECT:(@FT AS [f]))
|--Sort(ORDER BY:([BoxA].[ADHC].[dbo].[PA].[PI] ASC))
|--Filter(WHERE:(len([BoxA].[ADHC].[dbo].[PA].[CCode])=(2)))
|--Remote Query(SOURCE:(BoxA), QUERY:(SELECT "Tbl1009"."AI" "Col1025","Tbl1009"."PI" "Col1026","Tbl1009"."CCode" "Col1034","Tbl1009"."CreDate" "Col1036" FROM "ADHC"."dbo"."PA" "Tbl1009" WHERE ("Tbl1009"."CreDate" IS NOT NULL) AND "Tbl1009"."EndDate" IS NULL))
I wish I could provide tons of sample code but since it's sensitive data I cannot.
One other thing that I will mention is that there are different lengths on the datatype for a few columns.
Box A may have a column with varchar(50) and the developer may use varchar(11). In the above example Box A has a VarChar(4) on the CONVERT_IMPLICIT(varchar(2). Could that difference really cause 27 seconds of duration?
Update: I just ran perfmon on the Network Interface and there are no problems there.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Viewing 15 posts - 1 through 14 (of 14 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: 3472 More actions · Answer 3

ravikanth.

Hall of Fame

Points: 3472

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.