SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remote Queries Slow From 2005 -> 2000


Remote Queries Slow From 2005 -> 2000

Author
Message
Jason Crider
Jason Crider
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 2232
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_SLEEP 30343 *
IO_COMPLETION 687
SLEEP_TASK 218
OLEDB 8765
SQLTRACE_BUFFER_FLUSH 32000 *
* 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(OBJECTSad@PostTable), SETSad[PI] = @FT.[PI] as [f].[PI],[CCode] = [Expr1010],[CreDate] = [BoxA].[ADHC].[dbo].[PA].[CreDate],[AI] = [BoxA].[ADHC].[dbo].[PA].[AI]))
|--Compute Scalar(DEFINESad[Expr1010]=CONVERT_IMPLICIT(varchar(2),[BoxA].[ADHC].[dbo].[PA].[CCode],0)))
|--Top(ROWCOUNT est 0)
|--Merge Join(Inner Join, MANY-TO-MANY MERGESad[f].[PI])=([BoxA].[ADHC].[dbo].[PA].[PI]), RESIDUALSad[BoxA].[ADHC].[dbo].[PA].[PI]=@FT.[PI] as [f].[PI]))
|--Sort(ORDER BYSad[f].[PI] ASC))
| |--Table Scan(OBJECTSad@FT AS [f]))
|--Sort(ORDER BYSad[BoxA].[ADHC].[dbo].[PA].[PI] ASC))
|--Filter(WHERESadlen([BoxA].[ADHC].[dbo].[PA].[CCode])=(2)))
|--Remote Query(SOURCESadBoxA), QUERYSadSELECT "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
Jason Crider
Jason Crider
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 2232
*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
Mike - CI
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1155
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.
ravikanth.
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2054 Visits: 2142
Run DBCC FREEPROCCACHE to clear out the cache.
Jason Crider
Jason Crider
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 2232
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
Jason Crider
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 2232
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
richardbDBA
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 216
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
Jason Crider
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 2232
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
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24934 Visits: 5314
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
richardbDBA
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 216
Thanks for getting back to me...we are running SP3 9.00.4035.00 SP3 Enterprise 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search