Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Remote Queries Slow From 2005 -> 2000 Expand / Collapse
Author
Message
Posted Friday, January 23, 2009 1:42 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
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(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(WHERElen([BoxA].[ADHC].[dbo].[PA].[CCode])=(2)))
|--Remote Query(SOURCEBoxA), QUERYSELECT "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
Post #642751
Posted Monday, January 26, 2009 2:41 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
*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
Post #643710
Posted Monday, January 26, 2009 2:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 202, Visits: 1,088
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.
Post #643716
Posted Monday, January 26, 2009 3:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:32 PM
Points: 1,630, Visits: 1,810

Run DBCC FREEPROCCACHE to clear out the cache.

Post #643769
Posted Monday, January 26, 2009 9:28 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
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
Post #643887
Posted Tuesday, January 27, 2009 12:45 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
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
Post #644473
Posted Tuesday, July 7, 2009 1:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 13, Visits: 211
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.
Post #748844
Posted Tuesday, July 7, 2009 2:36 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
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
Post #748863
Posted Tuesday, July 7, 2009 2:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #748868
Posted Wednesday, July 8, 2009 9:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 13, Visits: 211
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.
Post #749832
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse